Search code examples
mysqlcodeigniteractiverecordcountblogs

How to count items in group with Codeigniter?

I am using active record to return a grouped list of dates with the following code:

function get_archive_links(){

        $this->db->order_by('date','desc');
        $this->db->group_by('MONTH(date), YEAR(date)');
        $query = $this->db->get('blog'); 

    foreach ($query->result() as $row) {
        $data[] = array(
            'id' => $row->id,
            'date' => $row->date
        );
    }

    return $data;
}

Which when looped and echoed on my page returns:

  • Febuary 2012
  • January 2012
  • December 2012

How can I count each item in the group and display it in my list? Example:

  • Febuary 2012 (2)
  • January 2012 (6)
  • December 2012 (7)

I will be counting how many blog posts there where for each month.

Solution

  • You need to either remove the GROUP BY then count in PHP, or add a ->select('COUNT(id) AS num_posts') to your AR query. I'd probably add the ->select(), as it's less work :)