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?

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)

  • 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 '10 at 7:38
  • 1
    you can run it in your index.php, if you want. – ariefbayu Dec 6 '10 at 7:48
  • But why mark it as "THIS IS JUST PSEUDOCODE"? – GPX Dec 6 '10 at 8:24
  • Because I didn't test it. I can't say it work. – ariefbayu Dec 6 '10 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 '10 at 9:52
5

Seems to be a common problem. Try this plugin:

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

  • Running the query (in the solution marked as the answer) once, IMHO, is much better than the overhead of another plugin! – GPX Dec 6 '10 at 8:27
  • 4
    Absolutely. Just easier for someone unwilling to modify code and can be deactivated once complete. – Jon Dec 6 '10 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 '10 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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