MySQL Table For Comment Count Incorrect Although Comments Are There

<?php
include('wp-config.php');

$posts = mysql_fetch_row(mysql_query("SELECT ID FROM wp_posts ORDER BY ID DESC LIMIT 1"));

function update_comment_count(){
	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;");
		mysql_query("UPDATE wp_posts SET comment_count = '".mysql_num_rows($comments)."' WHERE id = '$i';");
	}
	return true;
}

if(update_comment_count()){
	echo "successful";
}
?>

Just save it in a .php file and upload to the directory where wp-config.php is (the root directory), and run the file in the browser.

(Pastebin: http://pastebin.com/m57332b6d )

It should work (sort of tested), but you might want to backup your database first – just in case something goes wrong. No thanks for the beer, don’t want to get arrested for underage drinking. 🙂

Try this.

<?php
include('wp-config.php');

function update_comment_count(){
	$posts = mysql_fetch_row(mysql_query("SELECT ID FROM wp_posts ORDER BY ID DESC LIMIT 1"));
	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("failed1");
		mysql_query("UPDATE wp_posts SET comment_count = '".mysql_num_rows($comments)."' WHERE id = '$i';") or die("failed2");
		echo "Updated Post #$i - ".mysql_num_rows($comments)." comments";
	}
}

update_comment_count();
?>

The above code gives you status on which posts have been updated and how many comments are in the posts.

Cool one, as also had this problem … gonna try it out soon.

does this fix work for 2.6?? Anyone knows?

*Appears* to have worked for me under 2.6. See more zero results than I’d expect, but maybe close enough.

THAT is BRILLIANT!