3

How To Fix Incorrect Comment Counts In WordPress Post

WordPress has a smart way of making sure the comment count that is displayed on your site loads quick without actually having to query the comment table. Which means faster load times. Here is how they do it:

  • Posts live in a happy little table called wp_posts and each has an ID number.
  • Comments are in a table called wp_comments and referring to an ID in wp_posts.
  • But, to make queries faster, the total comment count is also kept in the wp_posts table, rather than adding them all up on every page load.

This is all great to make load times faster but in a word of anyone can make plugins for WordPress that count can easily get out of sync. Let’s say you have a different comment system other then WordPress’s built in one, something like a Facebook integration or even DISQUS, and on some update they mess up a piece of code that adds to the total comment counter for a post after a comment is made. Your comment counts are now out of sync and your post page will now report the wrong comment count number.

Well, here is your solution…in fact, here are two solutions to the problem. One in the form of a plugin and one in the form of a MySQL query.

Solution 1

I have written a plugin called Web Ninja Comment Count Fixer WordPress Plugin. After you install the plugin you just click a button and your comment counts are now fixed! Or if it happens to you a lot you can set it to automatically check it on a timer interval and never have to worry about it again…That’s the easy solution.

Solution 2

First, you will need to have access to a MySQL database shell that you can run queries from. Most hosting providers have this option through their control panel.

Now, let’s assume you are using the default table prefix of wp_ and run:

SELECT wpp.id, wpp.post_title, wpp.comment_count, wpc.cnt
FROM wp_posts wpp
LEFT JOIN
(SELECT comment_post_id AS c_post_id, count(*) AS cnt
FROM 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 results 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.

Now, we are going to run:

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

This will fix and recalculate all the comment counts that need to be fixed.

Conclusion

Solution 2 has worked on numerous cases for me so I know it works. I have written a couple custom Facebook Comment system integrations and while testing the counts always seem to mess up. After I run this, everything is back where it’s suppose to be.

It actually got to the point of me having to run the query so much I had to write the plugin in Solution 1. So I definitely know that works too.

Even though I wrote the plugin I can’t take full credit for the actual query. I got the query itself from Artem Russakovskii and his article can be found here.

If you have any questions just ask…

Comments