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.

[Boofo]

No, what you need to do is set the update cache time to 0 to run this to set the template up and then you can set it back to whatever you want after that.

[surfhog]

okay thanks I will try that then lol

[HTL] rules ......... lol

[surfhog]

well i have to say thanks for helping boofo, but my patience has gone with this one, maybe I will try it at a later date when i have more knowledge.

all the best

[Boofo]

Ok, well, when you're ready, I'll be here. Sorry you had so much trouble with it but it should have been no problem at all to install. Hmmm....

[surfhog]

okay I am here lol

I still getting as per:
https://vborg.vbsupport.ru/showpost.php?p=495901&postcount=63

I have done as per:

https://vborg.vbsupport.ru/showpost.php?p=495974&postcount=71

Any ideas please, the other thing

if I view a forum with no posts = nps
if I attempt to view a forum with posts, then I get the error:

Database error in vBulletin 3.0.0:
Invalid SQL:
SELECT user.userid, user.username, COUNT(post.postid) AS postcount
FROM vb3_post
LEFT JOIN vb3_thread ON (post.threadid = thread.threadid)
LEFT JOIN vb3_user ON (post.userid = user.userid)
WHERE thread.forumid=2
GROUP BY post.userid
ORDER BY postcount DESC
LIMIT 1

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

[Boofo]

That's the same problem as you are having with the other hack. For some reason the user table is giving us fits when you have a table prefix in the db and we use the TABLE_PREFIX on it. I am still looking into it and I will come up with an answer as soon as I know something. The strange thing is that there are those that also have a table prefix and are not having this same problem.

Sit tight and either disable that query for now or wait in installing the hack for this one until I get an answer.

[surfhog]

funny you say that boofo, i suspected as much myslef after i posted here.
yes I have seen these on several vb3 boards, so it must work and i do feel it would be a worth while modification to have on any board, hence my persistance.

i wonder if i am the only one having/had problems with these?
okay i will disable for now and await further instructions
thank you

[mharmon]

This works great.. one question though: How can I change the green color of the

"Last updated at 11:38 AM - April 10th, 2004"

I tried changing the <font color=green> in the template -- but it didn't do anything.

This is a great hack.. I love it!

[Boofo]

That's where you change it, but there are 2 places to change it in the template.

[Boofo]

Originally Posted by surfhog

funny you say that boofo, i suspected as much myslef after i posted here.
yes I have seen these on several vb3 boards, so it must work and i do feel it would be a worth while modification to have on any board, hence my persistance.

i wonder if i am the only one having/had problems with these?
okay i will disable for now and await further instructions
thank you

Here's your fix for this hack. It seems that this only affects those with table prefixes and since I don't use a table prefix as well as a lot of others, I didn't have any problem with this query. This will take care of it for you.

Replace the top poster code in the forumdisplay.php with this:

PHP Code:

    $topposter = $DB_site->query_first("
        SELECT user.userid, user.username, COUNT(post.postid) AS postcount
        FROM " . TABLE_PREFIX . "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
    "); 


Again, thanks to NTLDR for filling me in on this.