CodeIgniter Forums

hellow everyone!

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

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

(02-27-2015, 11:59 AM)Wouter60 Wrote: [ -> ]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.

ngangchill

Wouter60

frocco

ngangchill

ngangchill