1. 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

Discussion in 'XenForo Questions and Support' started by ProCom, Nov 8, 2013.

  1. ProCom

    ProCom Active Member

    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:

    ... 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!!
     
  2. Jeremy

    Jeremy XenForo Moderator Staff Member

    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.
     
  3. ProCom

    ProCom Active Member

    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?
     
    Walter likes this.
  4. MattW

    MattW Well-Known Member

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

    MattW Well-Known Member

  6. ProCom

    ProCom Active Member

    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:

     
  7. 0xym0r0n

    0xym0r0n Well-Known Member

    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
     
    MattW likes this.
  8. 0xym0r0n

    0xym0r0n Well-Known Member

    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.
     
    insocial, Walter and Andy.N like this.
  9. ProCom

    ProCom Active Member

    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.
     
  10. 0xym0r0n

    0xym0r0n Well-Known Member

    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.
     
  11. ProCom

    ProCom Active Member

    @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).
     
  12. AzzidReign

    AzzidReign Well-Known Member

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

    ProCom Active Member

    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
     
  14. NixFifty

    NixFifty Well-Known Member

    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 and AzzidReign like this.
  15. ProCom

    ProCom Active Member

    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!!!
     
  16. NixFifty

    NixFifty Well-Known Member

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

    ProCom Active Member

    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.
     

Share This Page