How To Diagnose And Fix Incorrect Post Comment Counts In WordPress
| Share |
Introduction
If your WordPress comment counts got messed up, whether because of a plugin (I'm talking about you, DISQUS) or you messed with your database manually and did something wrong (yup, that's what I just did), fear not – I have a solution for you.
But first, a little background.
Comment Counts In WordPress
Here's how comment counts work in WP:
- Posts live in a table called wp_posts and each has an ID.
- Comments reside in a table called wp_comments, each referring to an ID in wp_posts.
- However, to make queries faster, the comment count is also cached in the wp_posts table, rather than getting calculated on every page load.
If this count ever gets out of sync with the actual number of comments for some reason, WordPress, while still displaying all comments properly, will simply show the wrong count.
How To Find Out Which Posts Are Out Of Sync
Fire up a MySQL shell or your favorite MySQL software (mine is Navicat) and run this query.
It assumes your database is called wordpress and the prefix is wp_, so adjust those accordingly.
1 2 3 4 5 6 7 8 | SELECT wpp.id, wpp.post_title, wpp.comment_count, wpc.cnt
FROM wordpress.wp_posts wpp
LEFT JOIN
(SELECT comment_post_id AS c_post_id, count(*) AS cnt FROM wordpress.wp_comments
WHERE comment_approved = 1 GROUP BY comment_post_id) wpc
ON wpp.id=wpc.c_post_id
WHERE wpp.post_type IN ('post', 'page')
AND (wpp.comment_count!=wpc.cnt OR (wpp.comment_count != 0 AND wpc.cnt IS NULL)); |
The result of this query is a list of posts whose comment_counts differ from the actual number of comments associated with each of them.
The left count is the cached number, while the right one is the right one.
How To Fix The Counts Automatically
The following query will recalculate and fix the comment counts for all posts that are out of sync (ones we just queried for above):
1 2 3 4 5 6 7 8 | UPDATE wordpress.wp_posts wpp
LEFT JOIN
(SELECT comment_post_id AS c_post_id, count(*) AS cnt FROM wordpress.wp_comments
WHERE comment_approved = 1 GROUP BY comment_post_id) wpc
ON wpp.id=wpc.c_post_id
SET wpp.comment_count=wpc.cnt
WHERE wpp.post_type IN ('post', 'page')
AND (wpp.comment_count!=wpc.cnt OR (wpp.comment_count != 0 AND wpc.cnt IS NULL)); |
I tested this approach on a few test cases but if you experience any problems, please do alert me in the comments and desribe your problem.
Happy WP hacking!
Artem Russakovskii is a San Francisco programmer, blogger, and future millionaire (that last part is in the works). Follow Artem on Twitter (@ArtemR) or subscribe to the RSS feed.
In the meantime, if you found this article useful, feel free to buy me a cup of coffee below.
beer planet is a blog about technology, programming, computers, and geek life. It is run by Artem Russakovskii - a local San Francisco geek who currently works at 
Artem loves commenting on other people's blogs and reading comments on his own, so go ahead and leave one.
I've been looking high and low for this advice. Thanks so much for taking the time to share your this WP comment solution.
This is going needed more with more plug-ins and themes using comments and now comment_meta and custom comment_types to extend WordPress as CMS functionality. Thanks for the tip.
I'm using DisQus too for my comment system. This post is definitely could help me a lot. Thanks
This worked perfectly! Many, many thanks for this.
#1142 – SELECT command denied to user 'joxpa'@'localhost' for table 'wp_posts'
UPDATE joxpawp2.wp_posts wpp LEFT JOIN (
SELECT comment_post_id AS c_post_id, count( * ) AS cnt
FROM wordpress.wp_comments
WHERE comment_approved =1
GROUP BY comment_post_id
)wpc ON wpp.id = wpc.c_post_id
SET wpp.comment_count = wpc.cnt WHERE wpp.post_type IN (
'post',
'page'
) AND (
wpp.comment_count != wpc.cnt OR (
Looks like your user doesn't have the right permissions to access the WP tables.
thanks for sharing/
I hired an employee in mid 2007 without consulting my best friend Stephen while he was on business to another country. Few months later I realized hiring that guy was a mistake as he had made lot of trouble for me and my best friend Stephen. We tried to convince him to leave the company so we could move on but he never took the message. We then created an atmosphere that he could feel that he does not fit in so for him to force out of the situation. That worked for him to leave my group, but not the company, so that employee stayed in another group. We still did not like him as he did not fit to our department. After few days later, we feed inaccurate information to the management and force him out of the department tangling him on false charges. Management was careful when they were kicking him out. I cannot believe that this strategy to get rid of the guy that I mistakenly hired was really worked.
Roberta J. Santos
wordpress can be areal headache sometimes, I use dto run it on a sudoku solver site online sudoku solver but stopped
http://www.shopmerry.com
Hi, Thanks a lot for this. It saved me quite some thinking and work while migrating from an old blog to WordPress.
Check out Wohill soon when the migration is done!
-> http://www.wohill.com
[...] How To Diagnose And Fix Incorrect Post Comment Counts In WordPress [...]