PHP 和 MYSQL 中的交叉表查詢

Question

我正在尋找一種在 MySQL 中實現交叉表查詢的方法，並從這篇文章中找到了答案：在 MySQL 中將行中的日期顯示為動態列。我嘗試了該查詢並讓它工作，但如何使用 MySQL 過程風格在 PHP 中編寫查詢。以下是查詢：

SELECT
  GROUP_CONCAT(DISTINCT
               CONCAT('MAX(IF(`week_start` = "', `week_start`,'", `weekly_value`,0)) AS "',DATE(`week_start`),'"')
              ) INTO @sql
FROM (SELECT * FROM crosstab WHERE `week_start` BETWEEN NOW() - INTERVAL 4 WEEK  AND NOW()) t1;


SET @sql = CONCAT('SELECT s.`ID`, s.`name`,  ', @sql, ' 
                  FROM (SELECT * FROM crosstab WHERE `week_start` BETWEEN NOW() - INTERVAL 4 WEEK  AND NOW()) s
                 GROUP BY s.`name`
                 ORDER BY s.`ID`');
                 
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

$sql = "SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
               CONCAT('MAX(IF(`week_start` = \"', `week_start`,'\", `weekly_value`,0)) AS \"',DATE(`week_start`),'\"')
              ) INTO @sql
FROM (SELECT * FROM crosstab WHERE `week_start` BETWEEN NOW() - INTERVAL 4 WEEK  AND NOW()) t1;


SET @sql = CONCAT('SELECT s.`ID`, s.`name`,  ', @sql, '
                  FROM (SELECT * FROM crosstab WHERE `week_start` BETWEEN NOW() - INTERVAL 4 WEEK  AND NOW()) s
                 GROUP BY s.`name`
                 ORDER BY s.`ID`');

SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;";

// echo $sql;
// exit;


$report_query = mysqli_query($conn, $sql);

如果我列印出生成的sql 並將其貼到PHPMyAdmin 中，查詢執行正常，但在php 上給出SQL 語法錯誤「致命錯誤：未捕獲mysqli_sql_exception：您的SQL 語法中有錯誤；檢查相應的手冊到您的MariaDB 伺服器版本，以取得在'SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(week_start =...' at line 2 in..") 附近使用的正確語法。有沒有辦法在php 中寫查詢？< /p>

Answer 1

這個怎麼樣？