使用group_by显示所有行 - Codeigniter

Question

I am working with codeigniter. Below is my code

public function get_content(){
   $this->db->select('*');
        $this->db->from('content_table');
        $this->db->where('status', "Active");
        $this->db->group_by('MONTH(content_publish_date)');
        $query = $this->db->get();
        return $query->result();
}

I want to extract the rows by month & result will be shown month wise.. But as per the above query i am getting only one row of each month.. i need all the rows to show results monthwise.. Please help.

Answer 1

first of all your where clause has extra parenthesis in statement

$this->db->where('status'), "Active");

change it like this

$this->db->where('status', "Active");

secondly add order by instead of group by , but order by should be on date

$this->db->order_by('DATE(content_publish_date)', 'DESC');

thirdly get current record's month in select as

$this->db->select('*,MONTH(content_publish_date) as content_publish_month');

mean your final query will be

    $this->db->select('*,MONTH(content_publish_date) as content_publish_month');
    $this->db->from('content_table');
    $this->db->where('status', "Active");
    $this->db->order_by('DATE(content_publish_date)', 'DESC');
    $query = $this->db->get();
    return $query->result_array();

then in view you will have to add a variable which will check if month is same as previous record's month then just show record else show month header too e.g:

$lastMonth = 0;
foreach($result as $oneRec)
{
    if(($lastMonth == 0) || ($lastMonth != $oneRec['content_publish_month'])
    {
        //show header
        echo date('F Y', strtotime($row['content_publish_date']));
        //check if its first record or not
        if($lastMonth != 0)
        {
            echo '</table>';
        }
        //then add table start tag
        echo = '<table><tr>
        <th>Date</th>
        <th>Title</th>
        <th>Publication</th>
    </tr>';
    }
    //show record here
    echo = '<tr>
        <td>'.$oneRec['content_publish_date'].'</td>
        <td>'.$oneRec['title'].'</td>
        <td>'.$oneRec['publication'].'</td>
    </tr>';
    //then set current record's month as last month as
    $lastMonth = $oneRec['content_publish_month'];
}
 echo '</table>';