Count the number of rows in each group

Question

Using CodeIgniter's Active Record class and MySQL, I have a table of posts with user_id and various other fields, and I want to count how many posts each user has made. I could get rows where user_id = $whatever and count the number of results, but I'd have to loop through every user_id and use that count_all_results() query over and over for each one.

There must be a better way! If every field just had a field with a 1 in it, I could select_sum up that field and get a count. But that seems dumb.

Many thanks in advance!

Answer 1

Hi,

Using active record should be:

$this->db->select('field1, ... ,fieldn, count(1) as number_elements_of_row);

$this->db->group_by(array('field_group_1', ... ,'field_group_n'));

$result = $this->db->get('mytable');

so $result will have what you need!

Regards, Pedro