vB3 Forum Quick Stats Cache Serialize Hack v1.1

vB3 Forum Quick Stats Cache Serialize Hack
Version 1.1
(By Boofo)

What does this hack do?
It will display stats about each forum at the top of that forum. (NOTE: The attachments info will NOT show in forums that have no attachments.) Also included in the hack is Logician's vB2 "Last Post Minute Hack" (with his permission for the vB2 version, of course) optimized and redone for vB3. It now uses NO queries to get the last minute info. This hack will display a link the last post and the time the last post was made (i.e. "1 minute" or "2 days", etc.) as well as the name and link to the last poster in the forum. The stats are cached and the cache is only updated at a time set by you. With an option in the Admin CP, you can reset the time between cache updates without having to edit any more files once this hack is installed. This hack will add 1 optimized query (for the user's threads and replies stats) on a cache read, but since this hack is serialized, the cache read itself will not pull a query. On a cache update, it will add 6 queries to update the datastore. And then drop back down to the one query until the next cache update. This hack will make a cache in the datastore for each forum you have, so you don't have to worry about forums getting cross-cached with this hack. It has been thorughly tested on my site, but as we all know too well, sites can, and often do, vary in the way a hack performs on it. I forsee no problems of any kind on any site in the way this hack functions. It's basically pretty simple and hard to mess up.

Note: This is the setting for the time interval (in minutes) that you want the cache to be updated on. The default value is 10 minutes. The following code is in the forumdisplay.php part of this hack.

$updatetime = 10;

Once you install the Admin CP setting (at the end of this file), you will be able to update this from the Admin CP vBulletin Options settings.

Credits:
A BIG thank you goes out to KirbyDE for all of his guidance and patience while this hack was being written and tested. This hack might never have gotten past the dreamed up state if it wasn't for him. And it definately wouldn't have been finished by now or turned out as optimized as it is. Thank you, my friend. I learned a lot from you on our little adventure here. It was definately worth the trip.

Version Information:
Version 1.0 --Initial release
Version 1.1 --Corrected a few minor potential bugs and re-did the template. Now it has a cleaner look.

Installation overview:
Files to edit: (1)
--forumdisplay.php
Templates to add: (1)
--forumdisplay_quickstats
Templates to edit: (1)
--forumdisplay
Settings to add: (1)
--forumcachetime

Note: The first image below will show the forum quick stats for a forum that has attachments. The second image will show the forum quick stats for a forum that has no attachments.

[surfhog]

thanks and sorry but nope if I use the forumdisplay.php file with the above replacement code, I still getting the same error.

[Boofo]

Ok, this is definately the fix now. Apparently I have to do the AS part on all tables in a query that has more than 1 table. I never knew nthat before, so I guess we're both learning something here.

PHP Code:

    $topposter = $DB_site->query_first("
        SELECT user.userid, user.username, COUNT(post.postid) AS postcount
        FROM " . TABLE_PREFIX . "post AS post
        LEFT JOIN " . TABLE_PREFIX . "thread AS thread ON (post.threadid = thread.threadid)
        LEFT JOIN " . TABLE_PREFIX . "user AS user ON (post.userid = user.userid)
        WHERE thread.forumid=$forumid
        GROUP BY post.userid
        ORDER BY postcount DESC
        LIMIT 1
    "); 


[surfhog]

Originally Posted by Boofo

Ok, this is definately the fix now. Apparently I have to do the AS part on all tables in a query that has more than 1 table. I never knew nthat before, so I guess we're both learning something here.

PHP Code:

    $topposter = $DB_site->query_first("
        SELECT user.userid, user.username, COUNT(post.postid) AS postcount
        FROM " . TABLE_PREFIX . "post AS post
        LEFT JOIN " . TABLE_PREFIX . "thread AS thread ON (post.threadid = thread.threadid)
        LEFT JOIN " . TABLE_PREFIX . "user AS user ON (post.userid = user.userid)
        WHERE thread.forumid=$forumid
        GROUP BY post.userid
        ORDER BY postcount DESC
        LIMIT 1
    "); 


-------------------------------------------------------------------------
I still get error:
-------------------------------------------------------------------------

Database error in vBulletin 3.0.0:
Invalid SQL:
SELECT forum.title AS forum, COUNT(attachment.attachmentid) AS count, SUM(IF(thumbnail = '', 0, 1)) AS thumbs, SUM(IF(thumbnail = '', 1, 0)) AS files, SUM(attachment.filesize) AS bytes
FROM vb3_attachment, vb3_post, vb3_thread, vb3_forum
WHERE attachment.postid = post.postid
AND post.threadid=thread.threadid
AND forum.forumid=thread.forumid
AND forum.forumid=2
GROUP BY thread.forumid
ORDER BY count DESC

mysql error: Unknown table 'forum' in field list
mysql error number: 1109

[Boofo]

This should take care of that.

PHP Code:

    $attachs=$DB_site->query_first("
        SELECT forum.title AS forum, COUNT(attachment.attachmentid) AS count, SUM(IF(thumbnail = '', 0, 1)) AS thumbs, SUM(IF(thumbnail = '', 1, 0)) AS files, SUM(attachment.filesize) AS bytes
        FROM " . TABLE_PREFIX . "attachment, " . TABLE_PREFIX . "post AS post, " . TABLE_PREFIX . "thread AS thread, " . TABLE_PREFIX . "forum AS forum
        WHERE attachment.postid = post.postid
        AND post.threadid=thread.threadid
        AND forum.forumid=thread.forumid
        AND forum.forumid=$forumid
        GROUP BY thread.forumid
        ORDER BY count DESC
    "); 


Jusr replace the attachs query with this one.

[surfhog]

sorry it dosn't, i still get this error:

PHP Code:

 Database error in vBulletin 3.0.0:
Invalid SQL:  
        SELECT forum.title AS forum, COUNT(attachment.attachmentid) AS count, SUM(IF(thumbnail = '', 0, 1)) AS thumbs, SUM(IF(thumbnail = '', 1, 0)) AS files, SUM(attachment.filesize) AS bytes 
        FROM vb3_attachment, vb3_post AS post, vb3_thread AS thread, vb3_forum AS forum 
        WHERE attachment.postid = post.postid 
        AND post.threadid=thread.threadid 
        AND forum.forumid=thread.forumid 
        AND forum.forumid=5 
        GROUP BY thread.forumid 
        ORDER BY count DESC 
    
mysql error: Unknown table 'attachment' in field list
mysql error number: 1109 


[Boofo]

Ok, hold on, I missed one. Sorry about that. This WILL work now.

PHP Code:

    $attachs=$DB_site->query_first("
        SELECT forum.title AS forum, COUNT(attachment.attachmentid) AS count, SUM(IF(thumbnail = '', 0, 1)) AS thumbs, SUM(IF(thumbnail = '', 1, 0)) AS files, SUM(attachment.filesize) AS bytes
        FROM " . TABLE_PREFIX . "attachment AS attachment, " . TABLE_PREFIX . "post AS post, " . TABLE_PREFIX . "thread AS thread, " . TABLE_PREFIX . "forum AS forum
        WHERE attachment.postid = post.postid
        AND post.threadid=thread.threadid
        AND forum.forumid=thread.forumid
        AND forum.forumid=$forumid
        GROUP BY thread.forumid
        ORDER BY count DESC
    "); 


[surfhog]

nope sorry still the same error lol

[Boofo]

Originally Posted by surfhog

nope sorry still the same error lol

There's no way it can be the same error. Try it again and post the exact error here, will you please?

[surfhog]

okay this is the error, when i attempt to view forum with post within it - if no post, then i get nps.

Database error in vBulletin 3.0.0:

Invalid SQL:
SELECT forum.title, forum.forumid, COUNT( post.postid ) AS yourposts, SUM( IF( thread.postuserid = 1 AND post.dateline = thread.dateline , 1, 0 ) ) AS yourthreads
FROM vb3_forum, vb3_thread, vb3_post
WHERE forum.forumid = thread.forumid
AND thread.threadid = post.threadid
AND post.userid = 1
AND forum.forumid = 2
GROUP BY forum.forumid
ORDER BY yourposts
DESC LIMIT 1

mysql error: Unknown table 'forum' in field list

mysql error number: 1109

not sure it will help? but if u have email addy i can post my file for u to view?

[Boofo]

Ok, I was right, it was a different query. Replace the yourpost query with this one and be ready to celebrate!

PHP Code:

    $yourpost = $DB_site->query_first("
        SELECT forum.title, forum.forumid, COUNT( post.postid ) AS yourposts, SUM( IF(  thread.postuserid = $bbuserinfo[userid] AND post.dateline = thread.dateline , 1, 0 ) ) AS yourthreads
        FROM " . TABLE_PREFIX . "forum AS forum, " . TABLE_PREFIX . "thread AS thread, " . TABLE_PREFIX . "post AS post
        WHERE forum.forumid = thread.forumid
        AND thread.threadid = post.threadid
        AND post.userid = $bbuserinfo[userid]
        AND forum.forumid = $forumid
        GROUP BY forum.forumid
        ORDER BY yourposts
        DESC LIMIT 1
    ");