Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 5 down vote accepted

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)

share|improve this answer
    
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
    
Ran it, and works! Thanks a ton! –  GPX Dec 6 '10 at 8:20
    
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

Seems to be a common problem. Try this plugin:

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

share|improve this answer
    
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
3  
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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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