Count comments to each recension?

Question

I need someone to help me with this problem. My head doesn't want to think straight today.

So, i have a table named "recensions", and another named "comments". In each table, i have a column named "amne_id". This would make so i could connect the comments to the correct recension.

Now, on my first page, i simply get all the recensions with the code:

$rec = $this->db->get('recensions');

What i want is to count how many comments each recension has. But i have no idea how. I guess i maybe should use JOIN and num_rows()?

Please ask if you dont understand, so i can explain better.

Have a nice day!

Answer 1

$this->db->select('COUNT(*) as count, recensions.anme_id as recension_id')
->from('recensions')
->join('comments','recensions.anme_id = comments.anme_id','left')
->group_by('anme_id');
$result = $this->db->get();

Should give you the recensions id and the comment count for that id.

then loop:

foreach($result->result() as $row){
    echo "Recension id $row->recension_id has $row->count comments<br />";
}