Google PlusFacebookTwitter

Reseting Post and Comment Count in WordPress

By on Nov 16, 2008 in Tech Takes | 36 comments

Share On GoogleShare On FacebookShare On Twitter

I had faced a problem a few weeks back when I had tried to upgrade my WordPress install from 2.6.1 to 2.6.2. I was using the WP Automatic Upgrade plugin to upgrade my install, and it failed. Screwed up my database. Restoring from an SQL backup didn’t work either, strangely. Spent a few agonizing hours on the WordPress IRC channel and trying the stuff the helpful people there were suggesting but in the end it boiled down to this – short of doing a clean install there seemed no way out.

Thankfully I had also exported a WordPress Extended RSS (WXR) backup file (along with the SQL backup) before starting the upgrade process, so I was able to manually do a clean install of WordPress and then use WXR XML file to restore my blog. Before removing the old install I had already kept a copy of the wp-content directory on my host, so that when the XML import process was done recreating the file structure I could simply replace that with the copy I had moved to another folder. I learned a valuable lesson too – using the WP-AU might save you some time, but the consequences if something goes wrong are far-reaching and potentially ‘devastating’. My advice to fellow WP users is to use the manual update procedure no matter what. I shudder to think if the auto-upgrade functionality the developers intend to include in WordPress 2.7 fails, then a lot more users will be affected. (Since the feature will be available in the core install from now on, possibly a much greater number of users will be using it and will be at risk of something going wrong.)

Anyway, after doing the import I noticed that a few things were amiss. The first thing was that the category count for all posts was showing up as zero (and neither did the post management page show the categories under which a post was filed). This can be particularly irritating if you use a template (like mine) which displays the number of posts in a category in the sidebar. The solution for this is simple: create a post and file it under every category you have and publish it. This ‘forces’ WordPress to do a recount of the number of posts in for each category and the count is updated. You can then delete that post. Maybe ‘forces’ would be a strong word; WordPress counts the number of posts under a category any time you make a post.

Another note regarding post categories after importing from an WXR file it that if you were using a custom name for your ‘Uncategorized’ category, then you need to delete the custom category name (which will show up separately) and then rename the ‘Uncategorized’ category to whatever custom name you want.

The biggest problem I faced after the import was the fact that the comment count for each post was showing up as zero. Now you could do this the ‘hard way’ – similar to the post category update, you could go and make a new comment on each and every post you have (which would trigger WordPress to do a recount), and then delete those comments. Admittedly that would be stupid and time-consuming especially if you have a blog like mine which has hundreds of posts. So for this, here’s what you need to do:

  1. Save the script below as a *.php file using a text editor. Name it something like filename.php or whatever you feel like; just keep in mind NOT to give it a filename same as already-existing WordPress files on your host. Change the ‘wp_’ prefix to whatever your installation has, in case you changed it.
  2. Upload the PHP file to the root folder of your WordPress install.
  3. Navigate to the file at yourblogname.com/filename.php using your web browser (using your own blog and file name).
  4. Remember to remove the file after you’re done.

So use the comment count update script below to automate the comment count update process. Before running the script, it would be a good idea to create a backup of your existing database, just case something goes wrong. If the comment doesn’t seem to work then your text editor is probably breaking something. Remove all newline characters manually.

<?php

/* This program is free software: you can redistribute it and / or modify it under the terms of the GNU General Public License as published by the Free Software Foundation version 3 of the License. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. For a copy of the GNU General Public License see http://www.gnu.org/licenses/gpl.html */

/* Script to recount number of comments in WordPress install. This script has been tested with WordPress 2.6.x. Note that script may fail to run the update for all posts in case the script runs longer than the timeout period specified in your settings. In case that happens, try increasing the timeout limit by editing the php.ini file on your webhost. If you are unable to modify the php.ini file and / or don't have access to it then just say a quick prayer and hit the 'Reload' button on your web browser. */

include('wp-config.php'); // Needed for login details to WordPress database to make necessary changes

function updateCount()
    {
        $posts = mysql_fetch_row(mysql_query("SELECT ID FROM wp_posts ORDER BY ID DESC LIMIT 1")); // Fetch row in WordPress database containing information about post data
        for ($i = 1; $i < ($posts[0] + 1); $i++)
        {

     $comments = mysql_query("SELECT SQL_CALC_FOUND_ROWS comment_ID FROM wp_comments WHERE comment_post_ID = '$i' AND comment_approved = 1;") or die("Failed to calculate number of approved comments"); // Calculate the number of approved comments for a post and store in a variable. If unsuccessful, end program.

     mysql_query("UPDATE wp_posts SET comment_count = '".mysql_num_rows($comments)."' WHERE id = '$i';") or die("Failed to update the number of comments calculated"); // Update the comment count using the comment number fetched earlier. If unsuccessful, end program

     echo "Updated Post #$i - ".mysql_num_rows($comments)." comments <br />"; // Display message to user for each post comment count successfully updated
        }
    }

updateCount();
?>


36 Comments

  1. Ankur

    November 18, 2008

    Post a Reply

    @Karmanya: If your host doesn’t support PHP, then how are you going to run WordPress in the first place?

  2. Tomas

    January 16, 2009

    Post a Reply

    Wow, you really saved our day with that script!!
    Imported a wordpress.com blog into our wpmu install (took hours and hours) and after it had the exact same issues as you describe. Your post solved it all.

    Many thanks
    Tomas

  3. Matt

    February 10, 2009

    Post a Reply

    OK, this is all kinds of amazing. I had imported my old Livejournal (over 2,400 posts) into WordPress, and all the comments came along, but the comment counts were messed up.

    This script saved the day. You are a wonderful human being.

    • Ankur

      February 10, 2009

      Post a Reply

      LiveJournal user…so you’re migrating to WordPress because of the recent administrative problems at LiveJournal?

      • Matt

        February 10, 2009

        That was kind of the straw that broke the camel’s back, actually. I had been toying with moving off of LJ for a while, since I wanted more flexibility that I’d get with WP, and when the rumors started flying, it was the perfect catalyst.

      • Ankur

        February 11, 2009

        Glad that I could help in making a shift to the goodness of WordPress. :)

  4. Steven

    July 8, 2009

    Post a Reply

    Hi I tried using your script but I am still having the same problem I am still having my comments count = 0

    can you help please

      • steven

        July 8, 2009

        Hi the code did work, I got a page which listed all the comments but it still came out as 0 comments dont know why

        do you have any idea?

      • Ankur

        July 8, 2009

        Hmm, that’s odd. Sorry, dunno. Can someone else please check if you’re facing similar problems?

  5. Zulfikar

    November 20, 2009

    Post a Reply

    Awesome script Ankur :) Very much appreciated.

    Upgrade a very very old wordpress (1.2.2) through to 2.8.6 and all was ok but for the comment count, with hundreds of posts and comments the manual work would have been just silly and the hard way to do it as you said.

    Plugged in your script and boom all sorted.

    Thanks again :)

  6. Sam Critchley

    September 8, 2010

    Post a Reply

    I just migrated an old server and am running an older version of WordPress. I deleted all the spam comments from the database and then noticed the comment count was off. Your script fixed the problem, but initially it set the comment count to 0 for every post as it required ‘AND comment_approved = 1′ in one of the MySQL queries. Once I’d removed the requirement it recounted perfectly. Thanks!

    • Ankur Banerjee

      September 9, 2010

      Post a Reply

      Thanks for pointing that out. Maybe it’s an issue with the latest WordPress version that the comment approval status is needed.

  7. Brian Yang

    October 3, 2010

    Post a Reply

    Thanks so much for the script!

    My comment count problem was caused when I disabled Disqus. I found a duplicate copy of every single comment I had and since I had hundreds, deleting them manually was tedious.

    So I ran a query to mass delete an ID range from phpMyAdmin. Unfortunately, while the comments are gone, the count was incorrect.

    Script works perfectly. But I suggest adding somewhere telling users to change the wp_ part to whatever their table prefix is.

    Thanks again,
    Brian

    • Ankur Banerjee

      October 3, 2010

      Post a Reply

      Thanks for the suggestion Brian. I added a note about changing the ‘wp_’ prefix in case it has been customized. Glad that you find the script useful!

  8. Mike Salway

    February 24, 2011

    Post a Reply

    This was very helpful, and worked perfectly – thanks!

    Was surprised (but relieved) it still worked even on WP 3.1!

    Cheers

  9. Lorenzo

    January 21, 2013

    Post a Reply

    I’m getting a PHP Warning: mysql_fetch_row() expects parameter 1 to be resource, boolean given

  10. Brodie Butler

    February 23, 2013

    Post a Reply

    A much quicker way If you’re savvy with phpMyAdmin, just run this query in the SQL tab.

    BACKUP YOUR TABLE FIRST!

    update wp_posts
    SET comment_count =
    (
    Select count(c.comment_ID) from wp_comments c
    where c.comment_post_id = id
    )

  11. Sam

    August 7, 2014

    Post a Reply

    Thanks for this, worked like a charm!

    For people getting the “PHP Warning: mysql_fetch_row() expects parameter 1 to be resource, boolean given” error, this is probably because your “wp_” prefix is wrong. I had a typo in mine. Check it thrice.

Trackbacks/Pingbacks

  1. frEdSCAPEs 3.0 » Blog Archive » Repareer de reactietellers - [...] ~ Reseting Post and Comment Count in WordPress [...]
  2. The Eagle & Child: "A blogging tour de force!..." - Marc Vandersluys, The Eagle & Child - [...] searched a bit more and found a site which gave an incredibly easy solution.  The problem is that Wordpress …
  3. nbells.com/vt — Comment systems: Disqus, IntenseDebate, JS-Kit - [...] Update (2009.12.10): After trying the IntenseDebate plugin 2.5 for a while, i decided to disable it since all WordPress …
  4. Wordpress Export and Import « Anders - [...] are 0, so every blog says “no comments” even though that is not the case. I found a page …
  5. YazminMedia – Fixing Post Comment Counts in Wordpress - [...] like how the SQL was formatted for making the updates. Another quick search and I found this post: Reseting …
  6. Installation de WordPress 3.0 Multisite avec option sous-domaines : tutoriels, conseils, problèmes et solutions « Bloody Marie - [...] résoudre ce problème, Ankur Banerjee a mis au point un petit script hyper pratique qui remet tout en ordre. …
  7. Wrong comment count after XML import | James Fishwick - [...] of is! A better solution is of course, to update the database with the correct number of comments. This …
  8. How I received 16000+ SPAM Comments within a month on my Wordpress Blog | BOTS World - [...] face one more issue. The displayed comments count on each of your post will not change. You need to …
  9. Problemas al exportar o importar Wordpress - [...] o exportación. Yo no lo hice, entonces para arreglarlo usé esta solución que aparece en este enlace. Como está …

Submit a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>