Codeigniter foreach lookup table layout
Struggling with building html table layout. Here's where 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's everything I managed 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 | |
Values are taken from one 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's a view:
<?php foreach($results as $result) : ?>
<tr>
<td><?php echo $result->player; ?></td>
<td><?php echo $result->score; ?></td>
</tr>
<?php endforeach; ?>
I don't know how to do this to display it as shown above. I have tried nesting foreach loops in the viewpoint, but no luck. There is no way to think about how a nested foreach might work in a model function, especially since I want Player # to iterate only once, while each of its weekly evaluations is repeated on the same line.
edit: I made the tables clearer
source to share
Summary results
If you always have 3 weeks, you can roll up your results using conditional aggregation to get an estimate 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 save the current request and re-index the data in php, first by the player, then weekly so that each is player,week
mapped to 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>";
}
source to share