时间戳

首页 > 解决方案 > 计算特定值并分别显示

php - 计算特定值并分别显示

问题描述

我有以下表格项目(project_id,project_name) 任务(tid,project_id,status)

我想计算每个状态（进行中、完成、待定）并显示在单独的列中

我的尝试：

控制器：

public function overview(){
        $data['main_view'] = 'projects/overview';
       
        $data['data']=$this->project_model->get_data();

 
          $this->load->view('layouts/main' , $data);
}

看法：

<table class="table table-bordered">
        
            <thead>
            <tr> 
                <th>#</th>
                <th>Project Name</th>
                <th>Client Name</th>
                <th>Due</th>
                <th>Pending Tasks</th>
                <th>In progress Tasks</th>
                <th>Completed</th>
                
            </tr>
            </thead>
            <tbody>
                <?php foreach ($data as $row): ?>
                <tr>
                    <td><?php echo $row->project_id ?></td>
                    <td><?php echo $row->project_name ?></td>
                    <td><?php echo $row->name ?></td>
                    <td><?php echo $row->due_date ?></td>
                    <td><?php echo $row->pending_tasks ?></td>
                    <td><?php echo $row->in_progress ?></td>
                    <td><?php echo $row->completed_tasks ?></td>
                  
                    
                </tr>
                <?php endforeach;?>
            </tbody>
       
        
    </table>

模型：

public function get_data(){
        $status = array(
            'status' =>'Completed' ,
            'status' =>'Pending',
            'status' =>'In progress'
        );
        $this->db->select("projects.*,Count('tasks.*') as pending_tasks,Count('tasks.*') as completed_tasks,Count('tasks.*') as in_progress,clients.name");
        $this->db->join('tasks','tasks.project_id=projects.project_id');
        $this->db->join('clients','clients.id=projects.client_id');
        $this->db->where_IN(['tasks.status'=> $status]);
        $this->db->group_by('tasks.project_id');
        $q =$this->db->get('projects');
        return $q->result();
        
}

我得到的结果是每列中具有相应状态的任务总数。它没有分裂

标签： phpmysqlcodeigniter

解决方案

在您的选择中使用 SUM IF（或 CASE WHEN）：

$this->db->select("projects.*,COUNT(SUM(IF(tasks.status='Pending',1,0))) as pending_tasks,COUNT(SUM(IF(tasks.status='Completed',1,0))) as completed_tasks,COUNT(SUM(IF(tasks.status='In progress',1,0))) as in_progress,clients.name");

这仅计算与条件匹配的行。请参阅https://www.w3schools.com/sql/func_mysql_if.asp。

推荐阅读