How to group by date with Codeigniter active record?

Question

I want to group the data in my blog table by date.

I have a datetime field in my database that I will use to group my data by month and year. ( example 2012-01-23 17:25:18 )

I have added the following code to my model in an attempt to group my data by month and year (like an archive)

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;
}

But I receive the following error: Fatal error: Call to undefined function Month()

Want the results to look like:

• February 2012
• January 2012
• December 2012

Answer 1

this line,

$this->db->group_by(Month('date'), Year('date')); 

Month is a PHP function at this line. If you want to use SQL MONTH and YEAR functions, this line must be like this:

$this->db->group_by('MONTH(date), YEAR(date)');