Post Count SQL Query

[MattGarner]

I did this before but a user went missing during a database crash and we had to bring the user back. But their post count says 0 and I remember running a query to fix that but can no longer find what I used. Could someone let me know what it is again?

Cheers!

[kh99]

You could use Maintenance -> General Update Tools -> Update Post Counts to recalculate it for all users.

If you want to do it manually for one user you could use this query:

SELECT COUNT(*) AS posts FROM post
INNER JOIN thread ON (thread.threadid = post.threadid)
WHERE post.userid = ID
AND thread.visible = 1
AND post.visible = 1


(obviously replace ID with the user's id) then enter the resulting count using the user manager (You could have a query to set the value, and maybe even one query to calculate and set in one operation, but it doesn't seem worth figuring it out).

I should mention that I looked at the code in admincp/misc.php that recalculates user post count when you do it form the maintenance menu, and it accounts for any forums marked as not included in user post counts, while the query above doesn't. So if you have any forums like that you probably just want to use the maintenance menu.