Count the number of rows in each group

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!

From stackoverflow

• 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