6

Recently, I had to move to another domain and had to recreate all my MySQL tables on the enw server. I noticed that for some posts, the number of comments is doubled. Example - http://inversekarma.in/scraps/you-dont-mess-with-the-rajinikanth/.

In the above post, there's only 4 comments, but the comment count shoes 8 comments. I am not sure it is not a theme-related issue, as this happens only for a few posts.

Can someone show me how to write a MySQL query to recount the comments for all posts? Or maybe a plugin?

Improve this question

3 Answers 3

Reset to default
5

Try this code:

WARNING: THIS IS JUST PSEUDOCODE!

$entries = $wpdb->get_results("SELECT * FROM wp_posts WHERE post_type IN ('post', 'page')");

foreach($entries as $entry)
{
    $post_id = $entry->ID;
    $comment_count = $wpdb->get_var("SELECT COUNT(*) AS comment_cnt FROM wp_comments WHERE comment_post_ID = '$post_id' AND comment_approved = '1'");
    $wpdb->query("UPDATE wp_posts SET comment_count = '$comment_count' WHERE ID = '$post_id'");
}

Or you might want to try solution from this page (although it's not the proper way as you will add another query for every post)

Improve this answer
5
  • Thanks for the reply. But where do I run this from? Also, wouldn't it be simple to just run it as a bunch of pure MySQL queries?
    – GPX
    Dec 6, 2010 at 7:38
  • 1
    you can run it in your index.php, if you want.
    – ariefbayu
    Dec 6, 2010 at 7:48
  • But why mark it as "THIS IS JUST PSEUDOCODE"?
    – GPX
    Dec 6, 2010 at 8:24
  • Because I didn't test it. I can't say it work.
    – ariefbayu
    Dec 6, 2010 at 8:34
  • Well it did work like a charm. A note to others who might want to try this, the table prefix needs to be changed to whatever you've used when setting up WordPress. For most people, though, the default is 'wp_'.
    – GPX
    Dec 6, 2010 at 9:52
5

Seems to be a common problem. Try this plugin:

http://wordpress.org/extend/plugins/web-ninja-comment-count-fixer/

Improve this answer
3
  • Running the query (in the solution marked as the answer) once, IMHO, is much better than the overhead of another plugin!
    – GPX
    Dec 6, 2010 at 8:27
  • 4
    Absolutely. Just easier for someone unwilling to modify code and can be deactivated once complete.
    – Jon
    Dec 6, 2010 at 9:02
  • Yep. It's better than modify code and it's true that it's easier to activate this plugin and deactivate once you've done with it.
    – ariefbayu
    Dec 6, 2010 at 10:20
1

There's a Wordpress API just for that: wp_update_comment_count($post_id, $do_deferred). Granted, it operates on a single post, although it has a deferring mechanism to accumulate many of them and just update the count in one go.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.