How to group by date with an active Codeigniter record?

Question

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

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

I added the following code to my model, trying to group my data by month and year (e.g. 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 get the following error: Fatal error: Call to undefined function Month()

Want the results to look like this:

• February 2012
• January 2012
• December 2012

Answer 1

this line

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

Month is the php function for this line. If you want to use the sql MONTH and YEAR functions, this line should be like this

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

Answer 2

Your request must be

 $this->db->group_by('date'); 

You cannot run SQL functions through CI activerecord unless you use the query method

 $this->db->query("GROUP BY MONTH('date')"); 

Answer 3

Very easy to try it in Active Record

 $result = $this->db->select('DISTINCT DATE_FORMAT(`t`.`date_field`, "%M %Y") `myformat`', FALSE)->from('my_table `t`')->group_by('MONTH(`t`.`date_field`)')->order_by('t.date_field', 'DESC')->get()->result(); print_r($result); 

OR simple request

 SELECT DISTINCT DATE_FORMAT(`t`.`date_field`, "%M %Y") `myformat` FROM (`my_table` `t`) GROUP BY MONTH(`t`.`date_field`) ORDER BY `t`.`date_field` DESC; 

Output Print Array

 [0] => stdClass Object ( [myformat] => August 2011 ) [1] => stdClass Object ( [myformat] => July 2011 ) [2] => stdClass Object ( [myformat] => June 2011 ) [3] => stdClass Object ( [myformat] => March 2011 ) 

Hope this helps you. Thanks!!