how to make a date based blog archieve in ci3 using query builder

hellow everyone!

i think its a stupid question but i cant figured it out.

i want to make a blog archieve based on date i.e

January 2015 (4) // month_name (total_post number)
February 2015 (1)
etc.....

please some one help me.plz discuss in details such as table (date column type), and query builder...



Thnx.

Try this:

Code:

$this->db->select('DATE_FORMAT(datetime,"%Y%m") AS month',NULL,FALSE)
->select('COUNT(id) AS num_per_month')
->from('posts')
->group_by('month');
$query = $this->db->get()->result_array();

Assuming that your table name is 'posts' and this table has a field called 'datetime'.
The result is an array with rows. Each row has the columns 'month' and 'num_per_month'.
E.g.: $r['month'] => '201501' stands for: January 2015.

Code:

$this->db->select('*, count(*) as cnt')->from('blogs');
$this->db->group_by("blog_date");
$data['query'] = $this->db->get();

Code:

foreach ($query->result() as $row) {
    echo $row->blog_date. "  ". $row->cnt."<br/>";
}

thnx #Wouter60 & #frocco

Try this:

Code:

$this->db->select('DATE_FORMAT(datetime,"%Y%m") AS month',NULL,FALSE)
->select('COUNT(id) AS num_per_month')
->from('posts')
->group_by('month');
$query = $this->db->get()->result_array();

Assuming that your table name is 'posts' and this table has a field called 'datetime'.
The result is an array with rows. Each row has the columns 'month' and 'num_per_month'.
E.g.: $r['month'] => '201501' stands for: January 2015.

is it works in sqlit3?
if answer is not, than what will be the solution.

thanks.