统计动态列中的数据组

Question

目前我正在使用Mysql和codeigniter MVC框架来检索我的数据在一个特定的时间框架内。目前，我正在使用以下模型类从状态列检索数据计数。

模型类:

public function summary($st_date,$end_date){
$this->db->select("
SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft,
SUM(CASE WHEN status_to = 'Unpublish' THEN 1 END) AS unpublish,
SUM(CASE WHEN status_to = 'Publish' THEN 1 END) AS publish,
SUM(CASE WHEN status_to = 'Action' THEN 1 END) AS action,
);
$this->db->where('added_date >=', $st_date);
$this->db->where('added_date <=', $end_date);
return $this->db->get('crm_listings');
}

,这在我的控制器类:

$data_sum = $this->user_model->summary($startDate,$endDate)->result();
$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>
</tr>
';
if(isset($data_sum) && count($data_sum) > 0)
{
foreach($data_sum as $row ){
$draft = $row->draft ? $row->draft : 0;
$unpublish = $row->unpublish ? $row->unpublish : 0;
$publish = $row->publish ? $row->publish : 0;
$action = $row->action ? $row->action : 0;
$output .= '
<tr>
<td>'.$draft.'</td>
<td>'.$unpublish.'</td>
<td>'.$publish.'</td>
<td>'.$action.'</td>
</tr>
';
}
}
else
{
$output .= '<tr>
<td colspan="5">No Data Found</td>
</tr>';
}
$output .= '</table>';
echo $output;

但现在我注意到有不止这四个选项在数据库中,所以我想找出一种方法代替我去把这个数动态指定SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft草案变量应该是动态的,应该相应地反映在我的控制器类中,有我的显示代码。

Answer 1

您应该在查询中使用GROUP BY

public function summary($st_date,$end_date){
$this->db->select("
status_to,
SUM(1) AS sum_status
");
$this->db->where('added_date >=', $st_date);
$this->db->where('added_date <=', $end_date);
$this->db->group_by('status_to');
return $this->db->get('crm_listings');
}

控制器中的Than:

if(isset($data_sum) && count($data_sum) > 0){
$output .= '<tr>';
foreach($data_sum as $row){
$sum = $row->sum_status ? $row->sum_status : 0;
$output .= '<td>'.$sum.'</td>';
}
$output .= '</tr>';
}

如果您需要按特定顺序排列，只需将总数存储在这样的数组中:

if(isset($data_sum) && count($data_sum) > 0){
$totals = array(
"Draft" => 0,
"Unpublish" => 0,
"Publish" => 0,
"Action" => 0,
);
foreach($data_sum as $row){
$totals[$row->status_to] = $row->sum_status;
}
$output .= '
<tr>
<td>'.$totals["Draft"].'</td>
<td>'.$totals["Unpublish"].'</td>
<td>'.$totals["Publish"].'</td>
<td>'.$totals["Action"].'</td>
</tr>
';
}