MySQL Table For Comment Count Incorrect Although Comments Are There

I’m really desperate for someone who know SQL and WP to help me with this. I’ll even buy you a beer!

<?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: https://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. ??

Thank you so much for the code. I did exactly what you said. I received a message that the code had executed “successful”, but the comment counts still are not displaying correctly.

Any other ideas?

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.

Oh bless your heart! That worked.

Thank you so very much for your help. I can’t even begin to tell you how much this problem was bothering me. If nothing else, it made me want to learn SQL in order to fix it.

I know I can’t buy you a beer seeing that you’re underage, but if you ever need a favor, just let me know.

P.S. Care to tackle this one: https://www.ads-software.com/support/topic/165089 ?

@haochi: thank you also from my side. It worked for me as well. Great!

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

I had the same problem and I ran the second version above, and it ran and “worked” (meaning it told me that it had updated each file), only it still said that each post had zero comments (even though this is not true).

Any ideas out there?

does this fix work for 2.6?? Anyone knows?

Yes, it works for WP 2.6
Thanks haochi !!!

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

Followed the instructions but did not correct the problem. ??

THAT is BRILLIANT!

Yes haochi, thank you also for your work !