WordPress Quick Tip: Fixing the number of comments per post
January 7, 2009
For various reasons, sometimes the number of comments shown under the title of the post may not match the number of actual comments displayed under the post.
Here is a simple SQL statement that will resolve the issue:
update `wp_posts` set comment_count = (select count(*) from wp_comments
WHERE `comment_post_ID` = `ID` and comment_approved = '1')
You can use this to verify what would change and where your problems may lie:
SELECT ID, `post_title`, `comment_count`,
(select count(*) from `wp_comments`
WHERE (`comment_post_ID` = `ID`) and (`comment_approved` = '1')) as NewCC
FROM `wp_posts`
WHERE `comment_count` <> (select count(*) from `wp_comments`
WHERE (`comment_post_ID` = `ID`) and (`comment_approved` = '1'))