Mysql数据透视表在PhpMyadmin中完全可用，但不适用于Php mysqli_query() [英] Mysql pivot table is fullly working in PhpMyadmin but not with Php mysqli_query()

问题描述

我已经通过此动态MYSQL查询构建了一个可在phpmyadmin(和SequelPro)中使用的数据透视表:

I've this dynamic MYSQL query building a pivot table that works in phpmyadmin (and 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中使用它来查询数据库，但它会出现此错误:

I want to use it in Php to query the database but it gives this error:

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

有任何提示吗?

预先感谢...

推荐答案

这不是查询，而是一组查询.

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

Split it into separate statements and run them one by one

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

这篇关于Mysql数据透视表在PhpMyadmin中完全可用，但不适用于Php mysqli_query()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！