How To Diagnose And Fix Incorrect Post Comment Counts In WordPress
| Share |
Updated: August 8th, 2010
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 is currently pursuing his own projects and regularly enjoys hacking Android, PHP, CSS, Javascript, AJAX, Perl, and regular expressions, working on Wordpress plugins and tools, tweaking MySQL queries and server settings, administering Linux machines, blogging, learning new things, and other geeky stuff.
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 this post,
ccna
Worked like a charm! Thanks for posting this solution.
Thanks for this. This worked exactly as expected. For people getting the access denied error, try removing the "wordpress." prefix for the table names.
This happens a lot to me so I made a wordpress plugin for it that checks it and fixes it on a timer.
You can see it at: http://josh-fowler.com/?page_id=124
Nice one, Josh.
Worked perfectly! Thanks for posting this.
I tought about implementing a feature to send a maximum number of mails per day and/or to send a mail after a custom number of comments have been selected. Additionally I would implement a admin comment feed with all the neccessary liks to approve, edit, delete or mark as spam.
I just switched to DISQUS…hope it doesn't have any adverse effects…thanks for the tips on this..I had no idea!
THANK YOU SO MUCH!!! I really needed this fix – Intense Debate and WordPress together have been a disaster. Your SQL queries worked perfectly!
I use wordpress and disqus plugin,
when user add comment, after few minute, disqus agent will update wordpress database (wp_posts,wp_comments …etc),but when administrator delete comment, disqus will not update wp_posts's comment_count and wp_comments,
I can't get accuracy comment_count and comment counts got messed up, how could I get accuracy comment_count of every post ?
Thanks.
Sonic
this fixed my problem, SQL problems are really a headache if you dont know how to fix
Very helpful post.
Removing the wordpress prefix from the table names will help some. Also removing the where close in the second join might help (WHERE comment_approved =1)
works like a charm. thanks a lot.
Worked like a charm on WordPress 3.2.1 with no plugins (other than Akismet) and the default 2011 theme – thanks for sharing.
You need a writer or scholar. You write very well. I liked your article too. Thank you.
worked just great, thanks!