Reseting Post and Comment Count in WordPress

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