WordPress.org

Support

Support » How-To and Troubleshooting » [Resolved] MySQL Table For Comment Count Incorrect Although Comments Are There

[Resolved] MySQL Table For Comment Count Incorrect Although Comments Are There

  • I just upgraded to WordPress 2.5. Everything went ok, but my comment counts displayed all say zero even for posts with comments on them. The comments are all still there and can be seen; It is only the comment count that’s displayed beneath the post that is wrong.

    If a new comment is left on post, the comment count is updated correctly. It just shows zero until that happens.

    In browsing my WP database, I noticed the wp_posts table has a comment_count field. Currently it has the value of 0 for all the posts (except those which have received comments after the WP 2.5 upgrade).

    I’m wondering if there are any MySQL gurus out there who would know if the comments count can be corrected through a SQL query of something.

    If so, what would the command be and how would I enter it? I access my WP database using phpMyAdmin 2.9.1.1. Forgive me for being such a newb here, but I don’t know much about SQL.

    Thanks in advance for your help. It’s really appreciated.

Viewing 15 replies - 1 through 15 (of 28 total)
  • 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: 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. 🙂

    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: http://wordpress.org/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 !

Viewing 15 replies - 1 through 15 (of 28 total)
  • The topic ‘[Resolved] MySQL Table For Comment Count Incorrect Although Comments Are There’ is closed to new replies.