mySQL count question

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