Making Table head and its data dynamic in CodeIgniter

[JJM50]

Currently I'm using CodeIgniter and Mysql to fetch my data. Here I'm using the following model to get a count of each status from my database:

function get_total(){
        $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,
        SUM(CASE WHEN status_to = 'Unlisted' THEN 1 END) AS unlisted,
        SUM(CASE WHEN status_to = 'Sold' THEN 1 END) AS sold,
        SUM(CASE WHEN status_to = 'Let' THEN 1 END) AS let");
        $this->db->from('crm_logs');
        $results = $this->db->get();
        return $results;
     }  

Then to get this model I've used the following controller class:

public function totallistings($slug='')
    {  
        $fdate = $this->input->post("fdate");
        $tdate = $this->input->post("tdate");

        if ($fdate) {
        $content['fdate'] = $fdate;
        } else {
        $content['fdate'] = '';
        }
        if ($tdate) {
        $content['tdate'] = $tdate;
        } else {
        $content['tdate'] = '';
        }
        $content['data_total'] = $this->leads_model->get_total()->result();
        $main['content']=$this->load->view('crm/reports/totallistings',$content,true);
        $this->load->view('crm/main',$main);    
    }

And this is my View class:

<?php
         if(isset($data_sum) && count($data_sum) > 0)
           {
            foreach($data_sum as $row ){
               $draft = $row->draft ? $row->draft : 0;
               $unpublish = $row->unpublish ? $row->unpublish : 0;
               $publish = $row->publish ? $row->publish : 0;
               $action = $row->action ? $row->action : 0;
               $unlisted = $row->unlisted ? $row->unlisted : 0;
               $sold = $row->sold ? $row->sold : 0;
               $let = $row->let ? $row->let : 0;                              
          ?>
              <tr>
                    <td><?= $draft ?></td>
                    <td><?= $unpublish ?></td>
                    <td><?= $publish ?></td>
                    <td><?= $action ?></td>
                    <td><?= $unlisted ?></td>
                    <td><?= $sold ?></td>
                    <td><?= $let ?></td>
              </tr>
          <?php }  }  else { ?>
           
          <?php }  ?>

I now found out that there are more status words other than draft, publish, etc in my database. So I wanted a way to make this `<th>` and `<td>` dynamic but still having the same functionality it is currently having.

[Barand]

I'd do it this way

$res = $db->query("SELECT status_to
                        , COUNT(*) as tot
                   FROM crm_log
                   GROUP BY status_to     
                  ");
$totals = array_column($res->fetchAll(), 'tot', 'status_to');

$heads = "<tr><th>" . join('</th><th>', array_keys($totals)) . "</th></tr>\n";
$vals = "<tr><td>" . join('</td><td>', $totals) . "</td></tr>\n";
?> 
<table border='1'>
    <?=$heads?>
    <?=$vals?>
</table>                           
      

[JJM50]

14 hours ago, Barand said:

I'd do it this way

$res = $db->query("SELECT status_to
                        , COUNT(*) as tot
                   FROM crm_log
                   GROUP BY status_to     
                  ");
$totals = array_column($res->fetchAll(), 'tot', 'status_to');

$heads = "<tr><th>" . join('</th><th>', array_keys($totals)) . "</th></tr>\n";
$vals = "<tr><td>" . join('</td><td>', $totals) . "</td></tr>\n";
?> 
<table border='1'>
    <?=$heads?>
    <?=$vals?>
</table>                           
      

Thanks! Simple and really easy to understand