Wrong comment count in WordPress

Should you for some reason end up with what appears to be wrong number of comments listed next to your posts (like me). The following little php script may come to your rescue. The reason why is normally after playing manually with the DB, major theme/plugin update failures or some hick-up during moving a site from one server to another.


Anyway, create a file (e.g. fix_comments.php) and paste the following code inside and save it to the root of your WP installation (where you also find wp-config.php).

<?php
include("wp-config.php");
if (!mysql_connect(DB_HOST, DB_USER, DB_PASSWORD)) {  die('Could not connect: ' . mysql_error());  }
if (!mysql_select_db(DB_NAME)) {  die('Could not connect: ' . mysql_error());  }
 
$result = mysql_query("SELECT term_taxonomy_id FROM ".$table_prefix."term_taxonomy");
while ($row = mysql_fetch_array($result)) {
  $term_taxonomy_id = $row['term_taxonomy_id'];
  echo "term_taxonomy_id: ".$term_taxonomy_id." count = ";
  $countresult = mysql_query("SELECT count(*) FROM ".$table_prefix."term_relationships WHERE term_taxonomy_id = '$term_taxonomy_id'");
  $countarray = mysql_fetch_array($countresult);
  $count = $countarray[0];
  echo $count."
"; mysql_query("UPDATE ".$table_prefix."term_taxonomy SET count = '$count' WHERE term_taxonomy_id = '$term_taxonomy_id'"); } $result = mysql_query("SELECT ID FROM ".$table_prefix."posts"); while ($row = mysql_fetch_array($result)) { $post_id = $row['ID']; echo "post_id: ".$post_id." count = "; $countresult = mysql_query("SELECT count(*) FROM ".$table_prefix."comments WHERE comment_post_ID = '$post_id' AND comment_approved = 1"); $countarray = mysql_fetch_array($countresult); $count = $countarray[0]; echo $count."
"; mysql_query("UPDATE ".$table_prefix."posts SET comment_count = '$count' WHERE ID = '$post_id'"); } ?>

Simply run it online (www.yoursite.com/fix_comments.php) and do remember to delete the file afterwards from the server!!

Leave a Reply