Mysql 数据透视表在 PhpMyadmin 中完全工作，但不能与 Php mysqli_query() 一起使用

Question

我有这个动态的MYSQL查询构建了一个在phpmyadmin（和SequelPro）中工作的数据透视表：

SET SESSION group_concat_max_len = 1000000;
SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    'MAX(CASE WHEN meta_key = "lat_poi" THEN meta_value END) AS lat_poi,
    MAX(CASE WHEN meta_key = "lng_poi" THEN meta_value END) AS lng_poi'
   ) INTO @sql

FROM wp_postmeta;
SET @sql = CONCAT('SELECT posts.post_title, ', @sql, '
               FROM wp_posts posts
                LEFT JOIN wp_postmeta postmeta 
                ON posts.ID = postmeta.post_id
                WHERE (posts.post_type = "poi" AND posts.post_status = "publish")
                GROUP BY posts.ID
               ');
PREPARE stmt FROM @sql;
EXECUTE stmt;

我想在 Php 中使用它来查询数据库，但它给出了此错误：

string(226) "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @sql = NULL; 
SELECT
   GROUP_CONCAT(DISTINCT
     'MAX(CASE WHEN meta_key = ' at line 2"
 bool(false)

这是 Php 代码：

$connection = mysqli_connect("localhost", "db_name", "db_pwd", "db_user");
$query_expression = "
SET SESSION group_concat_max_len = 1000000;
SET @sql = NULL;
SELECT
   GROUP_CONCAT(DISTINCT
     'MAX(CASE WHEN meta_key = "lat_poi" THEN meta_value END) AS lat_poi,
      MAX(CASE WHEN meta_key = "lng_poi" THEN meta_value END) AS lng_poi'
) INTO @sql

   FROM wp_postmeta;
   SET @sql = CONCAT('SELECT posts.post_title, ', @sql, '
               FROM wp_posts posts
                LEFT JOIN wp_postmeta postmeta 
                ON posts.ID = postmeta.post_id
                WHERE (posts.post_type = "poi" AND posts.post_status = "publish")
                GROUP BY posts.ID
               ');
   PREPARE stmt FROM @sql;
   EXECUTE stmt;";
$query = mysqli_query($connection, $query_expression);
var_dump(mysqli_error($connection));
var_dump($query);

有什么提示吗？

提前感谢...

Answer 1

这不是一个查询，而是一组查询。

将其拆分为单独的语句并逐个运行它们

mysqli_query($connection, "SET SESSION group_concat_max_len = 1000000");
mysqli_query($connection, "SET @sql = NULL");
$query_expression = "SELECT...";
$query = mysqli_query($connection, $query_expression);