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

Question

What I have:

I have a table in MySQL named "updates" that currently holds the following information:

What I need:

What I need is the following:

What I have done so far:

I have the following MySQL query that works:

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
     CONCAT(
       'MAX(IF(Date = ''',
   Date,
   ''', Description, NULL)) AS ',
   CONCAT("'",Date,"'")
 )
   ) INTO @sql
FROM updates;

SET @sql = CONCAT('SELECT Action, ', @sql, ' FROM updates GROUP BY Action');

PREPARE stmt FROM @sql;
EXECUTE stmt;

The actual Question

I am not able to work out how to execute this using PHP so that I can display this output on a webpage. Is anyone able to either provide me with the PHP code to perform this or point me in the right direction of information required.

I have read a number of articles but I think the issue is that I don't know what I'm actually looking for. At first I assumed it was how to run prepared statements within PHP but this didn't appear to help.

Solution

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();

Comments

[Petay87]

This seems to be exactly what I need from inspecting the page once created. However, I get the following error message: Warning: mysqli_num_fields() expects parameter 1 to be mysqli_result, boolean given in C:\wamp\www\ActionPlan\updates.inc.php on line 60

[Rik]

@Petay87 Hey, strange I didn't catch that before. Yes. $result is a boolean in case of multi_query(). Maybe this is left over from a single-query() example of mine. If you change the $result in $res in the part // printing table headers it should work better (I think). (changed in the answer)