thomask
Member
Posted 3 years ago #
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
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)
thomask
Member
Posted 3 years ago #
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)
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);
trojanloy
Member
Posted 2 years ago #
I had the same problem, and did the SQL query listed on this page...
http://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.
jaidev