php - Pivot a mysql result set and create html table/matrix

Question

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>

Answer 1

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.

1. Loop through the result set object with a foreach() -- no, you don't need to call a fetching function to access the data because the result object is iterable.
2. Create a multidimensional grouping array with names as the first level keys, then subarrays with years as keys and values as values.
3. Create an array of unique years. My approach will ensure uniqueness by assigning the year as both the key and the value -- because arrays cannot contain duplicated keys, the values will be unique without having to call array_unique() later.
4. Sort the years ASC
5. Create an array of default values for every year. In this case, I am assigning - as the default value.
6. Add the literal word name to the front of the array containing unique years -- this will be used to populate the header row of the table.
7. I prefer to use implode() to craft a variable-celled table row.
8. printf() is a clean way of blending literal text with variables -- it avoids interpolation/concatenation syntax.
9. In each subsequent table row, replace the default yearly values with the relative person's yearly values and present with implode().
10. If there is any chance that the result set is empty, then you may want to wrap most of this snippet in an 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>