I was searching around for a way to implement a crosstab query in MySQL and found an answer from this post show dates in rows as dynamic columns in MySQL. I tried the query and got it to work but how do I write the query in PHP using MySQL procedural style. Below is the query:

我正在寻找一种在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);

If I print out the resulting sql and paste it in PHPMyAdmin, the query executes fine but on php in give an SQL syntax error "Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(week_start =...' at line 2 in..". Is there a way to write the query in php?

如果我打印出生成的SQL并将其粘贴到PHPMyAdmin中，查询执行得很好，但在php中给出了一个SQL语法错误“FATAL ERROR：UNAOGET MYSQLI_SQL_EXCEPTION：您的SQL语法中有一个错误；请查看与您的MariaDB服务器版本对应的手册，以获得正确的语法，以接近‘SELECT GROUP_CONCAT(DISTINCT CONCAT(’MAX(IF(Week_Start=...‘at line 2 in..’)。有没有办法用php编写查询？

What about this?

这个怎么样？

<?php
// Connect to your MySQL database
$mysqli = mysqli_connect("hostname", "username", "password", "database");

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

// Define your SQL query
$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;";

// Execute your SQL query
if (mysqli_multi_query($mysqli, $sql)) {
    do {
        if ($result = mysqli_store_result($mysqli)) {
            while ($row = mysqli_fetch_row($result)) {
                printf("%s\n", $row[0]);
            }
            mysqli_free_result($result);
        }
    } while (mysqli_next_result($mysqli));
}

// Close your MySQL database connection
mysqli_close($mysqli);
?>

I found a way using mysqli multi_query function :

我找到了一种使用mysqli MULTI_QUERY函数的方法：

if ($mysqli -> multi_query($sql)) {
  do {
    // Store first result set
    if ($result = $mysqli -> store_result()) {
      while ($row = $result -> fetch_assoc()) {
        $data[] = $row;
      }
     $result -> free_result();
    }
   
     //Prepare next result set
  } while ($mysqli -> next_result());
}

// Resulting $data array:

Array
(
    [0] => Array
        (
            [@sql] => SELECT s.`ID`, s.`name`,  MAX(IF(`week_start` = "2023-08-14", `weekly_value`,0)) AS "2023-08-14",MAX(IF(`week_start` = "2023-08-21", `weekly_value`,0)) AS "2023-08-21",MAX(IF(`week_start` = "2023-08-28", `weekly_value`,0)) AS "2023-08-28"
                  FROM (SELECT * FROM crosstab WHERE `week_start` BETWEEN NOW() - INTERVAL 4 WEEK  AND NOW()) s
                 GROUP BY s.`name`
                 ORDER BY s.`ID`
        )

    [1] => Array
        (
            [id] => 4
            [name] => Jacqui
            [2023-08-14] => 29
            [2023-08-21] => 44
            [2023-08-28] => 49
        )

    [2] => Array
        (
            [id] => 5
            [name] => Monica
            [2023-08-14] => 31
            [2023-08-21] => 33
            [2023-08-28] => 31
        )

    [3] => Array
        (
            [id] => 6
            [name] => Sarah
            [2023-08-14] => 58
            [2023-08-21] => 48
            [2023-08-28] => 44
        )

)

I just need to work on the array to produce totals. Thank you to all who tried to help.

我只需要在数组上工作来生成总计。感谢所有试图帮助我们的人。