Bang my head against the wall all night but no solution yet, Say I have Mysql table structure like this :
ID name value year
1 Tom 15 2018
2 Tom 4 2019
3 Tom 6 2020
4 Kate 18 2018
5 Kate 20 2019
...and so on...
and I would like to print the result like below by PHP and the year should be dynamic as it will be increased over the years. Please shed some light on me on what would be an approach Thanks
year |2018 |2019|2020
----------------------
Tom | 15 | 4 | 6
----------------------
Kate | 18 | 20 | ---
----- and so on ---
my code:
<table>
<?php
$mysqli = new mysqli('localhost', 'root', '123', 'news');
$report = array();
$columnIndex = 0;
$query = $mysqli->query("SELECT name, value, year FROM Testab");
while ($results = $query->fetch_assoc()) {
foreach ($results as $tos => $toa) {
$report[$tos][$columnIndex] = $toa;
}
$columnIndex++;
}
foreach ($report as $tos => $results) { ?>
<tr>
<th><?php echo $tos; ?></th>
<?php foreach ($results as $toa) { ?>
<th><?php echo $toa; ?></th>
<?php } ?>
</tr>
<?php } ?>
</table>
There will be many ways to do this; some techniques involve sql to prepare the dynamic pivot. My snippet below will use php to perform the pivot.
foreach()
-- no, you don't need to call a fetching function to access the data because the result object is iterable.array_unique()
later.-
as the default value.name
to the front of the array containing unique years -- this will be used to populate the header row of the table.implode()
to craft a variable-celled table row.printf()
is a clean way of blending literal text with variables -- it avoids interpolation/concatenation syntax.implode()
.if ($resultObject) { ... }
block.Code: (Demo)
$grouped = [];
$columns = [];
$resultObject = $mysqli->query("SELECT `name`, `value`, `year` FROM `Testab`");
foreach ($resultObject as $row) {
$grouped[$row['name']][$row['year']] = $row['value'];
$columns[$row['year']] = $row['year'];
}
sort($columns);
$defaults = array_fill_keys($columns, '-');
array_unshift($columns, 'name');
echo "<table>";
printf(
'<tr><th>%s</th></tr>',
implode('</th><th>', $columns)
);
foreach ($grouped as $name => $records) {
printf(
'<tr><td>%s</td><td>%s</td></tr>',
$name,
implode('</td><td>', array_replace($defaults, $records))
);
}
echo "</table>";
Output: (with added spacing/tabbing for easier reading)
<table>
<tr>
<th>name</th> <th>2018</th> <th>2019</th> <th>2020</th>
</tr>
<tr>
<td>Tom</td> <td>15</td> <td>4</td> <td>6</td>
</tr>
<tr>
<td>Kate</td> <td>18</td> <td>20</td> <td>-</td>
</tr>
</table>