Grouping multiple Mysql statements to fetch count of multiple statuses - Phpmyadmin

JJM50
October 6, 2021
272 views
0 votes
2 Answers

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

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;

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>

Grouping multiple Mysql statements to fetch count of multiple statuses – Phpmyadmin

Answers