1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Recalculating Message count

Discussion in 'Troubleshooting and Problems' started by Edrondol, Jan 10, 2011.

  1. Edrondol

    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?
     
  2. Brogan

    Brogan XenForo Moderator Staff Member

    Are the counts incorrect?

    Have you tried rebuilding the caches in the ACP?
     
  3. Edrondol

    Edrondol Well-Known Member

    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.
     
  4. Brogan

    Brogan XenForo Moderator Staff Member

    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
     
  5. Edrondol

    Edrondol Well-Known Member

    Precisely why I haven't done it. [​IMG]
     
  6. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    I don't think this can be done with a single query. Or at least I don't know how. I think you need a script for this.
     
  7. Edrondol

    Edrondol Well-Known Member

    *le sigh*

    Okay. Thanks.
     
  8. Brogan

    Brogan XenForo Moderator Staff Member

    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.
     
  9. Edrondol

    Edrondol Well-Known Member

    1000+ users. I'll wait.
     
  10. Brogan

    Brogan XenForo Moderator Staff 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.
     
  11. Edrondol

    Edrondol Well-Known Member

    Wow. Unexpected. Let me work on it.
     
  12. Brogan

    Brogan XenForo Moderator Staff Member

    No need.
    Jake's going to post the queries once he's prepared them.

    Just send me the output in csv format from phpMyAdmin and then I can do the rest.
     
  13. Edrondol

    Edrondol Well-Known Member

    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.
     
  14. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Run this query in phpmyadmin and give Brogan the CSV:

    Code:
    SELECT user_id, COUNT(*) AS posts
    FROM xf_post
    GROUP BY user_id
    
    This will return userids and associated post counts.
     
    Shelley likes this.
  15. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

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

    Export as a CSV.

    Or Brogan can probably do it if you give him your CPanel login.
     
  16. Edrondol

    Edrondol Well-Known Member

    Already sent. I just didn't know th ecommands for the SQL.

    For which I thank you.
     
  17. Brogan

    Brogan XenForo Moderator Staff Member

    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
     
  18. Edrondol

    Edrondol Well-Known Member

    Worked like a charm. And IIRC there are a few others with this same issue.
     
  19. Edrondol

    Edrondol Well-Known Member

    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.
     
  20. Brogan

    Brogan XenForo Moderator Staff Member

    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?
     

Share This Page