Pivot Tables PHP/Mysql

How do I produce a dynamic MySQL pivot table with PHP?

Assuming you are using mysqli (and not PDO) you can't use a simple query() because you want to execute multiple commands. You will need to use multi_query() in combination with store_result(), more_results() and next_result().

Here is some code I used once:

$db=mysqli_connect($databasehost,$databaseuser,$databasepass,$databasename) or die ("Connection failed!");
$result = $db->multi_query($sql);

if ($err=mysqli_error($db)) { echo $err."<br><hr>"; }

if ($result) {
  do {
  if ($res = $db->store_result()) {
      echo "<table width=100% border=0><tr>";

      // printing table headers
      for($i=0; $i<mysqli_num_fields($res); $i++)
      {
          $field = mysqli_fetch_field($res);
          echo "<td bgcolor=lightgray><b>{$field->name}</b></td>";
      }
      echo "</tr>\n";

      // printing table rows
      while($row = $res->fetch_row())
      {
          echo "<tr>";
          foreach($row as $cell) {
            if ($cell === NULL) { $cell = '(null)'; }
            echo "<td>$cell</td>";
          }
          echo "</tr>\n";
      }
      $res->free();
      echo "</table>";

    }
  } while ($db->more_results() && $db->next_result());
}
$db->close();

Pivot a mysql result set and create html table/matrix

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>

PHP Mysql pivot table

You can easily get the result by using the aggregate function sum() and by using group by with rollup:

select 
  coalesce(name, 'Total') Name, 
  sum(qty) qty,
  sum(value) value
from ist
group by name with rollup;

See SQL Fiddle with Demo. This gives the result:

|     NAME | QTY | VALUE |
| Product1 |  75 |  3000 |
| Product2 |  45 |  4500 |
|    Total | 120 |  7500 |

---

Related Topics