• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

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

ProCom

Well-known member
#1
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

Well-known member
#2
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

Well-known member
#3
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?
 

ProCom

Well-known member
#6
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

Well-known member
#7
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

Well-known member
#8
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
Code:
<?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
Code:
<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

Well-known member
#9
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

Well-known member
#10
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

Well-known member
#11
@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).
 

ProCom

Well-known member
#13
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? :D
 

NixFifty

Well-known member
#14
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:

Code:
<?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:

Code:
<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

Well-known member
#15
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!!!
 

NixFifty

Well-known member
#16
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

Well-known member
#17
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:

upload_2014-1-30_14-0-37.png

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