CodeIgniter Forums

Full Version: how to make a date based blog archieve in ci3 using query builder
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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.