CODEWITHSUNDEEP
phpmysqlcodeignitercountgroup-by
michaelmcgurk
michaelmcgurk

Reputation: 6509

CodeIgniter - order my 'posts' by month?

In my website I'd like to create an 'archive' list to categorise my posts.

My posts table is set up like so: id | post_title | post | pubdate | year | month

Currently this model function pulls the posts:

function getNews(){
     $data = array();
     $this->db->order_by("id", "desc");
     $Q = $this->db->get('posts');
     if ($Q->num_rows() > 0){
       foreach ($Q->result_array() as $row){
         $data[] = $row;
       }
    }
    $Q->free_result();  
    return $data;
}

However, is it possible I can create a list like the following?

July (12)

June (5)

May (2)

So it lists the month and the number of posts within that month?

All my attempts thus far have ended in frustration and tears. Any help would be greatly appreciated.

Upvotes: 0

Views: 1283

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Try this one 

SELECT  `year`,`month`,(SELECT COUNT(id) FROM `posts` WHERE `month` =p.`month`) AS c
FROM `posts` p  GROUP BY p.`month` ORDER BY `month`

function getNews(){
 $data = array();

 $Q = $this->db->query('SELECT `year`,`month`,(SELECT COUNT(id) FROM `posts` WHERE `month`=p.`month`) AS c FROM `posts` p  GROUP BY p.`month`ORDER BY `month`');
 if ($Q->num_rows() > 0){
   foreach ($Q->result_array() as $row){
     $data[] = $row;
   }
}
$Q->free_result();  
return $data;
}

Upvotes: 2

AchrafSoltani
AchrafSoltani

Reputation: 216

This is not codeigniter related but i'll answer you anyway.

use SELECT Count(*) then order by 'month', write a query and run it using this

http://ellislab.com/codeigniter/user-guide/database/queries.html

Upvotes: 1

Related Questions