Miscellaneous Hacks - Threads Started by User in Postbit and Profile

vB3.5.4 Threads Started by User in Postbit and Profile
Version 1.0.1
(By Andreas, Boofo, Hambil and Paul M.)

What does this product do?
This product counts the threads started by a user and displays it in the postbit and the profile. It will also display the threads per day in the profile. The thread count is automatically updated hourly via cron job. New threads are added to the count in real time. No extra queries are run in the postbit or profile when getting the thread count, only when the cron job runs. This product has been tested on a live board with over 15,000 users and 40,000 threads and ran unnoticeably in the background in 0.3 seconds during the cron job update. This product only counts valid threads (not moderated or soft-deleted threads).

NOTE: This Hack requires at least mySQL 4.0.4

Version Information:
Version 1.0.0 --Initial release
Version 1.0.1 --No longer counts threads in forums that have the "Count Posts Made in this Forum Towards User Post Counts" option set to No. Also made the posts and threads counts in the postbit clickable search links like in the profile.

NOTE: You must redo the postbit template edit to get the clickable links.

Credits:
Thanks goes out to Andreas for the original version of this product. Thanks also goes out to Paul M for coming up with the way to update the thread count without adding a query to every post, and to Hambil for coming up with the way to not count threads in forums where the "Count Posts Made in this Forum Towards User Post Counts" option is set to No. Thanks guys!

Details
---------
1 Product XML (4 Plugins, 2 Phrases)

[Boofo]

You need to post the whole error. I can't tell much with what you have there.

[evofile]

Database error in vBulletin 3.5.4:
Invalid SQL:
UPDATE user AS user 
SET user.threads =
(
SELECT COUNT(threadid) 
FROM thread AS thread
LEFT JOIN forum AS forum ON forum.forumid = thread.forumid 
WHERE visible = 1 
AND (forum.options & 4096) 
AND user.userid=postuserid
);
MySQL Error : 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 'SELECT COUNT(threadid) 
FROM thread AS thread
LEFT JOIN f
Error Number : 1064
Date : Monday, May 15th 2006 @ 09:41:36 PM
Script : http://forum.evofile.com/cron.php?&rand=694578
Referrer : http://www.evofile.com/
IP Address : 69.86.205.252
Username : Unregistered
Classname : vb_database

[Boofo]

Originally Posted by evofile

Database error in vBulletin 3.5.4:
Invalid SQL:
UPDATE user AS user 
SET user.threads =
(
SELECT COUNT(threadid) 
FROM thread AS thread
LEFT JOIN forum AS forum ON forum.forumid = thread.forumid 
WHERE visible = 1 
AND (forum.options & 4096) 
AND user.userid=postuserid
);
MySQL Error : 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 'SELECT COUNT(threadid) 
FROM thread AS thread
LEFT JOIN f
Error Number : 1064
Date : Monday, May 15th 2006 @ 09:41:36 PM
Script : http://forum.evofile.com/cron.php?&rand=694578
Referrer : http://www.evofile.com/
IP Address : 69.86.205.252
Username : Unregistered
Classname : vb_database

What version of MySQL are you running on the server?

[evofile]

Originally Posted by Boofo

What version of MySQL are you running on the server?

4.0.24

[Boofo]

Hang in there, we're looking at it now.

[Oblivion Knight]

Yup, I'm getting the same error but with vBulletin 3.6.0 Beta 2..

It might have been doing it before the upgrade, but I've only just re-added an email address to receive database error messages (they do my head in!).. Running MySQL 4.0.27-standard.

[Boofo]

Originally Posted by Oblivion Knight

Yup, I'm getting the same error but with vBulletin 3.6.0 Beta 2..

It might have been doing it before the upgrade, but I've only just re-added an email address to receive database error messages (they do my head in!).. Running MySQL 4.0.27-standard.

I thought 3.6 took a higher MySQL version that that.

[Oblivion Knight]

Originally Posted by Boofo

I thought 3.6 took a higher MySQL version that that.

From vbulletin.com:
vBulletin 3.6 now requires PHP 4.3.3 or later and MySQL 4.0.16 or later.

[Boofo]

I'm using 4.1.11 and this hack works fine with that. I have a fix for it for older versions somewhere.

[Freesteyelz]

Genius! Thank you guys! *Clicked Install*