CODEWITHSUNDEEP
mysqlcodeigniter
karisma
karisma

Reputation: 156

Count word based on category in codeigniter and sql

I found problem to make a report based on report table, for more detail please check this out :

My SQL Table

+----+-----+------------+--------------+
| No | ID  | Asset      | Link         |      
+----+-----+------------+--------------+
| 1  | 203 | Asset A    | Clear        |  
| 2  | 204 | Asset B    | Clear        | 
| 3  | 205 | Asset A    | Not Clear    |  
| 4  | 206 | Asset A    | Not Clear    |  
| 5  | 208 | Asset A    | Need Confirm |  
| 6  | 210 | Asset B    | Clear        |   
| 7  | 212 | Asset B    | Not Clear    |  
| 8  | 213 | Asset B    | Clear        |  
| 9  | 214 | Asset A    | Clear        |  
| 10 | 215 | Asset B    | Need Confirm |   
| 11 | 216 | Asset B    | Need Confirm |      
+----+-----+------------+--------------+

based on table above, i want to show information like below :

+----+--------------+-------+-----------+--------------+
| No | Count Asset  | Clear | Not Clear | Need Confirm | 
+----+--------------+-------+-----------+--------------+    
| No | Asset A      |   2   |     2     |       1      |  
| No | Asset B      |   3   |     1     |       2      |   
+-------------------+-------+-----------+--------------+ 
|  Total            |   5   |     3     |       3      |    
+-------------------+-------+-----------+--------------+

i already read some reference about pivot table in sql, tried this code :

Controller : 

$data_get= $this->db->query("SELECT asset,  
                        COUNT(CASE WHEN (link='Clear') THEN 1 ELSE 0 END) as clear,
                        COUNT(CASE WHEN (link='Not Clear') THEN 1 ELSE 0 END) as not_clear,
                        COUNT(CASE WHEN (link='Need Confirmation') THEN 1 ELSE 0 END) as need_confirm
                        FROM tbl_master GROUP BY asset");

Views :

<?php
    foreach($data_get->result_array() as $dp)
    {
    ?>
      <tr>
        <td><?php echo $dp['asset']; ?></td>
        <td><?php echo $dp['clear']; ?></td>
        <td><?php echo $dp['not_clear']; ?></td>
        <td><?php echo $dp['need_confirm']; ?></td>
      </tr>
     <?php
        }
     ?>

from my code above i got an error, there's undefined variabel.. i'm newbie here, if there correction or any advice, please inform, thanks

Upvotes: 1

Views: 456

Answers (1)

JYoThI
JYoThI

Reputation: 12085

Controller :

1st : You need to get the result in controller and send the result to view like below.

2nd : And follow @Tim Biegeleisen answer too . you need to use sum

$data_get['result'] = $this->db->query("SELECT asset,  
                        SUM(CASE WHEN link = 'Clear'             THEN 1 ELSE 0 END) AS clear,
                        SUM(CASE WHEN link = 'Not Clear'         THEN 1 ELSE 0 END) AS not_clear,
                        SUM(CASE WHEN link = 'Need Confirmation' THEN 1 ELSE 0 END) AS need_confirm
                        FROM tbl_master GROUP BY asset")->result_array();

$this->load->view ('home', $data_get);

In view :

foreach($result as $row){

}

Upvotes: 3

Related Questions