Crosstab query in PHP and MYSQL

Question

I was looking for a way to implement a crosstab query in MySQL and found the answer from this post: Display 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. The following is the query:

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 generated sql and paste it into PHPMyAdmin, the query executes fine but gives SQL syntax error on php "Fatal error: Uncaught mysqli_sql_exception: There is an error in your SQL syntax; check accordingly Manual to your MariaDB server version to get the correct syntax to use near 'SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(week_start =...' at line 2 in..") ). There No way to write query in php?< /p>

Answer 1

how about this?