There was a user in the #wordpress IRC channel who was having some problems with his database’s comment_count field being incorrectly updated when deleting comments.
Since there were several of us who weren’t sure if their comment_counts had been properly updated when the field was added, I whipped up a series of SQL queries. There was a good bit of playing around, but the final version is:
SELECT id, comment_count
FROM wp_posts p
WHERE p.comment_count >0
AND (
id, comment_count
) NOT
IN (
SELECT comment_post_id, count( * )
FROM wp_comments c
WHERE comment_approved = '1'
GROUP BY comment_post_id
)
Of course update wp_ with whatever your database prefix is, assuming you didn’t use the default.
Updating these values should be about as simple, although I have not tested this:
update wp_posts p
set p.comment_count = (
select count(*) from wp_comments where comment_post_id = p.id and comment_approved = '1'
)
This is interesting but does anyone know why the comments aren’t being updated correctly? I haven’t done any theming so I don’t know but is this up to the theme to make sure it happens?
Luke: No, it shouldn’t have anything to do with the theme, all the Wordpress internals should properly take care of it.
I haven’t looked into it enough to know exactly why it’s happening. The conversation that got it started was a person deleting comments (probably SPAM). I’m not sure how he was deleting them, or if there’s a problem there getting things updated. Obviously deleting them right out of the database wouldn’t work, but deleting them through the admin panel should work fine.
A couple others of us in #wordpress thought there may have been a problem experienced during upgrade that didn’t properly calculate the new values when the field got added, but I checked my main blog and wasn’t able to find any incorrect values.
If anyone can come up with more concrete theories as to why it doesn’t work, it may help.
Chris,
The original user on #wordpress (I can’t remember who it was) was deleting comments straight out of the database using phpMySql, that’s why their `comment_count` wasn’t being updated.
Like you said though, it seems that `comment_count` isn’t being populated with the comment count on some people’s blogs (mine included) in some situations.
Thanks for this fix, somehow I had ended up with a few posts with negative comment counts. I’m not sure, but I think it had to do with either the Akismet plugin or WP-HashCash.
Thanks. The second query saved my day. Because of the massive amount of spam I had (before the counter measures) I deleted them from PHPMyAdmin. Now the count is correct again.
Oh, and remove the “p.comment_count>0 AND” part from the first query: I was having comment_counts of zero, still saying there was 1 comment. With your query these don’t show up.