how to update comment count and category count (SQL geek wanted)

  • Hi, I did upgrade to wordpress but i have zeros at comment count and category count, so wordpress do not show me Comments (n) and category list on index. How can I update the counts.

    I tried e.g.
    UPDATE marigold_posts SET comment_count = (SELECT COUNT(comment_post_id) FROM marigold_comments WHERE marigold_posts.id = marigold_comments.comment_post_id)”;

    it is working for an hour but nothing happen, seems i messed something. Please can you help me to repair the SQL?
    Or is there any script which would rebuild those 2 counts?
    thanks

Viewing 6 replies - 1 through 6 (of 6 total)
  • Moderator Samuel Wood (Otto)

    (@otto42)

    www.remarpro.com Admin

    The comment_count for any post will get updated when a comment is made/updated/deleted/approved/etc.. It’s not something you have to maintain.

    The count is held in the object cache though, so if you don’t see it change, delete the contents of the cache directories in wp-content.

    @thomask – what version of WP did you upgrade from?
    Are all your plugins current? (in particular, are you running Spam Karma 2 and is it updated)

    Thread Starter thomask

    (@thomask)

    re otto: i have to, as i have zeros everywhere and it doesnt show the comment count with the articles even if there are some. I could do it article by article, but i have more then 2000 articles ??

    re handysolo: no, i have ‘upgraded’ from a different CMS using my own SQL ‘magic’ ?? (i will post the how to to wordpress/upgrade section then), iam just a bit tired and cannot catch an idea, how to make fast SQL update for comments count (2000 articles, >10 000 comments, so it need to be something realy nice)

    Moderator Samuel Wood (Otto)

    (@otto42)

    www.remarpro.com Admin

    Ah. Well, if you want to do it, that’s a pretty slow way of going about it, especially on a database of any size. I’d use a temporary table, myself.

    CREATE TEMPORARY TABLE commcnt
    SELECT comment_post_ID, count(*) AS a
    FROM marigold_comments
    WHERE comment_approved = ‘1’
    GROUP BY comment_post_id
    ORDER BY comment_post_id;

    UPDATE marigold_posts
    SET comment_count =
    (SELECT a FROM commcnt
    WHERE comment_post_ID = marigold_posts.id);

    If you haven’t resolved it yet, use https://jaidev.info/downloads/wp_comment_count.pl

    I had the same problem, and did the SQL query listed on this page…

    https://www.brendanloy.com/2007/02/wordpress-21-upgrade-problems.html

    …and it worked fine, and almost instantaneously. My database has 129 categories, 14,461 posts and 107,302 comments. So I’m thinking that if the query is entered properly, speed shouldn’t be an issue.

    Also visit that same link for a solution to the categories problem.

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘how to update comment count and category count (SQL geek wanted)’ is closed to new replies.

Tags