問題描述

我在 MySQL 中有一個(gè)名為updates"的表，目前包含以下信息:

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

我需要的是以下內(nèi)容:

我有以下有效的 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;

實(shí)際問題

我無法弄清楚如何使用 PHP 執(zhí)行此操作，以便我可以在網(wǎng)頁上顯示此輸出.是否有人能夠向我提供 PHP 代碼來執(zhí)行此操作，或者為我指明所需信息的正確方向.

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.

我已經(jīng)閱讀了許多文章，但我認(rèn)為問題在于我不知道我實(shí)際上在尋找什么.起初我認(rèn)為這是如何在 PHP 中運(yùn)行準(zhǔn)備好的語句，但這似乎沒有幫助.

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.

推薦答案

假設(shè)您使用的是 mysqli(而不是 PDO)，您不能使用簡單的 query()，因?yàn)槟獔?zhí)行多個(gè)命令.您需要將 multi_query() 與 store_result()、more_results() 和 next_result() 結(jié)合使用.

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

這是我曾經(jīng)使用過的一些代碼:

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

這篇關(guān)于如何使用 PHP 生成動態(tài) MySQL 數(shù)據(jù)透視表?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！