phpcodeigniteractiverecorddistinctcounting

Count occurrences of each value in a database table column using CodeIgniter's active record

I have a db with "user_name" and "posts". How can I get a count of posts per username using CodeIgniter's db class?

In SQL:

SELECT 
  user_name, 
  COUNT(1) as post_count
FROM posts_by_user
GROUP BY user_name

In CodeIgniter:

$this->db->select('user_name', ???????);
$this->db->from('posts_by_user');
$this->db->group_by('user_name');
$result = $this->db->query();
Solution

  • Try

    $this->db->select('user_name, COUNT(*) as cnt', FALSE);
$this->db->from('posts_by_user');
$this->db->group_by('user_name');
$result = $this->db->get();

return $result->result();