Grouping multiple Mysql statements to fetch count of multiple statuses

Currently I’m using Mysql and CodeIgniter to fetch my entries from my database in a particular timeframe. Each entry has a status in the database of either D,N,Y. Now to display this data I have a different statement for each of the statuses which I want to group into 1 statement.

Model Class:

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

And my controller class where I’m getting this data and displaying it in a form of a table:

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

And this is the AJAX request in my view class:

$('#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");
            }
        })

My question is that is there anyway to group all these into 1 method that’ll classify them all in thier particular heading in my controller class.

I have tried using the following query in phpmyadmin which gives me my correct output, but I don’t know how do I execute the same thing in my model and controller class:

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Use CASE statement instead of multiple queries

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:

Do not make HTML in the controller because it’s a bad practice in MVC. Use foreach loop in view file to show values
Read more about from CI Views

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

Read more about MySQL Case Statement

Method 2

try this

Your models
    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->from('crm_listings');
            $this->db->where('cast(added_date as date) BETWEEN "' . $st_date . '" AND "' . $end_date');
    
            return $this->db->get();
         }

controllers

....
$data = $this->user_model->summary($startDate,$endDate)->result();

echo $data->Draft;
echo $data->Unpublish;
echo $data->Publish;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0