Is it possible to count the comments in a category?

tacoshop

I'm trying to find a way to count all comments in a certain category.. Can't see how to do it from sql. Anyone found out how to do this?

jimw

Look at this discussion.

edit: This link works now.

WallPhone

SELECT sum(`CountComments`) FROM `LUM_Discussion` WHERE `CategoryID` = 5

Wanderer

jimw, you left an = out of the URL above.

tacoshop

thats only to count the discussions though?

WallPhone

If you did count(*) it would count discussions. CountComments (should) contain the number of comments in the discussion, so we add them to get a total.

tacoshop

Thanks, that worked! Another question for mysql though.. What I'm doing is trying to turn the categories page into like what most traditional forums are like, with how many posts, topics, etc.. now I am trying to put the most recent post for that category, how do i do that with Sql? theres no categoryID on comments ufortunately. I was hoping this would work but it only brings back discussions. <code> SELECT * FROM LUM_Discussion, LUM_Comment IF CategoryID = 3 ORDER BY CommentID DESC LIMIT 1 </code> Thanks for your help. Its greaty appreciated. :D I haven't messed with PHP in a long time..

WallPhone

You have to join that with the comments table first:
SELECT * FROM `LUM_Comment` LEFT JOIN `LUM_Discussion` USING `DiscussionID` WHERE CategoryID =6 ORDER BY CommentID DESC LIMIT 0 , 1
For some reason, this code isin't working, and I am too tired to figure out why right now.

tacoshop

Thanks wallphone,

I ended up getting it to work using this if anyone is curious

SELECT * FROM `LUM_Comment` LEFT JOIN `LUM_Discussion` USING ( `DiscussionID` ) WHERE LUM_Discussion.CategoryID =3 ORDER BY LUM_Comment.CommentID DESC LIMIT 0 , 1

addicted

Select count(lum_Comment.commentid) from lum_Comment, lum_Discussion where lum_Comment.discussionID = lum_Discussion.discussionID and lum_Discussion.categoryID = 11 and where lum_Comment.deleted = "0";
This one is better! It takes about .005 seconds without server lag (estimate I am logged in remotely, my actual time was .06 which is way to long)