Create a simple template to display data from a mysql query

pattycake2

Active member
I am trying to create a menu item that will display the results of a mysql query. Nothing fancy, no user permissions, no group permissions, no settings, etc. Just be able to create a query and have it display the results inside the forum. For example, I have AndyB's "New Member" add-on that does a query and then displays the results of that query showing all new members The table is the XF database so it's not an external database (although it could be but not required). No conditionals. etc.

Do I need to go thru all the steps of creating an add-on, or can I just create a php page with the necessary sql query and then display the results?

or, Is there an existing template that I can use as an example and then make the menu item call that template.

I know php, I know mysql, just don't know enough about XF to make this happen.

Thanks for any advice or help
 
There are most likely a zillion ways to achieve the goal "display data from a mysql query", here is one the uses only template code:

HTML:
<xf:foreach loop="$xf.app.db.fetchAll('SELECT title FROM xf_node')" value="$row">
{$row.title}
</xf:foreach>

If this does not help you please provide furhter information what exactly you would like to do.
 
There are most likely a zillion ways to achieve the goal "display data from a mysql query", here is one the uses only template code:

HTML:
<xf:foreach loop="$xf.app.db.fetchAll('SELECT title FROM xf_node')" value="$row">
{$row.title}
</xf:foreach>

If this does not help you please provide furhter information what exactly you would like to do.

Hey, first of all.... thank you big time for replying

I think you have got me started in the right direction. Do I put this in a new template and then point the menu item to the new template or, do I shove this in to somewhere when I create the navigation menu item? Sorry that I am such a newbie at this XF stuff... I'm getting there though with help like this
 
Gad, I am such a noob... ok, I created a new template, shoved my sql statement in there per Kirby's post but now, what does the LINK look like when adding a new navigation item? ie, what is the syntax/link to display a template?

I know how to add a new navigation item, I know how to place it on the menu bar (or as a dropdown), I know how to set the Display condition, extra attributes, etc... just don't know what the link is to call/display that template.
 
You can't just display a template - it would need a route.

I suggest using a page node.
 
I think you have got me started in the right direction.
I don't think so, but we'll see.

Do I put this in a new template and then point the menu item to the new template or, do I shove this in to somewhere when I create the navigation menu item?
As already pointed out by brogan, there is no URL to just display an arbitrary template - you would need controller code to do so.

Please explain exactly what you want to achieve, otherwise it is pretty much imposssible to provide useful help.
The template approach can be used if you want to display data somewhere in an existing template, help page, page node, advertisement, HTML widget ... anywhere XenForo Template syntax does work - it would not work if you want to show data in the public or ACP navigation.
 
I have a table called "events" - i can add the table to XF or create an external, whichever is easiest.
In that table, there is a description of the event, the date, location, event_id, etc.

I want to put a menu item on the nav bar called "Events". It'll be an sql query that displays all event in ascending order, latest events first.

Once the Events link is clicked, I want the person to be able to select a specific event from the list is displayed so it can be edited, deleted, etc.. At the top of the Events list, an "Add New" button to create a new event.

More exact?
 
Your original post stated:
I am trying to create a menu item that will display the results of a mysql query. Nothing fancy

Now you want to:
Once the Events link is clicked, I want the person to be able to select a specific event from the list is displayed so it can be edited, deleted, etc.. At the top of the Events list, an "Add New" button to create a new event.

That's a fully fledged add-on.
 
No, I'm not asking for help on how to do all of this, he said he wanted exact, I posted the exact. I don't need a full fledged add-on - I can create that stuff, the links in the list, the clicking of the links, the Add New button myself once I got the display of the template to work.

btw: i am already displaying the results of the sql... I temporarily hooked it into the HELP links just to see if it would work and it did.

So my original request is still the same - to just create a menu item that displays the result of an sql query - I'll create all of the other needs, selecting the event so it can be edited or deleted, adding an Add-New button or link, etc.

I just wanted to put that template in a menu item, and have it display the query results... nothing else - I'll code everything else.

I write software for Nascar - I'm not a noob at programing - been doing it for 20+ years. Just a noob at trying to add some extras to a Xenforo site
 
Last edited:
I want to put a menu item on the nav bar called "Events". It'll be an sql query that displays all event in ascending order, latest events first.
What does "It'll be an sql query that displays all event in ascending order, latest events first." mean to you?
An entry in the public navigation can't be an "SQL query" (well ... it could, but that would look kinda strange:
1672964318682.png
More exact?
Not really unfortunately :(
Maybe mockups could help to understand your goal?

Your questions sound confusing to me, sorry.
 
Last edited:
What does "It'll be an sql query that displays all event in ascending order, latest events first." mean to you?

It means replace that "strange as you called it" menu item with the word "event", and when clicked, it executes that sql statement

or... how did you put that sql statement on your menu bar? What does the LINK say in your menu item?
 
This was actually a screenshot taken from xenforo.com and I did put that via inspector for demo purpose, but of course such a an entry could be created:

1672965543321.webp

But as I said before, I don't think this is what you want (doesn't make sense to me so show a SQL query in nav, lol :D).
 
But as I said before, I don't think this is what you want (doesn't make sense to me so show a SQL query in nav
no, it doesn't

Ya know, this has gotten way outta hand... lets just forget it.

Just seemed like it should be a simple thing to do, to add a menu item on the nav bar that does nothing more than show the results of an sql query.
 
Just seemed like it should be a simple thing to do, to add a menu item on the nav bar that does nothing more than show the results of an sql query.
Ah - now we're getting somewhere :)

I am still not entirely sure what you actually want to do / know but right now it seems like you want to have an entry named Events that is a menu that has links to events generated from a SQL query?

If so the easiest approach would be to use a callback method:
PHP:
public static function eventsMenu(array $navData, ?string $context, ?string $selected)
{
    return [
        'title' => 'Events',
        'href' => '...',
        'counter' => 1,
        'children' => [
            [
                'title' => 'Event 1',
                'href' => '...',
            ],
            [
                'title' => 'Event 2',
                'href' => '...',
            ]
        ]
    ];
}
 
i'll get that a shot.

Perhaps my original request was misinterpreted or too confusing as to what I actually needed.

The question should have read "how to put a php script into a xf page" and then, what would the link look like when I add it to a navigation menu item.

I've got lots of code (all php) that I want to put in xf pages.
Sorry for the confusion

but to give you an idea of how confused I am... I see your call back up above, but when I click "callback" option as the new nav menu item, it asks for "class", "method" and "context value" which I have no idea what it wants. Can I do the same thing with php code?
 
Last edited:
I am trying to create a menu item that will display the results of a mysql query.
[...]
Is there an existing template that I can use as an example and then make the menu item call that template.
This sounds like you want to display the results of a MySQL query in a template; I've given an example of how to do that

So my original request is still the same - to just create a menu item that displays the result of an sql query
This sounds like you want to create a menu in public navigation that has links generated forom results of a MySQL query; I've given an example of how to to that.

The question should have read "how to put a php script into a xf page" and then, what would the link look like when I add it to a navigation menu item.
And this sounds like you want to have a "page" that ouputs HTML created by another other Non-XenForo PHP script and wrap in XenForo PAGE_CONTAINER.

If the script
  1. Does not depend on globals
  2. Does not process POST (including uploads)
  3. Does not issue redirects
  4. Does not send any output / flush the buffer
  5. Does not call die/exit
  6. Does not trigger uncatched errors / exceptions
  7. Does always create body only HTML output

You can use a callback method like
PHP:
public static function getEvents(): string
{
    // do whatever you need to do here and return the outout HTML
}

and call it from any location that accepts XenForo template syntax via
Code:
<xf:callback class="YourClass" method="getEvents" />

I see your call back up above, but when I click "callback" option as the new nav menu item, it asks for "class", "method" and "context value" which I have no idea what it wants.
... and this again sounds like you want to add a menu enty to the public navigation that has linkes generated from the result of a MySQL query?

You've pointed out that you are an experienced developer so I assume that you are familiar with callbacks in general.
XenForo uses a PSR4 style autoloader , eg. it expects to find the namespaced class in subdirectories of src/addons (normally it should be part of an Add-on but strictly speaking this is not necessary.)
Class is the classname you choose, method is the method name you choose (eventsMenu in my example), context doesn't matter (if you don't need context for your usecase)

I am sorry - I'd really like to help but I don't understand what you want to achieve :(
Maybe screenshots could help?
 
I found a much much simpler way to do this and it works great... I now have numerous menu items, all calling php scripts that contain sql queries so, lets close this out.

I'll send screen shots after I have prettied it up a bit
 
Top Bottom