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

Please see a code like below can help you figure out a solution:

Basically one extra looping of the input data is required to create the meta data required for the output data. In several data reporting scenarios this kind to data transformation is needed.

<?PHP
//assuming mysql is giving below data
$in_data = array(
                array('ID'=>1,'name'=>'Tom','value'=>15,'year'=>2018),
                array('ID'=>2,'name'=>'Tom','value'=>4,'year'=>2019),
                array('ID'=>3,'name'=>'Tom','value'=>6,'year'=>2020),
                array('ID'=>4,'name'=>'Kate','value'=>18,'year'=>2018),
                array('ID'=>5,'name'=>'Kate','value'=>20,'year'=>2019),
            );
            
$out_data = convertInToOut($in_data);

var_dump($out_data);

function convertInToOut($inData){
    $years = array();
    $persons = array('year'=>array('name'));
    //creating meta data 
    foreach($inData as $in){
        $years[$in['year']] = $in['year'];
        $persons[$in['name']] = array($in['name']); 
    }

    sort($years,SORT_NUMERIC);
    //aligning based on year
    $yearIndex = array();
    foreach($years as $key=>$year){
        $yearIndex[$year] = $key;
        $persons['year'][] = $year;
    }
    //final step of handling values for each names
    foreach($inData as $in){
        $persons[$in['name']][$yearIndex[$in['year']]+1] = $in['value'];    
    }
    
    return $persons;
    
}           

?>

Output of var_dump($out_data) :

    array(3) {
  ["year"]=>
  array(4) {
    [0]=>
    string(4) "name"
    [1]=>
    int(2018)
    [2]=>
    int(2019)
    [3]=>
    int(2020)
  }
  ["Tom"]=>
  array(4) {
    [0]=>
    string(3) "Tom"
    [1]=>
    int(15)
    [2]=>
    int(4)
    [3]=>
    int(6)
  }
  ["Kate"]=>
  array(3) {
    [0]=>
    string(4) "Kate"
    [1]=>
    int(18)
    [2]=>
    int(20)
  }
}

Answer 2

First you need to have a separate a query which gets all the years ordered in ascending order.

'SELECT year FROM table GROUP BY year ORDER BY year ASC

Or extract the years from the result array as commented out in the loop below, but make sure to order by year ASC in your main query, to avoid calling the database twice

You should have results as such. You manipulate the array in the foreach loop, it would give you a multidimensional array by user.

You then loop over it in your html as in the code below and you should be done

   <?php

$mydata = array(
    array(
        'id' => 1,
        'name' => 'Tom',
        'value' => 5,
        'year' => 2019
    ),
    array(
        'id' => 1,
        'name' => 'Tom',
        'value' => 5,
        'year' => 2018
    ),
    array(
        'id' => 1,
        'name' => 'kate',
        'value' => 5,
        'year' => 2017
    ),
    array(
        'id' => 1,
        'name' => 'Tom',
        'value' => 5,
        'year' => 2018
    ),
    array(
        'id' => 1,
        'name' => 'kate',
        'value' => 5,
        'year' => 2018
    ),
    array(
        'id' => 1,
        'name' => 'kate',
        'value' => 5,
        'year' => 2017
    ),
    array(
        'id' => 1,
        'name' => 'joe',
        'value' => 5,
        'year' => 2016
    ),
    array(
        'id' => 1,
        'name' => 'joe',
        'value' => 5,
        'year' => 2017
    )
);

 //this line assumes that you queries the database to fetch the years
$years = ['2019','2018','2017','2016'];

$display = array();

foreach ($mydata as $data) {
   //only add this if you don't want to query the database a second 
    //time to get the years
   if(!in_array($data['year'], $years)){
       $years[] = $data['year'];
    }
    $display[$data['name']]['values'][$data['year']] = $data['value'];
    $display[$data['name']]['name'] = $data['name'];
}


?>

<html>

<body>
    <table>
        <td>year</td>
        <?php foreach ( $years as $year) { ?>
            <td><?php echo $year; ?></td>
        <?php } ?>
        <tbody>
            <?php foreach ($display as $name => $info) { ?>
                    <tr>
                        <td><?php echo $info['name']; ?></td>

                        <?php foreach($years as $year){ ?>
                        <td><?php echo isset($info['values'][$year])? $info['values'][$year] : 'null'; ?></td>
                        <?php } ?>
                    </tr>

            <?php } ?>
        </tbody>
        </th>

    </table>
</body>

</html>

Answer 3

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>