对多个Mysql语句进行分组以获取多个状态的计数[重复]

目前，我正在使用 Mysql 和 CodeIgniter 在特定时间范围内从数据库中获取条目。每个条目在数据库中的状态为 D、N、Y。现在，为了显示此数据，我对每个状态都有一个不同的语句，我想将其分组为 1 个语句。

型号类别：

public function get_records_draft($st_date,$end_date){
        $this->db->select('*');
        $this->db->from('crm_listings');
        $this->db->where('cast(added_date as date) BETWEEN "' . $st_date . '" AND "' . $end_date . '" AND status= "D"');

         return $this->db->get();
     }

     public function get_records_unpublish($st_date,$end_date){
        $this->db->select('*');
        $this->db->from('crm_listings');
        $this->db->where('cast(added_date as date) BETWEEN "' . $st_date . '" AND "' . $end_date . '" AND status= "N"');

        return $this->db->get();
     }

     public function get_records_publish($st_date,$end_date){
        $this->db->select('*');
        $this->db->from('crm_listings');
        $this->db->where('cast(added_date as date) BETWEEN "' . $st_date . '" AND "' . $end_date . '" AND status= "Y"');

        return $this->db->get();
     }

在我的控制器类中，我获取这些数据并以表格的形式显示它：

$data = $this->user_model->get_records_draft($startDate,$endDate);
$data1 = $this->user_model->get_records_unpublish($startDate,$endDate);
$data2 = $this->user_model->get_records_publish($startDate,$endDate);

$output .= '
              <div class="table-responsive">
                 <table class="table table-bordered table-striped">
                    <tr>
                    <th>Draft</th>
                    <th>Unpublish</th>
                    <th>Publish</th>
                    </tr>
              ';
           if($data->num_rows() > 0)
           {
            $output .= '
               <tr>
               <td>'.$data->num_rows().'</td>
               <td>'.$data1->num_rows().'</td>
               <td>'.$data2->num_rows().'</td>
               </tr>
            ';
            
           }
           else
           {
              $output .= '<tr>
                 <td colspan="5">No Data Found</td>
                 </tr>';
           }
           $output .= '</table>';
           echo $output;

这是我的视图类中的 AJAX 请求：

$('#alertbox').click(function(){
            var startDate = $('#startDate').val();
            var endDate = $('#endDate').val();
            // var status = $('#status').val();
            if(startDate != '' && endDate != '' ){
            $.ajax({
                url:"<?php echo base_url(); ?>testcontroller/fetch_status",
                method:"POST",
                data:{startDate:startDate, endDate:endDate},
                success:function(data){
                    $('#result').html(data)
                }
            })
            }else{
                alert("Please enter a date");
            }
        })

我的问题是，无论如何都可以将所有这些分组到一种方法中，该方法会将它们全部分类到我的控制器类中的特定标题中。

我尝试在 phpmyadmin 中使用以下查询，它给出了正确的输出，但我不知道如何在模型和控制器类中执行相同的操作：

SELECT COUNT(status) FROM `crm_listings` WHERE added_date BETWEEN '2021-09-23' AND '2021-09-29' GROUP BY status

Use CASE语句而不是多个查询

Model:

public function summary($st_date,$end_date){
      $this->db->select("
         SUM(CASE WHEN status = 'D' THEN 1 END) AS draft,
         SUM(CASE WHEN status = 'N' THEN 1 END) AS unpublish,
         SUM(CASE WHEN status = 'Y' THEN 1 END) AS publish"
      );
      $this->db->where('added_date >=', $st_date);
      $this->db->where('added_date <=', $end_date);
      return $this->db->get('crm_listings');
 }

View:

不要在控制器中制作 HTML，因为这在 MVC 中是一种不好的做法。使用foreach在视图文件中循环以显示值 阅读更多关于来自CI Views https://codeigniter.com/userguide3/general/views.html#creating-loops

<div class="table-responsive">
    <table class="table table-bordered table-striped">
        <tr>
           <th>Draft</th>
           <th>Unpublish</th>
           <th>Publish</th>
         </tr>
    <?php      
       if(isset($data) && count($data) > 0){
          foreach($data as $row ){ ?>
           <tr>
           <td><?= $row->draft ?></td>
           <td><?= $row->unpublish ?></td>
           <td><?= $row->publish ?></td>
           </tr>
       <?php } //Foreach end here

          } else { ?>
          <tr>
             <td colspan="5">No Data Found</td>
          </tr>
       <?php } ?>
      </table>

阅读更多关于MySQL 案例陈述 https://dev.mysql.com/doc/refman/5.7/en/case.html