CountComments field in GDN_Category not accurate?

Aolee

hi anyone out there got an sql update script to update CountComments field in GDN_Category?

my problem is when you check this catergory page

http://pinoyau.info/categories/family-member-migration

you'll see 2 discussions and all in all 4 comments, but when you check

http://pinoyau.info/categories/all

the comment count for Family Member Migration is 0?

Aolee

Found a fix,

just incase anyone ran into the same problem...

1. backup first your GDN_Category table, so you can revert anything bad happens.
2. run this one shot SQL in your PHPmyadmin.


create temporary table my_temp_table
as
SELECT iF(sum( b.CountComments ) is null,0,sum( b.CountComments )) as counter , a.CategoryID
FROM gdn_category a
LEFT JOIN GDN_Category b ON a.CategoryID = b.CategoryID
GROUP BY CategoryID;

UPDATE GDN_Category a, my_temp_table s SET a.CountComments = s.counter WHERE a.CategoryID = s.CategoryID;

Aolee

Found a fix,

just incase anyone ran into the same problem...

1. backup first your GDN_Category table, so you can revert anything bad happens.
2. run this one shot SQL in your PHPmyadmin.


create temporary table my_temp_table
as
SELECT iF(sum( b.CountComments ) is null,0,sum( b.CountComments )) as counter , a.CategoryID
FROM gdn_category a
LEFT JOIN GDN_Category b ON a.CategoryID = b.CategoryID
GROUP BY CategoryID;

UPDATE GDN_Category a, my_temp_table s SET a.CountComments = s.counter WHERE a.CategoryID = s.CategoryID;