SQL查询工作在小提琴，但得到了错误在实际的php代码 - SQL query works in Fiddle but got error in actual php code

在这种情况下，查询可以完美地创建动态枢轴。我从这个线程的成员bluefeet中获取了查询，并在我自己的表模式中测试了它。

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(a.attr_name = ''',
      attr_name,
      ''', a.attr_value, NULL)) AS ',
      attr_name
    )
  ) INTO @sql
FROM attr;
SET @sql = CONCAT('SELECT p.model_id
                    , p.model_name
                    , ', @sql, ' 
                   from model p
                   left join model_attr t
                     on p.model_id = t.model_id
                   left join attr a
                     on t.attr_id = a.attr_id
                   GROUP BY p.model_id
                    , p.model_name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

但是在我的实际代码中，即使没有修改查询，它也会抛出语法错误:

[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 'SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(a.attr_name = ''', ' at line 2]

下面是实际代码:

      $id = $_GET["id"];
      function querySelect($sql,&$rows)
      {
        $link = database_link();
        $result = mysqli_query($link,$sql);
        $rows = array();
        while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
        {
          $rows[] = $row;
        }    
        return mysqli_num_rows($result);
      }
      $sql = "
      SET @sql = NULL;
      SELECT
        GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(a.attr_name = ''',
          attr_name,
          ''', a.attr_value, NULL)) AS ',
          attr_name
        )
      ) INTO @sql
      FROM attr;
      SET @sql = CONCAT('SELECT p.model_id
                    , p.model_name
                    , ', @sql, ' 
                   from model p
                   left join model_attr t
                     on p.model_id = t.model_id
                   left join attr a
                     on t.attr_id = a.attr_id
                   GROUP BY p.model_id
                    , p.model_name');
       PREPARE stmt FROM @sql;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;
       ";
      querySelect($sql,$rows);

我也尝试了PDO方法，但它抛出了一个SQLSTATE[HY000]: General error。查询是否与mysql不兼容?如果是这样，你有其他选择吗?

       try 
       {
          $dbh = new PDO("mysql:host=$hostname;dbname=$databasename", $username, $password);
          $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
       $sql = " 
       SET @sql = NULL;
       SELECT
         GROUP_CONCAT(DISTINCT
       CONCAT(
       'MAX(IF(a.attr_name = ''',
       attr_name,
       ''', a.attr_value, NULL)) AS ',
       attr_name
      )
    ) INTO @sql
    FROM attr;
    SET @sql = CONCAT('SELECT p.model_id
                    , p.model_name
                    , ', @sql, ' 
                   from model p
                   left join model_attr t
                     on p.model_id = t.model_id
                   left join attr a
                     on t.attr_id = a.attr_id
                   GROUP BY p.model_id
                    , p.model_name');
       PREPARE stmt FROM @sql;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;                          
        ";    
       $users = $dbh->prepare($sql);             
       $users->execute();
       $results = $users->fetchAll();
       foreach($results as $result)
       {
          echo '<div>".$result["model_name"]."</div>';
       }
       $dbh = null;
       }
       catch(PDOException $e) 
       {
         echo $e->getMessage();
       }

您的查询由六个sql语句组成。要么使用mysqli::multi_query()一次执行这六条语句，要么单独执行这六条语句:

首先声明:

  SET @sql = NULL;

第二句

  SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(a.attr_name = ''',
      attr_name,
      ''', a.attr_value, NULL)) AS ',
      attr_name
    )
  ) INTO @sql
  FROM attr;

第三声明

SET @sql = CONCAT('SELECT p.model_id
                , p.model_name
                , ', @sql, ' 
               from model p
               left join model_attr t
                 on p.model_id = t.model_id
               left join attr a
                 on t.attr_id = a.attr_id
               GROUP BY p.model_id
                , p.model_name');

第四:

   PREPARE stmt FROM @sql;

下一个是期望的结果集:

   EXECUTE stmt;

第六个也是最后一个:

   DEALLOCATE PREPARE stmt;

只要您使用相同的连接，您的用户变量将保持不变。也许最好从生成的SELECT语句中创建一个视图，以便以后重用。

基本代码(没有任何错误检查)

$sql = "SET @sql = NULL;";
$result = mysqli_query($link, $sql);      // execute first statement
$sql =  "SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(a.attr_name = ''',
      attr_name,
      ''', a.attr_value, NULL)) AS ',
      attr_name
    )
  ) INTO @sql
  FROM attr;";
$result = mysqli_query($link, $sql);       // the second statement
$sql = "SET @sql = CONCAT('SELECT p.model_id
                , p.model_name
                , ', @sql, ' 
               from model p
               left join model_attr t
                 on p.model_id = t.model_id
               left join attr a
                 on t.attr_id = a.attr_id
               GROUP BY p.model_id
                , p.model_name');";
$result = mysqli_query($link, $sql);           // the third one
$sql = "PREPARE stmt FROM @sql;";
$result = mysqli_query($link, $sql);           // prepare the real query

$sql = "EXECUTE stmt;";      // Attention, we execute the generated query
querySelect($sql,$rows);     // your function to extract the desired result
$sql = "DEALLOCATE PREPARE stmt;";
$result = mysqli_query($link, $sql);           // clean up

使用PHP 5.3和PDO，你应该能够一次执行它:看看PDO支持多个查询(PDO_MYSQL, PDO_MYSQLND)