Getting all data in a particular timeframe for the respective agent_id

[JJM50]

Currently I'm using CodeIgniter MVC framework and Mysql to retrieve a count of data within a particular timeframe. My table data looks like this:

Where there are different agents who are changing statuses in a particular timeframe. So what I want is to layout all the agents in the table and display what status change they had made in a particular timeframe. To do this I used the following model to get all the agents in the DB:

function get_agent(){
        $this->db->distinct();
        $this->db->select("agent_id");
        $this->db->from('crm_logs');
        return $this->db->get();
     }

And this model to get a count of the status changes:

function get_agentstatus($fdate,$tdate,$agent_id){
        $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->from('crm_logs');
        $this->db->where('cast(logtime as date) BETWEEN "' . $fdate . '" AND "' . $tdate . '" AND agent_id="'.$agent_id.'"');

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

In my controller class I have used the following code to get the models:

public function agentlistings($slug='')
    {
        $fdate = $this->input->post("fdate");
    $content['tdate'] = $tdate = $this->input->post("tdate");
    if(isset($fdate)){
        $content['fdate'] =$fdate;
    }else{
        $content['fdate'] = '';
    }
    if(isset($tdate)){
        $content['tdate'] =$tdate;
    }else{
        $content['tdate'] ='';
    }
    $content['agent'] = $this->leads_model->get_agent()->result_array();
    $content['agent_status'] = $this->leads_model->get_agentstatus($fdate,$tdate,$content['agent'])->result_array();
        $main['content']=$this->load->view('crm/reports/agentlistings',$content,true);
        $this->load->view('crm/main',$main);    
    }

And the following in my View class:

<table id="statustbl" class="table-fill">
        <thead>
          <tr style="height:<?php echo $height; ?>;">
            <th>Agent</th>
            <th>Draft</th>
            <th>Unpublish</th>
            <th>Publish</th>
            <th>Action</th>
          </tr>
        </thead>
        <tbody class="table-hover">
          <?php 
          foreach ($agent as $row)
          {
              echo '<tr><td>' .$row['agent_id']. '</td></tr>';//column names
          }
          ?>
        </tbody>        
    </table>

With this code I'm able to fill up all my distinct agents into the agents column, But I do not know how to show the agent_status with thier respective agent_id.

I have tried 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, agent_id FROM 'crm_logs' WHERE cast(logtime as date) BETWEEN "2021-09-25" AND "2021-10-01" GROUP BY agent_id to get the data in the same query, but this did not return the correct output.

[Barand]

Data

+----+---------+----------+-----------+---------------------+
| id | refno   | agent_id | status_to | logtime             |
+----+---------+----------+-----------+---------------------+
| 1  | LP01552 | 57       | Draft     | 2021-10-05 10:33:12 |
| 2  | LP02552 | 57       | Unpublish | 2021-10-04 10:33:12 |
| 3  | LP03552 | 57       | Draft     | 2021-10-05 10:33:12 |
| 4  | LP04552 | 57       | Publish   | 2021-10-09 10:33:12 |
| 5  | LP05552 | 57       | Draft     | 2021-10-10 10:33:12 |
| 6  | LP06552 | 57       | Publish   | 2021-10-11 10:33:12 |
| 7  | LP07552 | 57       | Action    | 2021-10-06 10:33:12 |
| 8  | LP08552 | 58       | Draft     | 2021-10-02 10:33:12 |
| 9  | LP09552 | 58       | Unpublish | 2021-10-11 10:33:12 |
| 10 | LP09652 | 58       | Publish   | 2021-10-08 10:33:12 |
| 11 | LP08542 | 59       | Draft     | 2021-10-06 10:33:12 |
| 12 | LP09542 | 59       | Unpublish | 2021-10-06 10:33:12 |
| 13 | LP09642 | 59       | Draft     | 2021-10-07 10:33:12 |
+----+---------+----------+-----------+---------------------+

Code

<?php
$res = $db->prepare("SELECT 
                           agent_id as Agent
                         , SUM(status_to = 'Draft') as Draft
                         , SUM(status_to = 'Unpublish') as Unpublish
                         , SUM(status_to = 'Publish') as Publish
                         , SUM(status_to = 'Action') as Action
                    FROM crm_log
                    WHERE logtime BETWEEN ? AND ?
                    GROUP BY agent_id
                    ");
$res->execute( [ '2021-10-01', '2021-10-31' ] );
$data = '';
$row = $res->fetch();
$heads = "<tr><th>" . join('</th><th>', array_keys($row)) . "</th></tr>\n";
do {
    $data .= "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n";
} while ($row = $res->fetch());
?> 
                           
    <table border='1' style='border-collapse: collapse; width: 500px'>
        <?=$heads?>
        <?=$data?>
    </table>

Output