Recalculating Message count

Edrondol

Well-known member
I know this is probably easy for someone out there, but I want to recalculate the message counts.

I assume that all that really needs to be done is a simple SQL command that counts posts and increments that person's message count, but I'm not good with SQL and would not trust myself to try it out.

Any ideas or is this harder than I'm thinking it is?
 
When I did the move from vB to XF I tested several data imports. What this did essentially was to double, then triple, then quadruple the counts. I repaired the caches and it did nothing. I then zeroed everything out and tried the cache repairs again. Nada.

So right now everyone's counts are very low and only reflect messages since the switch.
 
I see.

So essentially you need to run a query to count the number of messages for each user_id in the xf_user table and then insert the value into xf_user:message_count

My SQL isn't the best and I'd probably end up wiping your whole board so I'll leave this one for Jake :D
 
Precisely why I haven't done it.
wink.png
 
How many members have you got?

You could do a query to do a count and return user_id and message_count.
And then do another query inserting the count value for each member.
 
We're on it ;)

If you can run a query to list the message count per user and send me the output, I can build the second batch of queries for you to insert the values.

Jake will be posting the query shortly.
 
I've got to figure out a way to do that, first. When I said I'm not good with SQL I meant I'm REALLY not good with SQL. I'm scouring documentation right now.
 
I've got to figure out a way to do that, first. When I said I'm not good with SQL I meant I'm REALLY not good with SQL. I'm scouring documentation right now.

Is this for halforums.com? That is a CPanel server which will have phpmyadmin installed in your CPanel:

http://www.halforums.com/cpanel/

Open phpmyadmin, select your database on the left, click the SQL tab on the right, paste my query, run it, scroll to the bottom and click Export:

Screen shot 2011-01-10 at 12.47.54 PM.webp

Export as a CSV.

Or Brogan can probably do it if you give him your CPanel login.
 
I've just sent you a link to the second set of queries to run.

Take a backup first and let us know how it goes.

If it all goes horribly wrong, speak to Jake :D
 
Interestingly enough it worked...but I still have to turn them off. Seems that the counts are again double/triple/quadruple postings. So the database has multiple messages with different post IDs, etc. So it's abnormally inflating the numbers. So I have to remove the duplicates and then do this all again.
 
For new posts to increase the post count by more than 1 would suggest a bug.
I can understand why doing multiple imports would increase the values but not new posts.

Are these duplicate posts showing up in threads, or just in the database?
 
Top Bottom