如何使用PHP生成动态MySQL数据透视表? [英] How do I produce a dynamic MySQL pivot table with PHP?

问题描述

我在MySQL中有一个名为"updates"的表，该表当前包含以下信息:

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

我需要以下内容:

我有以下有效的MySQL查询:

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;

实际问题

我无法弄清楚如何使用PHP执行此操作，因此无法在网页上显示此输出.是否有人可以向我提供PHP代码来执行此操作，还是可以向我指出所需信息的正确方向.

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.

我已经阅读了许多文章，但我认为问题是我不知道我实际上在寻找什么.起初，我以为这是如何在PHP中运行准备好的语句，但这似乎无济于事.

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.

推荐答案

假定您正在使用mysqli(而不是PDO)，则不能使用简单的query()，因为您要执行多个命令.您需要将multi_query()与store_result()，more_results()和next_result()结合使用.

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

这篇关于如何使用PHP生成动态MySQL数据透视表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！