php - SQL 查询在 Fiddle 中有效，但在实际的 PHP 代码中出错

Question

在此fiddle该查询非常适合创建动态枢轴。我从 this 的成员 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();
       }

Answer 1

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

第一句话:

  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 语句创建一个 View 以供以后重用。

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

$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 support for multiple queries (PDO_MYSQL, PDO_MYSQLND) .