vB3 Forum Quick Stats Cache Serialize Hack v1.1

[Boofo]

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.

[Allan]

Great Hack ;)

[Allan]

Erreur

requ?te SQL :

INSERT INTO datastore( title,
DATA )
VALUES (

'forumstatscache', 'a:15:{ s:10:"numthreads";s:1:"0"; s:10:"numreplies";s:1:"0"; s:8:"numviews";s:1:"0"; s:15:"topthreadscount";s:1:"0"; s:12:"topthreadsid";s:1:"0"; s:12:"topthreadsun";s:0:""; s:17:"topthreadspercent";s:1:"0"; s:14:"toppostercount";s:1:"0"; s:11:"topposterid";s:1:"0"; s:11:"topposterun";s:0:""; s:16:"topposterpercent";s:1:"0"; s:12:"attachscount";s:1:"0"; s:12:"attachsbytes";s:1:"0"; s:9:"avgrating";s:1:"0"; s:10:"lastupdate";s:1:"0"; }'
)

MySQL a r?pondu:


#1146 - La table 'mysql.datastore' n'existe pas

[sabret00the]

great work, this is like grogs version but for vb3 :)

[Boofo]

Erreur

requ?te SQL :

INSERT INTO datastore( title,
DATA )
VALUES (

'forumstatscache', 'a:15:{ s:10:"numthreads";s:1:"0"; s:10:"numreplies";s:1:"0"; s:8:"numviews";s:1:"0"; s:15:"topthreadscount";s:1:"0"; s:12:"topthreadsid";s:1:"0"; s:12:"topthreadsun";s:0:""; s:17:"topthreadspercent";s:1:"0"; s:14:"toppostercount";s:1:"0"; s:11:"topposterid";s:1:"0"; s:11:"topposterun";s:0:""; s:16:"topposterpercent";s:1:"0"; s:12:"attachscount";s:1:"0"; s:12:"attachsbytes";s:1:"0"; s:9:"avgrating";s:1:"0"; s:10:"lastupdate";s:1:"0"; }'
)

MySQL a r?pondu:


#1146 - La table 'mysql.datastore' n'existe pas
What version of vB3 are you running? This is tested and running on 3.0.0 RC3. That is saying you don't have datastore and if you are runing 3.0, you have to have it. ;)

EDIT: Are you using a table prefix?

[Boofo]

great work, this is like grogs version but for vb3 :)
This is the upgrade to my vB2 version. This is cached. Grog's isn't. I think this has more stats, too. And the percentages are accurate. ;)

[Allan]

I am on RC4

what I find odd, the table "datastore" is well and not, I do not employ the table prefixed

[Boofo]

It shouldn't be any different as far as datastore goes. You can install the hack without running that query and the datastore will get made. But I need to find out why you are having that problem with it. Try repairing and optimizing your db (in the Admin CP) and try the query again. Grab a new file from the first post. I just added table prefix to that just to be sure for everyone. Did you install my forumhome stats cache hack ok? That is the same type of datastore query there. Did it run ok?
Let me know what happens again. ;)

[Allan]

It shouldn't be any different as far as datastore goes. You can install the hack without running that query and the datastore will get made. But I need to find out why you are having that problem with it. Try repairing and optimizing your db (in the Admin CP) and try the query again. Grab a new file from the first post. I just added table prefix to that just to be sure for everyone. Did you install my forumhome stats cache hack ok? That is the same type of datastore query there. Did it run ok?
Let me know what happens again. ;)

veiled, I optimized the table "datastore" in the admincp and Ca goes ok ;)

a last thing, to speak to you about this:
$updatetime = 10;


could you indicate to me or to seek that, you do not specify it in the txt ?

[Boofo]

Ok, so it wasn't the hack messing up, right? Thank you. ;)

That is the update time in minutes for the cache. You set that to what you want. If you look at the end of the file, there is a setting you can do in the Admin CP so you can set it from the Admin CP after you install the hack. ;)

[gmarik]

This is good. Maybe all the photos included and all the hyperlinks posted in the forum list would be a good add-on for this ...?

[Allan]

nop, in fact, I do not find "$updatetime = 10;" :(

[FleaBag]

Ahh fantastic, glad to see this move over from vB2. :D

[Boofo]

Ahh fantastic, glad to see this move over from vB2. :D
Don't forget the install button in here. You missed it on the other hacks. ;)

[Boofo]

nop, in fact, I do not find "$updatetime = 10;" :(
Look again. I see it right here in this piece of code from the hack:

////////////////////////////// BOOFO'S FORUM QUICK STATS CACHE HACK //////////////////////////////
$forumstatscache = unserialize($datastore['forumstatscache']);
$forumstats = $forumstatscache[$forumid];
$updatetime = 10;
$statsupdate = '';

[kauka]

Look again. I see it right here in this piece of code from the hack:

////////////////////////////// BOOFO'S FORUM QUICK STATS CACHE HACK //////////////////////////////
$forumstatscache = unserialize($datastore['forumstatscache']);
$forumstats = $forumstatscache[$forumid];
$updatetime = 10;
$statsupdate = '';
very good hack Boofo. Thank you and clicked install.

[AutomatikStudio]

Maybe I'm just an idiot...but how exactly do you "Turn on DEBUG"? I'm using RC3.

[assassingod]

Add

$debug = '1';


to the end of /includes/config.php (But before the ?>)

[Allan]

Yes, while returning in d?bug mode, that frees much functionality in the options of Vbulletin.

==============================================

$updatetime = 10;

I does not include/understand, why not have directly to integrate it in the code?

[Boofo]

Yes, while returning in d?bug mode, that frees much functionality in the options of Vbulletin.

==============================================

$updatetime = 10;

I does not include/understand, why not have directly to integrate it in the code?
Not all boards will want it set to 10 minutes. Some might want it more and some might want it less. This gives you the option to set it to what you want for your site and not have to have it hard-coded. Trust me, this really IS the better way. ;)

[Boofo]

Add

$debug = '1';


to the end of /includes/config.php (But before the ?>)
Thanks, Steve. ;)

Make sure you let them know to take that back out of the config.php file when they are done setting the option up. ;)

[Allan]

no problem.

I did not understand the system, now, I understands better ;)

[Boofo]

No problem. ;)

[Boofo]

This is good. Maybe all the photos included and all the hyperlinks posted in the forum list would be a good add-on for this ...?I just added the amount of attached Thumbnails and the amount of Attached Files to the hack. It looks something like this:


Total Attachments: 33 (17 Images - 16 Files)
Can you think of any other forum stats you would like to see in this? I'm totally out of ideas. ;)

[sabret00the]

an option in the admin cp to turn on/off what stats you'd like to display would be great.

[Boofo]

an option in the admin cp to turn on/off what stats you'd like to display would be great.
I thought about doing that when I wrote this hack but decides against it since there really aren't enough stats there to warrant that. As the hack grows and I add more stuff to it, it might make more sense to incorporate that then.

Just for curiosity's sake, which stats would you like to have that option? And how would you like to see it done? I might not be seeing this as you mean it. ;)

[lasto]

nice oen m8

[Boofo]

Don't forget the install button in case there are any updates. ;)

[FleaBag]

Don't forget the install button in here. You missed it on the other hacks. ;)

LOL I didn't miss it, I just hadn't upgraded to vB3 yet. But now I have, and now I'm installing this. So... CLICK! Heh. :)

[Boofo]

I have an update for this as soon as I get the install file done. Those clicking install will know about the update when it happens. ;)

[FleaBag]

Uh oh...


Warning: Unexpected character in input: '\' (ASCII=92) state=1 in /www/dragonninja/forumdisplay.php on line 300

Parse error: parse error in /www/dragonninja/forumdisplay.php on line 300


Any ideas Boofo?

[Boofo]

What is on line 300?

[FleaBag]

LOL I'm a fool, I pasted...


/////////////////////////////////////////////////////////////////
\ TEMPLATES MODIFICATIONS \
/////////////////////////////////////////////////////////////////


...into forumdisplay.php. Sorry to trouble you Bob.

[Boofo]

No trouble at all. Glad it was something simple. ;)

[Mr. HillBilly]

Nice hack but,


Wrong SQL Code ;)


Correct code:


INSERT INTO datastore ( title, data ) VALUES (
'forumstatscache' ,'a:15:{
s:10:"numthreads";s:1:"0";
s:10:"numreplies";s:1:"0";
s:8:"numviews";s:1:"0";
s:15:"topthreadscount";s:1:"0";
s:12:"topthreadsid";s:1:"0";
s:12:"topthreadsun";s:0:"";
s:17:"topthreadspercent";s:1:"0";
s:14:"toppostercount";s:1:"0";
s:11:"topposterid";s:1:"0";
s:11:"topposterun";s:0:"";
s:16:"topposterpercent";s:1:"0";
s:12:"attachscount";s:1:"0";
s:12:"attachsbytes";s:1:"0";
s:9:"avgrating";s:1:"0";
s:10:"lastupdate";s:1:"0";
}' )

[Boofo]

First of all, you don't really need to even run this query, if you don't want to. When the stats get inserted into the datastore, it will be made up then. And second of all, if someone uses a table prefix, they would need that part of it if they did use this query. So, actually, the query is right. Yours would be wrong, if they had a table prefix. I had the query as you have it before I changed it to add the table prefix, by the way. Other than that, there is no difference in the 2 queries. ;)

[SpeedStreet]

Can you set this up so that it is only displayed in certain forums?

I don't want this in my off-topic, but my user support forum...this would be great.

[Boofo]

Sure. You could use a condition in the forumdisplay for that real easy.

[SpeedStreet]

Sure. You could use a condition in the forumdisplay for that real easy.
How so?

[Boofo]

<if condition="$forumid == '30'">
$forumdisplayquickstats
</if>

Just make the 30 above the ID of the forum you want to show it in. I just tested it and it works fine. ;)

[SpeedStreet]

Good deal!

And just to finalize my questions...how would I implement this for multiple forums?

Any chance we could get an admin option panel in the admincp for this? :)

[Boofo]

If you are going to only use this on a few forums, you could do this:

<if condition="in_array($forumid, array(30, 31, 32))">
$forumdisplayquickstats
</if>

As far as the option panel in the Admin CP, what do you mean exactly?

[SpeedStreet]

Thanks for the tip.

What I meant was that I'd like to see this as an option in vBulletin settings, where I could modify what forums receive this versus modifying a conditional.

You said if there are only a few forums....how much load would the array hammer on the system? Can you define a few....like less than five or less than 20?

[Boofo]

The array sholdn't do any more than if you did

<if condition="$forumid == '30' OR $forumid == '31' OR $forumid == '32'">

As far as an option in the Admin CP, I will look into that. You are the first person who has ever requested that. ;)

[Zachariah]

Boofo ! http://www.gzhq.net/forums/images/smilies/yourock.gif

* Zachariah "click"


BTW: your forums mouse is cool almost Star Trek :D

[Boofo]

Thank you, sir. And my mouse thanks you. ;)

Did you check out my Forum Quick Stats setup on my site? I re-did the template so it has a cleaner look to it. Check it out and let me know what you think. ;)

[Steve123]

Ahh bofo, i was just thinking about making something like this.. just saved me hours of work :D...

thanks dude.. :)

[Boofo]

No problem. ;)

And it's Boofo, with 2 o's. ;)

[Owen]

Error

SQL-query :

INSERT INTO " . TABLE_PREFIX . "datastore(

title,

DATA
)
VALUES (
'forumstatscache', 'a:15:{
s:10:"numthreads";s:1:"0";
s:10:"numreplies";s:1:"0";
s:8:"numviews";s:1:"0";
s:15:"topthreadscount";s:1:"0";
s:12:"topthreadsid";s:1:"0";
s:12:"topthreadsun";s:0:"";
s:17:"topthreadspercent";s:1:"0";
s:14:"toppostercount";s:1:"0";
s:11:"topposterid";s:1:"0";
s:11:"topposterun";s:0:"";
s:16:"topposterpercent";s:1:"0";
s:12:"attachscount";s:1:"0";
s:12:"attachsbytes";s:1:"0";
s:9:"avgrating";s:1:"0";
s:10:"lastupdate";s:1:"0";
}'
)

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '" . TABLE_PREFIX . "datastore( title,

DATA )
VALUES ( 'forum

[Owen]

false alarm, works fine without the query

[Boofo]

Yeah, I found out after coding this that you don't even really need to run the query to make the table. It will make itself when you first run the hack. KirbyDE pointed that out to me when I was writing this hack. ;)

[sabret00the]

prefered the old layout but oh well, heres an improved "forumdisplay_quickstats" template

<table class="tborder" cellpadding="$stylevar[cellpadding]" cellspacing="$stylevar[cellspacing]" border="0" width="100%" align="center">
<tbody>
<tr>
<td class="tcat"><strong>
<a style="float:$stylevar[right]" href="#top" onclick="return toggle_collapse('forumstats')"><img id="collapseimg_forumstats" src="$stylevar[imgdir_button]/collapse_thead$vbcollapse[collapseimg_forumstats].gif" alt="" border="0" /></a>
$foruminfo[title] Statistics</strong>
</td>
</tr>
</tbody>
<tbody id="collapseobj_forumstats" style="$vbcollapse[collapseobj_forumstats]">
<tr>
<td class="panelsurround" align="center">

<table class="panel" cellpadding="0" cellspacing="$stylevar[formspacer]" border="0" width="100%">
<tr>
<td align="$stylevar[left]">
<fieldset class="fieldset" style="margin:0px">
<legend>$statsupdate</legend>
<table cellpadding="0" cellspacing="$stylevar[formspacer]" border="0">
<tr>
<td colspan="2" nowrap="nowrap"><div class="smallfont">Total Number Of Threads: <strong>$forumstats[numthreads]</strong></div></td>
</tr>
<tr>
<td nowrap="nowrap"><span class="smallfont"><a href="$vboptions[bburl]/showthread.php?$session[sessionurl]goto=newpost&amp;t=$lastthreadid">Lastpost</a>: <strong>$lastpostmin </strong></span></td>
</tr>
<tr>
<td nowrap="nowrap"><span class="smallfont">Top Threads: <a href="member.php?$session[sessionurl]u=$forumstats[topthreadsid]" target="_blank">$forumstats[topthreadsun]</a> (<strong>$forumstats[topthreadscount]</strong> Threads = <strong>$forumstats[topthreadspercent]</strong>)</span></td>
</tr>
<if condition="$forumstats[attachscount] !='0'">
<tr>
<td nowrap="nowrap"><span class="smallfont">Total Attachments: <strong>$forumstats[attachscount]</strong> ($forumstats[attachsthumbs] - $forumstats[attachsfiles])</span></td>
</tr>
</if>
<tr>
<td nowrap="nowrap"><span class="smallfont">Average Thread Rating: <img class="inlineimg" src="$stylevar[imgdir_rating]/rating_$forumstats[avgrating].gif" alt="some text"></span></td>
</tr>
</table>
</fieldset>
</td>
<td align="$stylevar[left]">
<fieldset class="fieldset" style="margin:0px">
<legend>$statsupdate</legend>
<table cellpadding="0" cellspacing="$stylevar[formspacer]" border="0">
<tr>
<td colspan="2" nowrap="nowrap"><span class="smallfont">Total Number Of Replies: <strong>$forumstats[numreplies]</strong></span></td>
</tr>
<tr>
<td nowrap="nowrap"><span class="smallfont">Last Poster: <a href="member.php?$session[sessionurl]find=lastposter&amp;f=$forumid" target="_blank">$lastposter</a></span></td>
</tr>
<tr>
<td nowrap="nowrap"><span class="smallfont">Top Posts: <a href="member.php?$session[sessionurl]u=$forumstats[topposterid]" target=_blank>$forumstats[topposterun]</a> (<strong>$forumstats[toppostercount]</strong> Posts = <strong>$forumstats[topposterpercent]</strong>)</span></td>
</tr>
<if condition="$forumstats[attachscount] !='0'">
<tr>
<td nowrap="nowrap"><span class="smallfont">Total Attachments Bytes: <strong>$forumstats[attachsbytes]</strong></span></td>
</tr>
</if>
<tr>
<td nowrap="nowrap"><span class="smallfont">Total Thread Views: <strong>$forumstats[numviews]</strong></span></td>
</tr>
</table>
</fieldset>
</td>
</tr>
</table>
<tr>
<if condition="$bbuserinfo[userid] > 0">
<if condition="$yourposts != 0">
<td class="thead" align="center" nowrap="nowrap" colspan="2"><if condition="$forumstats[topposterun] == $bbuserinfo[username]"><strong>$bbuserinfo[username], you're this forum's <i>Top Poster</i> with a total of $yourposts</strong>. (<strong>$yourthreads and $yourreplies</strong>)<else /><strong>$bbuserinfo[username], you have made a total of $yourposts in this forum</strong>. (<strong>$yourthreads and $yourreplies</strong>)</if></td>
<else />
<td class="thead" align="center" nowrap="nowrap" colspan="2"><strong>$bbuserinfo[username], you have not yet posted in this forum</strong>.</td>
</if>
<else />
<td class="thead" align="center" colspan="2"><phrase 1="faq.php?$session[sessionurl]" 2="register.php?$session[sessionurl]">$vbphrase[first_visit_message]</phrase></td>

</if>
</tr>
</tbody>
</table>
<br />

[Boofo]

Can we see a screenshot, please? ;)

[sabret00the]

ok :)

basically it just removed the colour hardcoding and shows guests the welcome message instead of "unregistered you have 169 posts and 20 threads"

also changed the header to use tcat and the bottom to use tfoot for a cleaner look.

[Boofo]

If the user is unregistered, it should tell them they have made no posts in that forum.

Unregistered, you have not yet posted in this forum.

Do you allow guests to post?

[sabret00the]

If the user is unregistered, it should tell them they have made no posts in that forum.



Do you allow guests to post?
nope i don't but occasionally users get pruned and their posts remain so theirs something like 500 unregistered/guest posts, but with the conditional i managed to get rid of that

<if condition="$bbuserinfo[userid] > 0">
<if condition="$yourposts != 0">
<td class="thead" align="center" nowrap="nowrap" colspan="2"><if condition="$forumstats[topposterun] == $bbuserinfo[username]"><strong>$bbuserinfo[username], you're this forum's <i>Top Poster</i> with a total of $yourposts</strong>. (<strong>$yourthreads and $yourreplies</strong>)<else /><strong>$bbuserinfo[username], you have made a total of $yourposts in this forum</strong>. (<strong>$yourthreads and $yourreplies</strong>)</if></td>
<else />
<td class="thead" align="center" nowrap="nowrap" colspan="2"><strong>$bbuserinfo[username], you have not yet posted in this forum</strong>.</td>
</if>
<else />
<td class="thead" align="center" colspan="2"><phrase 1="faq.php?$session[sessionurl]" 2="register.php?$session[sessionurl]">$vbphrase[first_visit_message]</phrase></td>

</if>
so it displays seperate messages dependant on your userid being above zero

[Boofo]

You need to change this:

<if condition="$bbuserinfo[userid] > 0">

to this:

<if condition="$bbuserinfo[userid]">

since guests don't have a userid of any kind. ;)

[sabret00the]

nope it counts it as guests having a userid of 0

[Boofo]

nope it counts it as guests having a userid of 0

I'm sorry, big fella, but you are wrong on this one. ;)

* Boofo ducks and hides

Guests have NO userid and there isn't such a thing as a userid 0.

[sabret00the]

I'm sorry, big fella, but you are wrong on this one. ;)

* Boofo ducks and hides

Guests have NO userid and there isn't such a thing as a userid 0.
probably but it works, where as before it was showing unrigistered you have XXX posts and XXX threads

[surfhog]

This looks really kool and i am sure it's just what i was seeking :)

Please tell me that it works with vb3.0.0 ??

Thanks

[Boofo]

Should work just fine. ;)

[surfhog]

sorry to ask boofo

I am getting db error, can you tell me what i may have done wrong please and thanks

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]

Did you change this:

" . TABLE_PREFIX . "

to vb3

by chance?

[surfhog]

sorry boofo "newbie alert here" :( lol

Do you mean when i installed vb? if so then yes my db is set to vb3_

So I guess I have messed up now then?

[Boofo]

No, I mean when you ran the query? Did you change the query to show that yourself? ;)

[Boofo]

That's the top poster query, right? If it is, here is what you need to run for that query:

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

[surfhog]

ok i have missed something here :( I have not run any db query, so I must have missed something in your instructions. I have just made edits to forumdisplay.php file, by way of copy and paste from your instructions. Done template and new settings.

(-0-0-) Do you think I need some? lol

Thanks

[Boofo]

No, you did right. When you entered a forum, it runs the queries. Did you get any more errors or was that the only one?

Check the forumdisplay.php and make sure the query for the top poster is the one I posted in that last message. Let me know what you find out. We'll get it going for you, hang in there. ;)

[surfhog]

thanks boofo :)

okay I have double checked all the instructions, inc edits to forumdisplay.php, templates and new setting.

Now I am lost, because I do not even have the new template displayed, which i didnt before.

I am assuming that I should have run a sql query via phpadmin? or something I am missing something really silly here. lol lol :o

[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 (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 (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]

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:

$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. ;)

[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. ;)

$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]

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. ;)

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

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

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. ;)

$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]

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

$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
");

[surfhog]

it's party time LOL

it's working now, what did you say? it's working ;)

Thanks boofo :glasses: for me and beer for you :speechless:

[Boofo]

Apparently when you are using a table prefix you HAVE to declare the tables with a "table AS table" in it for each table (if you are calling more than 1 table in the query) or the table prefix gets confused, I guess. I hadn't been doing that because that is new in vB3 and I don't use a table prefix. Now that I know that (many, many thanks to NTLDR for enlightening me on this), I will start doing that in any future coding I do just so we can avoid all that you have had to go through for me not knowing that. ;)

It wasn't your fault, there were 3 queries doing that in this hack and as soon as we fixed one of them, it stopped at the next one. I should have looked at all of them the first time but, alas, I didn't. Sorry you had to go through all of this for such a small hack. ;)

I'm glad it's finally working for you, my friend. Enjoy! And thanks for the beer. I can sure use it now. ;)

You remembered the install button, right? ;)

[surfhog]

okay well it proved to be a task for both of us then and thanks for the explanation.

Thanks to NTLDR for all the help also :)

I have hit install on both now.

I am really pleased and can't stress enough how grateful i am for your brilliant support

[Boofo]

And we both learned something along the way. I guess we're never too old to learn, huh? ;)

Thank you for the kind words, sir. I really appreciate it. It feels kindof nice when someone appreciates the work that goes into a hack, no matter how small. If more users felt like you do, this would be a much happier place. I enjoyed our little adventure. ;)

[Nj?rd Eriksson]

And we both learned something along the way. I guess we're never too old to learn, huh? ;)

Thank you for the kind words, sir. I really appreciate it. It feels kindof nice when someone appreciates the work that goes into a hack, no matter how small. If more users felt like you do, this would be a much happier place. I enjoyed our little adventure. ;)
Boofo,

there are some problems with this hack. I didn't check all my forums because I have - sub-forums included - well over 200 but

1. one of them lists a user with user ID = 0 as top thread starter. If I search for him in the Admin CP, it tells me, he doesn't exist.

2. some forums list no top poster.

3. I know that your hack is not phrased, what is not a problem, as I can phrase it myself apart from one thing: the date that is not displayed in the German language system. Also affects your Forum Home Stats modification; and also the Arcade modification ... so maybe there is a general solution I can apply to all of them?

Screen shots are attached.

Your modification seems to work fine in most forums and sub-forums into which I surfed. Great work. :)

[Boofo]

The aracde modification doesn't have anything to do with this hack. As far as the date thing goes, I don't know what the german language uses so I couldn't change that. Just use whatever you use elsewhere on your site for the date and time part of it. ;)

As far as the other problems go, you are the first that has reported that so I'm not sure exactly what is going on. Have you updated your counters recently? You might want to try that and see if that fixes the problem, especially if you have deleted any users that were top posters in a forum at one time. Let me know. ;)

[Nj?rd Eriksson]

The aracde modification doesn't have anything to do with this hack. As far as the date thing goes, I don't know what the german language uses so I couldn't change that. Just use whatever you use elsewhere on your site for the date and time part of it. ;)

As far as the other problems go, you are the first that has reported that so I'm not sure exactly what is going on. Have you updated your counters recently? You might want to try that and see if that fixes the problem, especially if you have deleted any users that were top posters in a forum at one time. Let me know. ;)
Yes, I did. Still persists. There definitely was never a user Strapu having created 117 threads. Since his user ID is 0, I think the problem could be caused by members that were deleted (pruned)? Once about 800 members were pruned ...

So maybe a conditional of the sort "if user ID = 0 then skip" could solve the "top thread starter" problem? as far as the "top poster" (top poster doesn't appear in some forums) problem is concerned -- it might be related?

[Boofo]

Yes, I did. Still persists. There definitely was never a user Strapu having created 117 threads. Since his user ID is 0, I think the problem could be caused by members that were deleted (pruned)? Once about 800 members were pruned ...

So maybe a conditional of the sort "if user ID = 0 then skip" could solve the "top thread starter" problem? as far as the "top poster" (top poster doesn't appear in some forums) problem is concerned -- it might be related?
But after you deleted the users and updated the counters, it should have straightened itself out. Did you update the theread and forum counters as well as the postcount? You need to do that, too.

[Bison]

Apparently when you are using a table prefix you HAVE to declare the tables with a "table AS table" in it for each table (if you are calling more than 1 table in the query) or the table prefix gets confused, I guess. I hadn't been doing that because that is new in vB3 and I don't use a table prefix. Now that I know that (many, many thanks to NTLDR for enlightening me on this), I will start doing that in any future coding I do just so we can avoid all that you have had to go through for me not knowing that. ;)

It wasn't your fault, there were 3 queries doing that in this hack and as soon as we fixed one of them, it stopped at the next one. I should have looked at all of them the first time but, alas, I didn't. Sorry you had to go through all of this for such a small hack. ;)

I'm glad it's finally working for you, my friend. Enjoy! And thanks for the beer. I can sure use it now. ;)

You remembered the install button, right? ;)

Boofo, It would be nice if you changed the attachment instructions to reflect these changes for other who might experience this problem. Might save you the hastle of explaining this to other who don't want to wade through the threads to find a fix. ;)

[nydk]

Quick question... (not sure if it was addressed in this thread)

My Administrator usergroup has HTML markups to BOLD and change the font color of the username to RED. My Super Moderators are supposed to show up just BOLD... etc. etc.

Under "Currently Active Users", only the Administrator usernames reflect HTML markups. If an Admin is online, it shows their name BOLDED and RED, but other usergroups look like standard links(like regular, registered users).

Under "Number of Active Users Today", all usergroups' "HTML Markups" show up correctly, not just the Administrators.

Does anybody know how I would go about fixing this??? Thanks

[Boofo]

Quick question... (not sure if it was addressed in this thread)

My Administrator usergroup has HTML markups to BOLD and change the font color of the username to RED. My Super Moderators are supposed to show up just BOLD... etc. etc.

Under "Currently Active Users", only the Administrator usernames reflect HTML markups. If an Admin is online, it shows their name BOLDED and RED, but other usergroups look like standard links(like regular, registered users).

Under "Number of Active Users Today", all usergroups' "HTML Markups" show up correctly, not just the Administrators.

Does anybody know how I would go about fixing this??? Thanks
Wrong thread. ;)

[RetroDreams]

I, too, have tried to change the green in both places and it still shows green. Any idea?

[Boofo]

I, too, have tried to change the green in both places and it still shows green. Any idea?
Just look for:

<font color=green>

in the install file and change it to what you want where the code is at for the hack.

[RetroDreams]

Got it.

[Boofo]

Good. Now you can click the install button. ;)

[RetroDreams]

If you insist...

You can see it in action at: http://www.ka-alumni.org

[Boofo]

If you insist...

You can see it in action at: http://www.ka-alumni.org
That looks really good. ;)

[RetroDreams]

Thanks and thanks for the excellent hack! :nervous:

[pjdaley]

Boofo, im just thought of it... first of all thanks for the great hack... you see on the last table, how it gives you the number of threads? you think its possible to get a total number of lets say the number of threads of a few forums, of a certain member and add them up?

[Boofo]

Boofo, im just thought of it... first of all thanks for the great hack... you see on the last table, how it gives you the number of threads? you think its possible to get a total number of lets say the number of threads of a few forums, of a certain member and add them up?
Since this shows in each forum, that really wouldn't be accurate to add other forum thread counts to each forum. ;)

Try my vB3 Threads Started in postbit, pm, usernotes, showpost and profile v1.3 hack. That might be more of what you are looking for for this.

You forgot the install button, sir. ;)

[pjdaley]

(install has been clicked).... boooofooooo i need this hackkkkk.... is it hard to do? count threads of alot of forums to one sum?

[pjdaley]

that one has all the forums into 1 sum right? i can just choose 5? forums to count? (good job by the way)

[Boofo]

that one has all the forums into 1 sum right? i can just choose 5? forums to count? (good job by the way)
No, that hacks shows ALL of the threads that each user has started right next to how many posts they have made, in the postbit.

[Nj?rd Eriksson]

But after you deleted the users and updated the counters, it should have straightened itself out. Did you update the theread and forum counters as well as the postcount? You need to do that, too.
Yes, I have updated all these counters. I do so quite regularly, whenever I change someone's username. However, vBulletin keeps a number of user records that equals the total number of registrations; and not the active registrations. All those users that have been pruned seem to be assigned no user ID (or an ID of 0) -- that's what I assume at least.

I also have been mistaken about this user "Strapu", which allegedly is the "Top Thread Starter" in this forum:

Top Threads: Strapu (117 Threads = 12.39%)

He did indeed exist, and has been pruned at some stage (what explains his user ID 0). He, however, definitely didn't start 117 threads in this forum -- that's rather the number of threads started by all pruned users combined. He did create -- amongst others -- the first thread in this forum, however.

The

Top Posts: (1,117 Posts = 7.83%)

problem where no user is displayed is then probably related. That should again be the total number of posts made by all pruned users combined.

[Osterling]

These maybe two stupid qestiosn but..

1.) how do you debug
and
2.) how do you add a settings

[Osterling]

anyone?

[Elmer]

anyone?
Edit your config.php and put this variable anywhere in the config.php file

$debug = "1";

next, go to your admincp and in your vBulletin Options select Forum Display Option (forumdisplay)
onece there, just click the link Add New Setting

that should work ;)

[psico]

There is a problem with the queries, I found the solution:

In forumdisplay.php find this code:

$topthreads = $DB_site->query_first("
SELECT COUNT(threadid) as threads, postusername, postuserid
FROM " . TABLE_PREFIX . "thread
WHERE forumid=$forumid
GROUP BY postuserid
ORDER BY threads DESC, dateline ASC
LIMIT 1
");

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


Replace with this code:


$topthreads = $DB_site->query_first("
SELECT COUNT(threadid) as threads, postusername, postuserid
FROM " . TABLE_PREFIX . "thread
WHERE forumid=$forumid AND postuserid <> 0
GROUP BY postuserid
ORDER BY threads DESC, dateline ASC
LIMIT 1
");

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


That's all!

See ya.