WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] MySQL Table For Comment Count Incorrect Although Comments Are There (29 posts)

  1. scottorama
    Member
    Posted 6 years ago #

    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.

  2. scottorama
    Member
    Posted 6 years ago #

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

  3. haochi
    Member
    Posted 6 years ago #

    <?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. :)

  4. scottorama
    Member
    Posted 6 years ago #

    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?

  5. haochi
    Member
    Posted 6 years ago #

    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.

  6. scottorama
    Member
    Posted 6 years ago #

    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.

  7. scottorama
    Member
    Posted 6 years ago #

    P.S. Care to tackle this one: http://wordpress.org/support/topic/165089 ?

  8. gdebruijn
    Member
    Posted 5 years ago #

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

  9. EazyVG
    Member
    Posted 5 years ago #

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

  10. letter9
    Member
    Posted 5 years ago #

    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?

  11. mambo78
    Member
    Posted 5 years ago #

    does this fix work for 2.6?? Anyone knows?

  12. paburmester
    Member
    Posted 5 years ago #

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

  13. davezatz
    Member
    Posted 5 years ago #

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

  14. Nicki Faulk
    Member
    Posted 5 years ago #

    Followed the instructions but did not correct the problem. :(

  15. katitude
    Member
    Posted 5 years ago #

    THAT is BRILLIANT!

  16. voyance
    Member
    Posted 5 years ago #

    Yes haochi, thank you also for your work !

  17. voyance
    Member
    Posted 5 years ago #

    Yes haochi, thank you also for your work !
    I speack on my site horoscope

  18. joe28
    Member
    Posted 5 years ago #

    Thanks for the code. I am having some problem with my blog, you save my day!

  19. veka
    Member
    Posted 5 years ago #

    many thanks. its really worked even for kbe on WP 2.6.1

  20. steve49589
    Member
    Posted 5 years ago #

    I tried this earlier for 2.7, but it did not seem to work. I thought it did, but within the hour, the numbers were no longer correct.

    I just ran this again in 2.7 and it work. At least I think it worked. We'll see what happens.

    Does anyone know the cause? My guess is the script goes into the database and reads the comment_count number and checks to ensure that it is correct, looking for trackbacks and comments directly related to that post number. Is that correct?

    Thanks.

  21. johnwar
    Member
    Posted 5 years ago #

    I got the same problem when I exported mySql from my old host to my new one. The thing I do is export it again, it work.

  22. steve49589
    Member
    Posted 5 years ago #

    Export it again? To me, that does not make sense since if I look at the raw data in the database, it provides the incorrect number.

    I guess I could try it, but still wondering if there is some sort of issue elsewhere.

  23. steve49589
    Member
    Posted 5 years ago #

    For others that are having this issue, I've got another thread going here - http://wordpress.org/support/topic/233605?replies=3#post-964180 - because this thread is marked as "Resolved" in the title.

    Can you chime in over there?

  24. johnwar
    Member
    Posted 5 years ago #

    Do you mean the exported one or the original in the old server. If you talked about the database that you've exported, you should try to export from you old server again. Because I found that some time the export file are incorrect/missing.

  25. steve49589
    Member
    Posted 5 years ago #

    Unfortunately, that is not an option for me since I've merged the posts from two blogs, and since the merge, I've had at least 50 new posts and maybe 100+ comments.

    Why would this problem effect new posts? I post something new, and immediately the first comment added bumps the number up to 4 or 5 shown.

  26. cherooo
    Member
    Posted 5 years ago #

    Articles of interest really good job.
    Best regards

  27. voyance
    Member
    Posted 5 years ago #

    Me too, this is not an option for me, after the merger of my two blogs 300 posts and 500 new comments! My blog is Astrologie

  28. tolo87
    Member
    Posted 5 years ago #

    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). Thanks Mike pit bikes

  29. ver0ndel
    Member
    Posted 5 years ago #

    very nice article,keep the good work

Topic Closed

This topic has been closed to new replies.

About this Topic