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!!

 

[ProCom]

Thanks!

I've been having some GREAT FUN with these queries and the XF pages! Wish I had stuck to figuring them out 4 years ago!!!

What's crazy is that they are probably WAY more capable than what I'm using them for... I feel like I might only be scratching the surface!

 

[ProCom]

FYI, here's the basic PHP that's working for me when pulling in which member is logged-in (thanks a ton @Mouth & @NixFifty for the help!) and applied to Bob's Article Management System:

<?php

class TEST_Data3
{
    public static function Data3Query(XenForo_ControllerPublic_Abstract $controller, XenForo_ControllerResponse_Abstract &$response)
    {
        $visitor = XenForo_Visitor::getInstance();
        
        $doneQuery = XenForo_Application::get('db')->fetchAll("
SELECT rate_review_id,article_id,user_id,username
FROM `xf_nflj_ams_rate_review`
WHERE user_id = ?
ORDER BY `xf_nflj_ams_rate_review`.`rate_review_date` DESC
LIMIT 100;", $visitor['user_id']);
        
        $response->params['done'] = $doneQuery;
    }
}

 

[ProCom]

Hey guys, I'm back with another newbie question!

So far all my pages have used "loops" to display multiple rows. Well, let's say I don't want to display a bunch of rows of data... I just want to display a single DB data point?

For example, here's the single data item query I want to display in a page:
SELECT COUNT(*)
FROM `xf_user`
WHERE `message_count` > 5000


So, in my page php, I have:

$myrows3 = $mydb->fetchAll("
SELECT COUNT(*)
FROM `xf_user`
WHERE `message_count` > 5000

... but what do I put in the "Template HTML:" for the new page to simply display this one data point? Obviously it's not going to be:

<xen:foreach loop="$myrows1" value="$myrow1" i="$i" count="$count">

... so what should it be?

Sorry for the SUPER NEWB question.

Thanks!

 

[ProCom]

Hmm... I still can't figure this out.

Any ideas on how to simply call one data-point vs. a "loop"?

 

[ProCom]

HUGE THANK YOU to @NixFifty for helping me with this!!!

Change your php code to this:

$myrows3 = $mydb->fetchOne("
SELECT COUNT(*)
FROM `xf_user`
WHERE `message_count` > 5000");

In your template use {$myrows3} or if you want thousands separation, use {xen:number $myrows3}

 

[ProCom]

well... dang it!

I upgraded to XF 2.1 and all my kick-ass custom pages that took me days to code (since I'm no developer) all broke!

Below is an example of the errors I'm getting now. Any chance it's a simple tweak here and there, or do the pages, php, sql, html, etc. discussed in the previous 25 posts all need to be completely rewritten? Any chance @Brogan or someone else has created an updated "how to" on XF pages?

Here's my error from one of my pages:

ErrorException: [E_WARNING] call_user_func_array() expects parameter 1 to be a valid callback, class 'ProCom_ams' not found in src/XF/Pub/Controller/Page.php at line 58
XF::handlePhpError()
call_user_func_array() in src/XF/Pub/Controller/Page.php at line 58
XF\Pub\Controller\Page->actionIndex() in src/XF/Mvc/Dispatcher.php at line 350
XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 257
XF\Mvc\Dispatcher->dispatchFromMatch() in src/XF/Mvc/Dispatcher.php at line 113
XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 55
XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2184
XF\App->run() in src/XF.php at line 391
XF::runApp() in index.php at line 20

 

[ProCom]

I've been wracking my brain trying to figure out what I need to do to get these pages working again.

Is it just a function of making changes to the existing code, or was everything with these changed such that I need to recreate them entirely, or a new system completely?

Thanks in advance for any tips / suggestions (or links to where this is discussed / resolved elsewhere).

 

[Naz]

Can you post the PHP and HTML code? Both will need to be mostly re-done so it’ll be helpful to see what you had, prior to XF2, to point you in the right direction.

 

[ProCom]

Can you post the PHP and HTML code? Both will need to be mostly re-done so it’ll be helpful to see what you had, prior to XF2, to point you in the right direction.

Sure!

Note: I'm mostly using these custom pages to help display data from @Bob 's awesome Article Management System.

Here's the PHP:

<?php

class ProCom_lc
{
    public static function lcQuery(XenForo_ControllerPublic_Abstract $controller, XenForo_ControllerResponse_Abstract &$response)
    {
        $visitor = XenForo_Visitor::getInstance();
        
        $lcneedQuery = XenForo_Application::get('db')->fetchAll("
SELECT article_id,title,review_count
FROM `xf_nflj_ams_article`
WHERE review_count < 5
AND category_id IN (11,21,22,27,23,26,24,28,29,25,30,31,32,33,34)
AND article_state = 'visible'
AND article_id NOT IN (SELECT article_id from `xf_nflj_ams_rate_review` WHERE user_id = ?)
ORDER BY review_count ASC,article_id ASC LIMIT 20;", $visitor['user_id']);
              
        $lcuserdoneQuery = XenForo_Application::get('db')->fetchAll("
SELECT rate_review_id,article_id,user_id,username
FROM `xf_nflj_ams_rate_review`
WHERE is_review = 1
AND user_id = ?
ORDER BY `xf_nflj_ams_rate_review`.`rate_review_date` DESC
LIMIT 100;", $visitor['user_id']);

       $lccompletedQuery = XenForo_Application::get('db')->fetchAll("
SELECT review_count,COUNT( review_count ) AS 'count'
FROM `xf_nflj_ams_article`
WHERE category_id IN (11,21,22,27,23,26,24,28,29,25,30,31,32,33,34)
AND article_state = 'visible' GROUP BY review_count");
              

       $memberQuery = XenForo_Application::get('db')->fetchAll("
SELECT user_id,username,COUNT( user_id ) AS 'count'
FROM `xf_nflj_ams_rate_review`
WHERE rate_review_date > 1532063991
AND is_review = 1
GROUP BY user_id ORDER BY `count` DESC LIMIT 20");
        
      
        $response->params['lcneed'] = $lcneedQuery;
        $response->params['lcuserdone'] = $lcuserdoneQuery;       
        $response->params['lccompleted'] = $lccompletedQuery;   
        $response->params['member'] = $memberQuery;   
    }
}

... and here's the HTML Template:

LC Articles You Have Not Reviewed Yet:
<br><br />
<xen:foreach loop="$lcneed" value="$lcneed">
<a href="{xen:link 'articles/{$lcneed.article_id}'}">{$lcneed.title}</a> - reviews: {$lcneed.review_count}
<br><br />
</xen:foreach>
<xen:foreach loop="$lcneed" value="$lcneed">
<a href="{xen:link 'articles/{$lcneed.article_id}'}">{$lcneed.title}</a> - reviews: {$lcneed.review_count}
<br><br />
</xen:foreach>


<br><br />
Your Recently Reviewed Articles (not just ones for LC):
<br><br />
<xen:foreach loop="$lcuserdone" value="$lcuserdone">
Article ID: <a href="{xen:link 'articles/{$lcuserdone.article_id}'}">{$lcuserdone.article_id}</a>
<br />
</xen:foreach>

<br><br />
<p>Pages with reviews:</p>
#Articles - reviews <br>
<xen:foreach loop="$lccompleted" value="$lccompleted">
{$lccompleted.count} - {$lccompleted.review_count} reviews
<br />
</xen:foreach>


<br><p>
Top 20 Reviewers Since 7/19/2018:<br>
<xen:foreach loop="$member" value="$member">
{$member.count} - <a href="{xen:link 'members/{$member.user_id}'}">{$member.username}</a>
<br />
</xen:foreach>