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?
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)
