检索的行数与特定值后过滤查询通过日期选择器

Question

目前我正在使用CodeIgniter来检索特定时间范围内的数据。所有这些条目都有一个状态。我想对具有相同状态的所有条目进行分组，并将其显示在各自的标题中。目前，这是我的模型类，我有以下条目返回特定日期范围内的所有条目:

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

我的控制器类:

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

我已经尝试在我的phpmyadmin中使用以下命令，这给了我想要的输出，但我不知道如何在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

这里的日期必须替换为$startDate和$endDate。

Answer 1

$sql = "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 '" . $st_date . "' AND '".$end_date."'
ORDER BY `added_date` DESC";
$response = count($this->db->query($sql));
echo $response;

或

echo $response->num_rows();