Counter for comments shows wrong number

[SoWhy]

Hi there,

don't know how to phrase this, so I just try it:
On the post-view the number shown in "Comments (X)" is not correct. Is there a way to update that number to recount the comments?
For example: this one shows 0 while there is one and this one shows 4 while there are seven.

I hope I was able to say what bothers me.

TIA

SoWhy

[judebert]

We've been having some trouble with comment counts. The weirdest one is when it goes negative, which results in a really big number.

Garvin knows what's going on, but he's traveling right now. You can either wait until he gets back, or try a forum search.

[garvinhicking]

Hi!

The counting bug should be fixed in the recent 1.0.4 release; you can use this SQL to fix the comment count:

Code: Select all

UPDATE serendipity_entries AS e SET e.comments = (SELECT count(c.entry_id) FROM serendipity_comments AS c WHERE c.entry_id = e.id AND c.status = 'approved') 

HTH,
Garvin

[SoWhy]

Tried that, my phpMyAdmin replies with:

Code: Select all

Error

SQL query: 

UPDATE s9y_entries AS e SET e.comments = ( SELECT count( c.entry_id )
FROM s9y_comments AS c
WHERE c.entry_id = e.id
AND c.status = 'approved' )

MySQL said: 
#1064 - You have an error in your SQL syntax near 'AS e SET e.comments = (SELECT count(c.entry_id) FROM s9y_comments AS c WHERE c.e' at line 1 

Most likely my old MySQL version (3.23.58) does not understand it (can't see why tho), I updated it manually where I saw it wrong. Thanks anyways

[garvinhicking]

Hi!

Ah, yes. MySQL 3.x does not allow Sub-Queries.

Best regards,
Garvin