XF 1.2 Quick & Easy / Dirty Pages With SQL Database Query Results

[ProCom]

Disclaimer: I'm not a coder, but I pretend to be one on TV

Every once in a while I'd love to look at some stats / data that isn't readily available in xF or a plugin. On my old platform (punbb) I had some help from a guy who was able to create some really super basic pages that had basic SQL queries.

For example, the page hat the following at the top:

<?php
define('PUN_ROOT', './');
require PUN_ROOT.'include/common.php';

... and under that was some code that would allow me to insert my SQL queries and the page would run that query and display the info.

It was super simple... no headers, styling, login access rights/credentials... just a page I could bookmark and get quick access to data without having to login to PhpMyAdmin, browse to the table, copy the SQL query, paste the query, run the query, etc. Just a simple page bookmark.... BAM!

I know there are ways to create plugins (over my head) and I run via PhpMyAdmin (too many steps) but I'd love to have a page I can bookmark and view anytime I need it.

Hopefully that makes some level of sense?

Thanks in advance for your help!!

 

[Jeremy]

You are easily able to add a PHP file to your directory that connects to the database and runs queries. If you don't want to use the XenForo framework you do not need to utilize any functionality of XenForo.

 

[ProCom]

Thanks for the reply!

Let's say I have this "friend" who has no idea where to begin with doing something like that. Are there any existing examples that I... I mean HE could use as a template to edit?

 

[MattW]

So you just want to have a page with some of your own SQL results populated in there?

 

[MattW]

http://xenforo.com/community/resources/querier.2389/

 

[ProCom]

Wow, thanks Matt, that's definitely a great option too!

Yes, I want to just have a simple page that shows the results of a query. While the addon above is excellent, I still have to dig up the query and insert it every time vs. just having a page I've FTP'd up and then bookmarked.

For example, right now I'm keeping an eye on all my users that are uploading avatars with the following query. It would be great to just click a link and see that info on the page:

SELECT username,avatar_date
FROM `xf_user`
WHERE `avatar_date` > 0
ORDER BY `avatar_date` DESC

 

[0xym0r0n]

I don't know how good you are with code but if you break down this resource
http://xenforo.com/community/resources/topoxy.1419/

That's a quick and dirty way. I literally passed SQL results into the page. I did add a cron job but it's along the same concept. You need to pass the query to a view param *someone correct my lingo if I'm wrong* and then call to that from the page, but also be sure to include the callback in the page options

 

[0xym0r0n]

Uhm here's a step by step I actually did for someone else. I just changed out the names lol

and I used Fuhrmann's guides to help me in the beginning so you might try to look at his guides in the resource section to help you out with the questions

1. Create a folder in library/ [FolderName]
2. in that folder create a test.php file
3. Open up test.php and add this

<?php

class FolderName_test
{
    public static function testQuery(XenForo_ControllerPublic_Abstract $controller, XenForo_ControllerResponse_Abstract &$response)
    {
$mydb = XenForo_Application::get('db');
$testQuery = $mydb->fetchAll("
SELECT username,avatar_date
FROM `xf_user`
WHERE `avatar_date` > 0
ORDER BY `avatar_date` DESC");

        $response->params['testQuery'] = $testQuery;
}
}

4. Open up admincp->applications->Create New page
5. Fill out everything how you'd like except under PHP callback enter
Class = FolderName_test
Method = testQuery
6. Under the page options page html simply include

<xen:foreach loop="$testQuery" value="$testQuery">
{$testQuery.username} - {$testQuery.avatar_date}
</xen:foreach><br>

7. Save and navigate to that page. You should see results printed according to what you defined in your query.

 

[ProCom]

Cool, thanks!

That looks almost do-able, but I'm wondering if there is even a more simple way to just put whatever bits are needed into a single page. Again, I don't need any of the xF stuff (styles, navigation, headers, etc.)... just a spit-out of the data onto a page.

Sorry if I'm not making a lot of sense since I'm not a coder. From what I understand all I need / want from xF is the access to the db for the file to run the query.

 

[0xym0r0n]

i wouldnt advise it but you could just make a regular php page with the query and password protect it. i dont think anyone or any add on is available to do that. actually try looking at the kotami bridge add on. that might be what you need.

 

[ProCom]

@0xym0r0n, I'm following the steps you mentioned above (trying to match it perfectly) and I get this error when trying to create the page:

Please enter a valid callback method (FolderName_test::testQuery).

 

[AzzidReign]

@NixFifty can probably help with this if you still need help.

 

[ProCom]

Thanks AzzidReign!

Here's where I'm at: I've been following the instructions and have created pages using the samples. The problem now is I have ZERO idea on how to customize it.

Here is the sample query I need to run and displayed in the page:

SELECT thread_id, username, title, reply_count, view_count, first_post_likes, FROM_UNIXTIME(post_date) as post_date_time
FROM xf_thread
WHERE post_date > UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -30 DAY))
AND reply_count > 10
ORDER BY `xf_thread`.`first_post_likes` DESC ​

What I need with is:

1) The php file I will install in my /library/folder/
2) The "Template HTML" I need in the page settings
3) The "callback" settings

I'm hoping that once I get those, I can customize from there.

Anybody willing to help me create the code for the PHP file and the code for the HTML template that I'll paste into a page?

 

[Naz]

Here's what I tossed together based on your avatar query. You can probably follow the same steps and use it to replicate any other queries you need to do. I adapted this from what Oxy posted above. Worked like a charm.

1) Create a new folder in your library directory, called ProCom and inside there create a file called Callback.php.
2) Paste the following:

<?php

class ProCom_Callback
{
    public static function avatarQuery(XenForo_ControllerPublic_Abstract $controller, XenForo_ControllerResponse_Abstract &$response)
    {
        $avatarQuery = XenForo_Application::get('db')->fetchAll("
                SELECT username, avatar_date
                FROM `xf_user`
                WHERE `avatar_date` > 0
                ORDER BY `avatar_date` DESC");
      
        $response->params['avatars'] = $avatarQuery;
    }
}

3) Navigate to AdminCP -> Applications -> Create New Page and adjust as required.
4) Paste the following in to Template HTML and change up as required:

<xen:foreach loop="$avatars" value="$avatar">
{$avatar.username} - {$avatar.avatar_date}
<br />
</xen:foreach>

5) Under PHP Callback, enter the following:

Class: ProCom_Callback
Method: avatarQuery

Take it for a whirl and see how you get on. Should be able to get an idea from that how to make different ones and drop in more methods in the callback with different queries. Hope I helped

 

[ProCom]

Excellent, thanks @NixFifty !

I got that page to work great. It lists all the users and avatar dates.

I've been trying to adjust both the php file contents and also the HTML template to work with my query above, but am getting all kinds of errors. Any change I could get some help with the php file and HTML template to do this query results:

SELECT thread_id, username, title, reply_count, view_count, first_post_likes, FROM_UNIXTIME(post_date) as post_date_time
FROM xf_thread
WHERE post_date > UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -30 DAY))
AND reply_count > 10
ORDER BY `xf_thread`.`first_post_likes` DESC ​

Ideally the line would be a hyperlink directly to the thread.

I think once I see this, I should be able to edit it for future queries.

Thanks in advance!!!

 

[Naz]

Excellent, thanks @NixFifty !

I got that page to work great. It lists all the users and avatar dates.

I've been trying to adjust both the php file contents and also the HTML template to work with my query above, but am getting all kinds of errors. Any change I could get some help with the php file and HTML template to do this query results:

SELECT thread_id, username, title, reply_count, view_count, first_post_likes, FROM_UNIXTIME(post_date) as post_date_time
FROM xf_thread
WHERE post_date > UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -30 DAY))
AND reply_count > 10
ORDER BY `xf_thread`.`first_post_likes` DESC ​

Ideally the line would be a hyperlink directly to the thread.

I think once I see this, I should be able to edit it for future queries.

Thanks in advance!!!

Seems like an issue with your SQL query. What exactly are you trying to do?

 

[ProCom]

The query should be ok... I run it in phpMyAdmin and get the results just fine.

Basically I want to display a page with the results of that query similar to what I see in phpMyAdmin, but on a "page" in xF.

Just a page that lists results like this:



The only difference would be that having the row, or just one element in the row linking to the actual thread would be awesome.

 

[ProCom]

Well, it only took me 4 years to figure this out

I had to do about 100 iterations of editing the callback file and the template, but starting with @NixFifty 's working example, I was able to tweak things to get it to do what I needed it to do.

I think my brain's going to explode.

One problem I ran into was that the classes kept getting errored out. I think I must have some page caching or something going on that was causing some issues because if I waited a few minutes, then saved again, the errors would go away.

Thanks again for the help guys!

 

[ProCom]

Ok, I've got a SUPER newbie question: Is it possible to have everything contained in the "page" system such that I don't need to keep editing the php file, ftp, etc? It would be awesome if I could just do all the edits, etc. direct through the XF CP.

Does that make any sense, or are y'all just like "What a dummy!"

For example, say I have a query like the following in my PHP file:

SELECT user_id,message_count FROM `xf_user` WHERE `message_count` > 15000

... and then I just want to change the 15000 to 25000. Is there a way to code all that into the template, page, etc. without having to access / ftp the php file?

 

[Paul B]

Create a basic add-on and set that as an option, and the PHP code will pull the value from it.