I am creating a tabulation web app and displaying the score of candidates.

I am trying to place these data in their correct value, but I'm failing to do so, I have already used distinct and group by but still, the problem persists.

Here is an image of the problem:

What I would like to achieve is this data:

Code:

Model:

   public function get_score_candidates(){
        $this->db->distinct();
        $this->db->select('categories.description,candidates.fullname,categories.percent');
        $this->db->from('tabulations');  
        $this->db->join('categories','categories.cat_id = tabulations.cat_id');
        $this->db->join('candidates','candidates.cand_id = tabulations.cand_id');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_scores(){
        $this->db->distinct();
        $this->db->select('score');
        $this->db->from('tabulations');  
        $query = $this->db->get();
        return $query->result_array();

    }

View: Here, wha I did is reconsrtuct a table so I can achieve the column and row needed.

<thead >
    <tr>
        <th></th>
        <?php foreach($scores as $score): ?>
            <th scope="col"><?php echo $score['description'].' ('.$score['percent'].'%)' ?></th>
        <?php endforeach; ?>
    </tr>
</thead>
<tbody >
    <?php foreach($scores as $score): ?>

        <tr> 
            
            <td><?php echo $score['fullname'] ?></td>
            <?php foreach($cand_scores as $cand_score): ?>
                
                <td><input type="number" name="score[]"  value="<?php echo $cand_score['score'] ?>"></td>

            <?php endforeach; ?>
        </tr>
    <?php endforeach; ?>
</tbody>

CodePudding user response：

    //query in controller
    $this->db->select('cat_id,description,percent');
    $this->db->from('categories');  
    $query = $this->db->get();
    $data['categories'] =   $query->result_array();

    $cat_select = '';
    foreach($data['categories'] as $cat){
        $cat_id = $cat["cat_id"];
        $cat_desc = $cat["description"];
        $cat_select .= 'sum( CASE WHEN categories.cat_id = 
        '.$cat_id.' THEN 
        tabulations.score END ) AS '.$cat_desc.',';
    }

    $this->db->select($cat_select .' fullname');
    $this->db->from('candidates');  
    $this->db->join('tabulations','tabulations.cand_id = 
    candidates.cand_id');
    $this->db->join('categories','categories.cat_id = 
    tabulations.cat_id');
    $this->db->group_by('fullname');
    $query = $this->db->get();
    $data['cand_scores'] = $query->result_array();


    /*****************************************************/
//view

    if(!empty($cand_scores)) { ?>
    <table>
     <thead >
      <tr>
        <th></th>
        <?php foreach($categories as $score): ?>
         <th scope="col"><?php echo $score['description'].' 
            ('.$score['percent'].'%)' ?></th>
          <?php endforeach; ?>
     </tr>
   </thead>
   <tbody >
    <?php 
    foreach($cand_scores as $emp_group){
     echo '<tr>';
     echo '<td>'. $emp_group['fullname'].'</td>';

      foreach($categories as $score){ ?>  
       <td>
        <input type="number" name="score[]"  
        value="<?php echo $emp_group[$score['description']]?>">
       </td> 
      <?php } 
      echo '</tr>';
     } ?>
   </tbody>
  </table>    
 <?php }