CodeIgniter - 按月排列我的'帖子'？ [英] CodeIgniter - order my 'posts' by month?

问题描述

我的文章

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

我的网站上我想创建一个'存档'列表来分类我的文章。 c>表的设置如下所示：
id | post_title |后| | pubdate |年| |月

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.

推荐答案

试试这个

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;
}

这篇关于CodeIgniter - 按月排列我的'帖子'？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！