Currently I'm using CodeIgniter to retrieve my data in a particular timeframe. All these entries have a status. I want to group all the entries that have the same status and display it in thier respective headings. Currently this is my model class where I have the following entry to return all the entries in a particular date range:
public function get_records($st_date,$end_date){ $this->db->select('*'); $this->db->from('crm_listings'); $this->db->where('(added_date BETWEEN "' . $st_date . '" AND "' . $end_date . '")'); echo $this->db->count_all_results(); } And my controller class:
function fetch_status(){ $output =''; $startDate = ''; $endDate = ''; $this->load->model('crm/user_model'); if($this->input->post('startDate')){ $startDate = $this->input->post('startDate'); } if($this->input->post('endDate')){ $endDate = $this->input->post('endDate'); } $data = $this->user_model->fetch_date($startDate,$endDate); $output .= ' <div class="table-responsive"> <table class="table table-bordered table-striped"> <tr> <th>Draft</th> <th>Unpublish</th> <th>Publish</th> <th>Action</th> <th>Unlisted</th> <th>Sold</th> <th>Let</th> </tr> '; if($data->num_rows() > 0) { foreach($data->result() as $row) { $output .= ' <tr> //Dont know what to put in here </tr> '; } } else { $output .= '<tr> <td colspan="7">No Data Found</td> </tr>'; } $output .= '</table>'; echo $output; } I have tried using the following command in my phpmyadmin which is giving me the output that I want, but I don't know how to make the query in codeigniter.
SELECT sum(case when status = 'D' then 1 else 0 end) AS Draft, sum(case when status = 'L' then 1 else 0 end) AS Let, id FROM `crm_listings` WHERE added_date BETWEEN '2021-09-24' AND '2021-09-28' ORDER BY `added_date` DESC Here the dates have to be replaced with $startDate and $endDate.
