26

How To Diagnose And Fix Incorrect Post Comment Counts In WordPress


Posted by Artem Russakovskii on March 21st, 2010 in Wordpress

Updated: September 16th, 2012

image

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

Please make a backup of your database before performing any altering queries such as the one below (I recommend mysqldump or the WP-DBManager plugin).

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.



Share
  • ekule44

    I've been looking high and low for this advice. Thanks so much for taking the time to share your this WP comment solution.

  • R'phael Spindel

    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.

  • Erline

    I'm using DisQus too for my comment system. This post is definitely could help me a lot. Thanks

  • http://greywulf.net greywulf

    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 (

    • http://beerpla.net Artem Russakovskii

      Looks like your user doesn't have the right permissions to access the WP tables.

  • john100

    Thanks for sharing this post,
    ccna

  • Regina Smola

    Worked like a charm! Thanks for posting this solution.

  • Navid Azimi

    Thanks for this. This worked exactly as expected. For people getting the access denied error, try removing the "wordpress." prefix for the table names.

  • Josh Fowler

    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

    • http://beerpla.net Artem Russakovskii

      Nice one, Josh.

  • Katrina Kaif

    Worked perfectly! Thanks for posting this.

  • Supratall

    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.

  • nate

    I just switched to DISQUS…hope it doesn't have any adverse effects…thanks for the tips on this..I had no idea!

  • Beth

    THANK YOU SO MUCH!!! I really needed this fix – Intense Debate and WordPress together have been a disaster. Your SQL queries worked perfectly!

  • Sonic

    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

  • mylene

    this fixed my problem, SQL problems are really a headache if you dont know how to fix

  • loloudoudara

    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)

  • boybati

    works like a charm. thanks a lot.

  • Robin Majumdar

    Worked like a charm on WordPress 3.2.1 with no plugins (other than Akismet) and the default 2011 theme – thanks for sharing.

  • How

    You need a writer or scholar. You write very well. I liked your article too. Thank you.

  • Hannes

    worked just great, thanks!

  • pradeepf

    thanks just worked perfectly…

  • Pingback: Cet objet

  • Alexboss

    Works like a charm, even on an old WordPress (2.9.1). Thanks for this. Alex

  • Dariusz

    Thanks for soluccion. It Works perfectly.