PHP PDO - Using MySQL Variables

Question

I'm trying to run a query in PHP using PDO. The query has some variables at the top to determine a rank, except the when using the SET @var in the $sql, it returns an empty rowset. If I remove the offending SQL however, it returns fine.

I don't want to return @prev_value, @rank_count or @rank_increasing in my script, only the rank it creates in the SELECT.

Can you let me know what I am doing wrong please?

Thanks

    $sql = "
    SET @prev_value = NULL;
    SET @rank_count = 0;
    SET @rank_increasing = 0;
    SELECT a.*
         , @rank_increasing := @rank_increasing + 1 AS row_num
         , CASE
           WHEN @prev_value = score 
              THEN @rank_count
           WHEN @prev_value := score 
              THEN @rank_count := @rank_increasing
           END AS rank
      FROM ( 
           -- INLINE VIEW --
           ) a
    ";
    try {
        $sth = $dbh->prepare($sql);
        $sth->execute(array($var1, $var2));
        return $sth->fetchAll(PDO::FETCH_ASSOC);
    } catch (Exception $e) {
        return $e;
    }

Possible duplicate of [How to execute mysql script with variables using PHP::PDO?](http://stackoverflow.com/questions/4683110/how-to-execute-mysql-script-with-variables-using-phppdo) — ash, Aug 03 '16 at 18:13

score 14 · Accepted Answer · edited May 23 '17 at 12:00

14

Found the solution here: https://stackoverflow.com/a/4685040/1266457

Thank you :)

To fix:

// Prepare and execute the variables first
$sql = "
SET @prev_value = NULL;
SET @rank_count = 0;
SET @rank_increasing = 0;
";
$sth = $dbh->prepare($sql);
$sth->execute();

// Run the main query
$sql = "
SELECT a.*
     , @rank_increasing := @rank_increasing + 1 AS row_num
     , CASE
       WHEN @prev_value = score 
          THEN @rank_count
       WHEN @prev_value := score 
          THEN @rank_count := @rank_increasing
       END AS rank
  FROM ( 
       -- INLINE VIEW --
       ) a
"; ...

edited May 23 '17 at 12:00

Community

1
1

answered Jul 25 '14 at 14:54

ash

1,224
3
26
46

Executing the "sets" inside of the same query throws the error, but separatelly it works fine. For, as it seems, no logical reason. So thanks, this solved the problem. – Guilherme Ferreira Dec 21 '17 at 19:18
Executing the SET statements within the same statement as an UPDATE statement didn't throw an error in my case, the query did perform normally. But I couldn't get the affected rows back from the database connection with rowCount(). Splitting up the query did the trick for me, and I can now use rowCount() to check if I updated some rows! When removing the Mysql variables from the prepare() I got my affected rows, and hence I found this post. Thanks for the answer! – Piemol May 02 '18 at 18:16
This question is a little old, but if I'm not mistaken. There is a security setting in MySQL or PHP that prevents Executing two(2) SQL statements within the same call. This is of course to prevent some forms of SQL injection. Such as using a second query to add a user to the database, or alter the table etc... I found this because I have to do a aggregation query where I have to use a user ID multiple times, and it would be easer as a variable then to keep injecting it. – ArtisticPhoenix Aug 10 '23 at 19:55

PHP PDO - Using MySQL Variables

1 Answers1

Linked

Related