[El Forum]

[eluser]cinoob[/eluser]
I am creating a forum and would like to display the number of posts within each category. The database structure is as follows:

A category table holds the name of categories
A topics table holds topics for each category
A messages table holds responses to each topic

Can anyone help me with a query which would return all the columns in the categories table and a total count for the number of topics and responses to topics?

[El Forum]

[eluser]Ben Edmunds[/eluser]
Most people here won't do the work for you but we don't mind helping you along so try it, post what you have, and then we'll help you figure it out and learn it instead of just spoon feeding it to you.

[El Forum]

[eluser]jedd[/eluser]
[quote author="cinoob" date="1262221216"]
I am creating a forum and would like to display the number of posts within each category. The database structure is as follows:

A category table holds the name of categories
A topics table holds topics for each category
A messages table holds responses to each topic
[/quote]

There are lots of ways of designing forums, so you might want to post your schema to make it a bit clearer on what you're doing.

Are replies substantially different from the first message in a given thread. (I'm guessing what you call a topic is what everyone else calls a thread - ?)

Do messages contain their topic and category identifier, or just their topic identifier?

Post code (as Ben suggested) showing how you already extract a thread's content. It's unclear if you are having trouble working out basic SQL queries where you join tables together, or if you are having trouble using COUNT(id) , for example.

[El Forum]

[eluser]cinoob[/eluser]
Hi Jedd,

I've got the categories from the database using:

Code:

$query = $this->db->query('SELECT * FROM forumcategories');

Each topic (or thread) has a foreign key in it linking to the category table and each message (or reply) has a foreign key in it linking to the topics table. I can see how I could count the topics with each category using something like this:

Code:

$this->db->where('category_id', $categoryid);
$this->db->from('forumtopics');
echo $this->db->count_all_results();

This would be fine for finding out the count for one category but I would like to find out how to count all the categories topics.

I'm also unsure how I can then pass this information to the view. Currently I am able to loop through the category names from the database but I think this count would have to be part of the same SQL statement to be able to pass it to the view at the same time. Unless I am able to add a second set of SQL results to to the first array.

[El Forum]

[eluser]cinoob[/eluser]
I think I have managed to get quite close now. I have got the count for all topics messages but this SQL statement does not include the topics that do not yet have any messages:

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');

Any ideas how I can amend this statement to retrieve all topics that have a count of 0 messages?

[El Forum]

[eluser]jedd[/eluser]
Well, by the sounds of it you just need to left join to the topic and then left join again to the category.

It's not clear what the distinction is between replies versus messages (within a thread). In my forum code I don't let a thread exist without a message, and if the last (in the sense of only, not most recent) message in a thread is deleted, then the thread is deleted at the same time.

FWIW message counts are the one place I've de-normalised my DB - and I'm still in two minds about whether I should keep it that way. It makes me feel a bit dirty.

How to pass the information to the view depends very much on what your view will be like. (Hint hint.)

[El Forum]

[eluser]Chad Fulton[/eluser]
I think in cases like this, with high reads and comparatively fewer writes, people often make a table which just aggregates this data.

That may be what Jedd meant when he said it's the one place he de-normalized his DB.

[El Forum]

[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.