Vanilla 1 is no longer supported or maintained. If you need a copy, you can get it here.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

Is it possible to count the comments in a category?

edited October 2006 in Vanilla 1.0 Help
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?


  • edited October 2006
    Look at this discussion.

    edit: This link works now.
  • edited October 2006
    SELECT sum(`CountComments`) FROM `LUM_Discussion` WHERE `CategoryID` = 5
  • jimw, you left an = out of the URL above.
  • thats only to count the discussions though?
  • 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.
  • 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..
  • 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.
  • edited October 2006
    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
  • edited October 2006
    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)
This discussion has been closed.