mySQL count question

[eluser]jedd[/eluser]
Correct - I thought I had, but I don't have it in my git repository, so maybe I just dreamt it.

Actually I think I ended up not caring about the number of messages in a whole forum, and considered message-counts-per-thread to be cheap enough to do on the fly.

In any case, yes, you can keep a message count in your forum table. This gets messy, of course, as any de-normalising does - consider extra code you have to write for thread deletes, thread moves, message deletes and message moves.

Btw, I forgot to mention, but your code:

Code:

$query = $this->db->query('SELECT forumtopics.id AS topicid, forumtopics.*,
COUNT(forummessages.topic_id) AS total_replies FROM forummessages, forumtopics
WHERE forummessages.topic_id = forumtopics.id  AND forumtopics.category_id =
'.$this->uri->segment(3).' GROUP BY forummessages.topic_id');

- is unsafe. Escape or sanity-check (an is_numeric() would be sufficient) the uri->segment(3) before feeding it into a SQL query, otherwise you're wide open to injection attacks.