Homepage Statistics Cache

[Tigga]

A little while ago I ran into this problem... I like having a lot of statistics on my forum's homepage, but I don't like having a lot of queries on that page. That got me to start thinking of a better way to do it, hence the creation of this hack.

How does this hack work?
It's pretty simple really. Instead of counting every single thread, post, member and etc every time someone loads your forum, it only counts these after a specified amount of time and updates one table that the information will be pulled from. This hack is especially useful for high traffic sites and can reduce the time it takes to load your forum's homepage.

Statistics Included:
Total Threads
Threads Today
Total Posts
Posts Today
Total Members
Newest Member
Top Poster
Top Thread Starter
If you are an admin, it will show you the last time the stats were updated as well.

(Please note that some of these started as hacks from other members at vB.org. Most of them are so simple though I don't see much of a point in trying to figure out which one's I got from here X months ago. If your hack was included here and you would like credit given please post here and I will add it.)

Normally these stats would add 8 queries to your forum's homepage. With this hack installed it will only perform 1 query most of the time, and 9 queries when it needs to update the information. I have found this very useful to cut down on the number of queries on my homepage, decrease the page's loading time, and put less overall stress on my server.

Well that's about it. I hope some others will find this hack useful and if anyone has some suggestions for other stats they would like to include in the stats cache please let me know and I will try to implement them.

[Tigga]

There's not much to it, but here's a screen shot as well. :)

[Lethal]

looks nice, ill try this out ty

[SgtSling]

this also makes my last post on forum home hack update every ten mins along with the rest of the stuff you included above.
Is there anyway I can change this?

[SgtSling]

clicksm install

[SgtSling]

nevermind.. my fault
It saves 8 queries per load for me

[SgtSling]

How can we also cache the moderatorlist ?
XENON has a hack for this.. but it isn't compatable with this hack
https://vborg.vbsupport.ru/showthread.php?s=&threadid=46848&perpage=15&display=&pagenumber=1

[Tigga]

Thanks for pointing that out SgSling. I had forgotten about his modifications (I have Xenon's hack installed too). He saves another query by counting the posts in the thread query, so to use that with this hack you would just replace this (in the hack itself, or index page if you've already modified it):
$countthreads=$DB_site->query_first('SELECT COUNT(*) AS threads FROM thread');

// get total posts
$countposts=$DB_site->query_first('SELECT COUNT(*) AS posts FROM post');
$totalposts=number_format($countposts['posts']);

With:
// get total posts
$counters=$DB_site->query_first('SELECT SUM(replycount) AS posts, SUM(threadcount) AS threads FROM forum WHERE parentid=-1');
$countposts[posts]=$counters['posts'];
$countthreads[threads]=$counters['threads'];
That should be the only conflict with the 2 hacks, and changing that saves yet another query when it updates the cache. :)

[Snapperhaed]

Lets say I wanted to add a few more items to be cached ...

1 - Total Thread Views
2 - New Members Today
3 - Most popular thread by # of Replies

These 3 items I have installed, but they dont show now since I added this hack. (A nice one might I add).

I know (or i think rather) I need to add them into the table via a query, then adjust accordingly on the forumhome template ... But since im new to it, (i already ran the intial query), how can I add to the exist newly created table? (IE: add this to the statscache table) ...

Any/All help appreciated!!

[Tigga]

Snapperhaed - The first 2 wouldn't be hard to add at all. If you'll give me a link to the 3rd one you're talking about I'll have a look at it to see what would need to be done to add it as well. :)
Oh, and those things should still work fine without the stats cache as long as you didn't modify any of the code that was for those hacks...

[Snapperhaed]

Tigga, #3 can be found at:
https://vborg.vbsupport.ru/showthread.php?s=&threadid=25755

Thanks!! I'll also peep to see what I may have altered. :D

[Tigga]

Well here's the first two for you. The 3rd looks like it would take a little more work so I'll have to post that for you tomorrow.

For members today and total thread views, first run this query via phpMyAdmin:

ALTER TABLE `statscache` ADD `threadviews` INT( 10 ) UNSIGNED DEFAULT '0' NOT NULL ;
ALTER TABLE `statscache` ADD `memberstoday` INT( 10 ) UNSIGNED DEFAULT '0' NOT NULL ;

Then open your index.php file (assuming you've already installed the hack) and look for:
$getstats[posttoday]=number_format($getstats['posttoday']);
Below that Add:
$getstats[threadviews]=number_format($getstats['threadviews']);
$getstats[memberstoday]=number_format($getstats['memberstoday']);
Then look for:
$poststoday=$getpoststoday[count];
Below that Add:
// members today
$getmemstoday=$DB_site->query_first("SELECT count(*) AS count FROM user WHERE joindate>='$datecut'");

// thread views
$getthreadviews=$DB_site->query_first("SELECT SUM(views) AS tviews FROM thread");
Then look for:
posttoday='$poststoday', lastupdate='".time()."'");
Replace that with:
posttoday='$poststoday', lastupdate='".time()."', threadviews='$getthreadviews[tviews]',memberstoday='$getmemstoday[count]'");
Then just add the variables $getstats[threadviews] and $getstats[memberstoday] in your forumhome template where you would like those to appear. (Remember it will take 10 minutes, or however long you set between updates, before those will appear.)

[Snapperhaed]

Thank You, Thank You, Thank You!!

Installed and worked perfectly. Many thanks for the assistance!
As a side note, I checked to make sure none of the original code was altered, before install, and it wasnt. But soon as I made the changes you provided, cured the problem right up!

Again, Thank you! Your the greatest. :p

[Logician]

caching such stats are always a good idea, especially in forum home, so very good job! :)

[Tigga]

Snapperhaed - No problem at all. I've added those stats to my forum page as well. :)
By the way, I noticed that you have mYvBindex installed on your site. You can use the same code there to pull the info and save 4 queries on your homepage (and add more stats) as well. ;)

Logician - Thank you. :D

[mossyuk]

1997 PMs?!?! Is that in total or just yours? Wow :)

[Boofo]

Ok, I got the other 2 figured out (finally). This is what I have left. Can anyone please tell me how to incorporate this, too?

//Page Counter Code
$mycounter = $DB_site->query_first("SELECT count FROM mycounter");
$DB_site->query("UPDATE mycounter SET count = count + 1");
$mycounter = number_format($mycounter['count']);
//Page Counter Code

[Kars10]

Works like a charm and saves me about 5 Querys on Forumhome / and vBindex!!

Thanks
Kars

* Kars10 kicks install! :)

[Tigga]

mossyuk - Yep, those are all mine. The board's been up for a little over a year and I rarely delete anything, so I guess they add up pretty quickly. :)

Boofo - I don't think that's something that should really be integrated with this... Since it's a page counter, updating it only every ten minutes wouldn't allow it to be accurate. It appears that it would only be 2 very small queries and a small table though, so it shouldn't affect performance much at all.

Kars10 - Glad you like it. :)

[Boofo]

You're right. I got to thinking about it after I wrote the message. I went from 31 queries down to 23 (and at 35 when the cache updates) and I have a couple of more stats than I had before. I did the Top Profile Views with this, too, if anyone is interested. ;)

[Lethal]

did u update the attachment with the add-ons?

[Lethal]

great hack, saves me 7 queries nice

[Austin Dea]

Very nice hack, man. Here's updated instrucs with the add-ons.

[Dean C]

Awesome idea Tigga :)!

- miSt

[Prankster]

04-04-03 at 11:53 PM Lethal said this in Post #22 (https://vborg.vbsupport.ru/showthread.php?postid=377642#post377642)
great hack, saves me 7 queries nice


* Prankster too

great idea
thanks

greets Prankster

[Areku]

OK, what should we do if we have some or all of the old hacks your new hack is based on so we can truly save queries instead of adding 9 new to the 8 old ones? ;)

[Tigga]

If you have all (or some) of these stats installed already, you would pretty much just replace the coding for those stats with the new code for the stats cache. It shouldn't be too hard to figure out what to replace, but if you have problems with it let me know.

[Mijae]

Database error in vBulletin 2.3.0:

Invalid SQL: UPDATE statscache SET members='1617',threads='2145',posts='39246',topthr ead='Mijae',topthreadid='1',to pthreadnumber='186',topposter='Jim',topposterid='1 53',toppostnumber='2496',newes tmember='umar'newestmemberid='1617',threadtoday='0 ',posttoday='7',lastupdate='10 50682471', threadviews='259042',memberstoday='2'
mysql error: You have an error in your SQL syntax near 'newestmemberid='1617',threadtoday='0',posttoday=' 7',lastupdate='1050682471', thr' at line 1

mysql error number: 1064

[Tigga]

It looks like a comma is somehow missing in that query. Make sure you have a comma right before 'newestmemberid' in the query that updates the cache.

[Areku]

Up and running in v2.2.0!!!

[Areku]

Btw,

- how do i change ',' char to '.' char (eg.: 325,500,934 to read 325.500.934)
- how do i know how many queries currently my homepage loads?

Ideas?

[Areku]

DO NOT cache the SINCE YOUR LAST VISIT! hack by MrLister (https://vborg.vbsupport.ru/showthread.php?s=&threadid=31957). Those of you who installed the WELCOME HACK [FINAL] are also affected, like me!

:P

[Tigga]

Areku - If you'll refer to a post I made here (https://vborg.vbsupport.ru/showthread.php?postid=382385#post382385), that should help you. It's for a different hack, but it should be enough to help you figure out how to change the commas to periods. ;)
As for figuring out how many queries a page has, I would recommend installing Teck's Microstats hack. Alternatly you could open your admin/config.php file and put the code $debug="1"; right at the end of that file before the ?>. Then you would go to the url http://yoursite.com/forum/index.php?explain=1. At the bottom of that page it will show you the number of queries and the time it took to execute them.

[Areku]

Does any1 know what's wrong with the SINCEYOURLASTVISIT addon I published 2 posts ago (https://vborg.vbsupport.ru/showthread.php?postid=386005#post386005)?

Yesterday was working fine but today it displays Han habido 5,770 hilos y 33,486 mensajes desde tu ?ltima visita!

that is, 33 thousand messages since my last visit, 30 seconds ago!!!!


Wth is going wrong??

[Tigga]

Yea, actually I just realized what you did with the new posts since a users last visit. You should not have done that... Every time it updates the cache, it will display the number of new posts for that particular user, so all users will see the same number of new posts and threads. There's really not a way to cache those since they are specific for each member.

[Areku]

Damn, I really messed it up! :P

OK I'll edit the previous post.

Can you now explain me when does the update happen actually?

It's 0:01am here (server time) and update has been done for this new day, but it's still counting/displaying yesterday's new members/posts/threads...

[Tigga]

Well actually it should go by the time that is set on the server to determine when a new day starts. Then it should update that table every 10 minutes (assuming you didn't change the value), so within 10 minutes it *should* be correct. I'll work on changing it so that it will go by your forums timeoffset values as well though since that would make a little more sense.

[Boofo]

Today at 04:37 PM Tigga said this in Post #37 (https://vborg.vbsupport.ru/showthread.php?postid=386642#post386642)
Well actually it should go by the time that is set on the server to determine when a new day starts. Then it should update that table every 10 minutes (assuming you didn't change the value), so within 10 minutes it *should* be correct. I'll work on changing it so that it will go by your forums timeoffset values as well though since that would make a little more sense.

Couldn't you just change this:

lastupdate='".time()."'

to something like this?

lastupdate='".$ourtimenow."'

[Tigga]

Bofo - Actually the only thing that would affect is where it shows an admin the last time the cache was updated. The $datecut variable is what would need to be modified. ;)

[Areku]

I later realized you're right... server is on PST and I'm on GMT so it used server's time to update, presenting us "today registered users" when that was not true for our time zone ;)

Will wait for your fix then ;)

[Boofo]

Yesterday at 05:23 PM Tigga said this in Post #39 (https://vborg.vbsupport.ru/showthread.php?postid=386661#post386661)
Boofo - Actually the only thing that would affect is where it shows an admin the last time the cache was updated. The $datecut variable is what would need to be modified. ;)

It was just a guess, and not a very good one it seems. ;) I'll wait for your update then instead of messing it up. ;)

[Sanjiyan]

Can you get this to cache the front page stats on vbhome lite?

Heres the link to the stats listed:

www.theborg.cjb.net

the stats are along the left hand side.

thanks

[Boofo]

04-24-03 at 06:23 PM Tigga said this in Post #39 (https://vborg.vbsupport.ru/showthread.php?postid=386661#post386661)
Bofo - Actually the only thing that would affect is where it shows an admin the last time the cache was updated. The $datecut variable is what would need to be modified. ;)

Tigga, did you get a chance to look at this yet?

[corsacrazy]

mate how can i exempt a certain user id out of the Top Thread Starter ?

[Tigga]

Boofo - Actually I did. I just updated the text file with the new code. For anyone who's already installed this, you can just look for this code:
$datecut = mktime(0,0,0,date("m"), date("d"), date("y"));
And replace it with this:
$datecut = mktime(0,0,0,date('m',"$ourtimenow"), date('d',"$ourtimenow"), date('y',"$ourtimenow"));
There's no good way to have it go by the user's time offset, but this does at least go by your forum's default time.



corsacrazy - You would just need to look for this code:
$topposter=$DB_site->query_first("SELECT username,posts,userid FROM user ORDER BY posts desc LIMIT 1");
And replace it with this:
$topposter=$DB_site->query_first("SELECT username,posts,userid FROM user WHERE userid!='XX' ORDER BY posts desc LIMIT 1");
(Replace XX in that query with the user's id)

[Boofo]

Could you use that here, too?

// members registered today
$datecut = mktime(0,0,0,date("m,$ourtimenow"), date("d,$ourtimenow"), date("y,$ourtimenow"));
$getmemstoday=$DB_site->query_first("SELECT count(*) AS count FROM user WHERE usergroupid=2 AND joindate>='$datecut'");

[Boofo]

Shouldn't this:

$datecut = mktime(0,0,0,date("m,$ourtimenow"), date("d,$ourtimenow"), date("y,$ourtimenow"));

be like this?

$datecut = mktime(0,0,0,date("m",$ourtimenow), date("d",$ourtimenow), date("y",$ourtimenow));

[Tigga]

Actually you can just remove that 2nd instance of $datecut. They will both have the same result, so there's no point in having it there twice. I thought I had already taken that out, but I guess I forgot about it.
Thanks for pointing out the errors in the code. I guess I was more tired than I thought when I did that. I updated the file and the post above with the correct code though. :)

[corsacrazy]

i set the user id to 0 as i hav the welcome hack installed and every time sum one registers a post is created to include the details of that person and the thread started is user id 0, so therefore the top thread starter is user id 0 but i get this error in index when adding 0 to where u said


Invalid SQL: SELECT COUNT(*) AS count,postuserid,postusername FROM thread WHERE userid!='0' GROUP BY postuserid ORDER BY count DESC LIMIT 1
mysql error: Unknown column 'userid' in 'where clause'

mysql error number: 1054

[Tigga]

Blah, sorry about that. Try replacing userid with postuserid. :)

[corsacrazy]

^ much better cheers ;)

[Crazy Pete]

I can't figure out how to get Link14716's Newest Member's First Post! (v1.1) to work with this hack. It shows the member's first post for the first 10 minutes until the cache updates again, then it disappears and seems to pop back in at random. Any ideas? The query for the post is...

// get newest user's first post
if ($getnewestusers['lastpost']!=0){
$getnewusersposts=$DB_site->query_first("SELECT * FROM post WHERE userid=$newuserid LIMIT 1");
$userfirstpost=$getnewusersposts['postid'];

if (!$getnewusersposts) {
$firstpost="";
} else {
$firstpost=" | <a href='showthread.php?postid=$userfirstpost#post$us erfirstpost'>First Post!</a>";
}
}

[Crazy Pete]

No one?

[Tigga]

Sorry Crazy Pete, for some reason I don't think I got email notification the first time you replied to this. To get that to work with the cache you will have to add some extra fields to the statscache table that can be updated. Or to just get it to work properly without being cached you would need to make sure your code isn't in the part where it gets the information for the cache. If you look for this code:
$statsupdate = '';
And put your code for the newest member's first right above that it should work fine.

[Crazy Pete]

Thanks Tigga, I'll try that and see if it solves the problem. :)

[Lethal]

sometimes my (total) # of users don't show up. The active users show up all the time but my total users don't. Just everyone once and awhile they do which is weird becuase if they show up once, shouldn't they show up all the time?

[Till]

<a href="http://0x00.org/php/phpCache/" target="_blank">http://0x00.org/php/phpCache/</a>

I use this one to cache various things on our forums. For example, all the forum jump dropdowns (yeah, how often do you modify your forums, yet this is pulled from the database on nearly every page).

[blakkboy]

i followed the directions and this is what i got

[futureal]

Out of curiosity, instead of updating every 10 (or however many) minutes, why not just create a cache table in your database and then update it whenever actions take place? That way, you still get "real time" stats but you still only need a single query on forumhome.

For example, you could:

- update cache whenever a post is made (posts today, total posts)
- update cache whenever a thread is started (total threads, total posts, posts today)
- update cache whenever someone registers (total members, newest member)

...and so on and so forth.

Is it just too much of a pain to implement it that way, or is there something else I am missing?

I like the idea of caching stats, but I am also bent on getting real-time stats. :)

[Tigga]

Well part of the point of this hack is to help decrease strain on the server. If you're updating that table every time a post/thread is made, a new user joins, and etc then that's quite a few more queries to the database. If you're that bent on getting real-time stats that method would work and your forum homepage would load quicker with all the stats, but it would be hard to tell if it would be any better or worse on your server...

[futureal]

It would certainly be easier on your server. Adding a single query when a new post is made does not add much strain on the server at all. Even on larger sites, the ratio of forumhome views to new posts has to be 40:1 or 50:1, if not greater. And obviously even moreso for new users and that sort of thing.

Anyway, just thoughts. :)

[blakkboy]

i could i add this in my welcome panel???

[julius]

Thanks!
Click install.

[poetic]

does this work with filberts hack that optimizes the forumhome??

[blakkboy]

i could i add this in my welcome panel???


let me rephrase my question

how can i add this in my welcome panel hack???