Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I am doing a special migration from someone's custom CMS to a WP installation. I successfully got the posts and comments migrated into their respective tables. Now I need to get the comment count into the wp_posts table for each matching id. I have something like this. I'm close, I think:

SELECT COUNT(comment_content) AS total_comments, comment_post_ID AS commentID
FROM wp_comments
GROUP BY comment_post_ID;
--mysql won't let me run the group by on just the returned count column, so I'm stuck with    two columns of data when I just want to insert one

UPDATE wp_posts SET comment_count = total_comments WHERE wp_posts.ID = commentID;
--So I tried to set my returned data to variables and assign them into the update statement..which doesn't work

Anyway, perhaps I need to iterate all the posts, find a matching ID and update it by moving the cursor? Or is there an easier way i'm missing somehow?

share|improve this question
add comment

2 Answers

The easy way is to just write PHP to go through all the posts and call wp_update_comment_count() on each post ID.

share|improve this answer
    
But let's say I really wanted to figure this out with MySQL. Any idea how? –  Lynn Nov 30 '12 at 22:11
add comment
up vote 0 down vote accepted

And I finally figured it out. For those who want to know how something like this is handled with MySQL, here it is:

    UPDATE wp_posts, (SELECT COUNT(comment_content) AS total_comments, comment_post_ID
FROM wp_comments
GROUP BY comment_post_ID) x
       SET wp_posts.comment_count = x.total_comments
       WHERE wp_posts.ID = x.comment_post_ID;
share|improve this answer
1  
That doesn't account for deleted or spam comments. You need to only count comments with comment_approved = '1'. –  Otto Dec 9 '12 at 19:47
    
Good point. Though I was doing this from a custom built CMS. So the comments either existed or didn't. No spam or deleted status. –  Lynn Dec 11 '12 at 21:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.