Codeigniter foreach view table layout

Question

Struggling to build an html table layout. Here's what I want to display:

Player | Week 1 | Week 2 | Week 3
  1    |   11   |   19   |   7
  2    |   14   |   12   |   10
  3    |    9   |   15   |   13

But here is all I have been able to get:

Player | Week 1 | Week 2 | Week 3
  1    |   11   |        |   
  1    |   19   |        |   
  1    |    7   |        |   
  2    |   14   |        |   
  2    |   12   |        |   
  2    |   10   |        |   
  3    |    9   |        |   
  3    |   15   |        |   
  3    |   13   |        |   

The values come from a single table (results) with columns 'Player', 'Week', and 'Score'

Here is the function in the model:

public function get_results(){
    for($plyr=1;$plyr<3;$plyr++) {
      $this->db->select('player, week, score');
      $this->db->from('results');
      $this->db->group_by(array('player','week'));

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

Here is the view:

<?php foreach($results as $result) : ?>
    <tr>
        <td><?php echo $result->player; ?></td>
        <td><?php echo $result->score; ?></td>
    </tr>
<?php endforeach; ?>

I'm clueless how to loop through this to display it as shown above. I tried nesting foreach loops in the view, but no luck. Can't think of how a nested foreach might work in the model function, especially since I only want the Player # to iterate only once while each of their weekly scores follow along in the same row.

edit: i have made the tables clearer

Answer 1

Pivot Results

If you always have 3 weeks, you can pivot your results by using conditional aggregation to get the score for each week

  $this->db->select("player, 
    sum(case when week = 'Week 1' then score end) 'Week 1',
    sum(case when week = 'Week 2' then score end) 'Week 2',
    sum(case when week = 'Week 3' then score end) 'Week 3'");
  $this->db->from('results');
  $this->db->group_by('player');
  $query = $this->db->get();
  return $query->result();

Alternative: Reindex Data in PHP

Another solution is to keep your current query and to reindex the data in php first by player then by week so that every player,week key is mapped to a score.

$resultsByPlayer = array();

foreach($results as $result) {
    $resultsByPlayer[$result->player][$result->week] = $result->score;
}

foreach($resultsByPlayer as $player => $resultsByWeek) {
    print "<tr><td>$player</td>";
    ksort($resultsByWeek);
    foreach($resultsByWeek as $week => $score) {
        print "<td>$score</td>";
    }
    print "</tr>";
}