如何使用 PHP 生成动态 MySQL 数据透视表?

2021-12-25 00:00:00 php mysql mysqli

我在 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>
";

      // 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>
";
      }
      $res->free();
      echo "</table>";

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

相关文章