CODEWITHSUNDEEP
phphtmlmysqlsqlpdo
RedGiant
RedGiant

Reputation: 4748

SQL query works in Fiddle but got error in actual php code

In this fiddle the query works perfectly to create dynamic pivots. I took the query from member bluefeet from this thread and tested it in my own table schema.

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;

But in my actual code, it throws a syntax error even though there is no modification to the query:

[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]

Here's the actual code:

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

I've also tried the PDO method but it throws an SQLSTATE[HY000]: General error. Is the query not compatible with mysql? If so, do you have any alternative?

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

Upvotes: 1

Views: 198

Answers (1)

VMai
VMai

Reputation: 10336

Your query consists of six sql statements. Either you use mysqli::multi_query() to execute these six statements in one go or you execute these six statements separately:

First statement:

  SET @sql = NULL;

Second statement

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

Third statement

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

Fourth:

   PREPARE stmt FROM @sql;

The next one is that with the desired resultset:

   EXECUTE stmt;

And the sixth and last one:

   DEALLOCATE PREPARE stmt;

As long as you use the same connection your user variables will remain intact. Maybe it would be better to create a view from the generated SELECT statement instead for later re-use.

Rudimentary code (without any error checking)

$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

With PHP 5.3 and PDO you should be able to execute it at one go: Have a look at PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND).

Upvotes: 2

Related Questions