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? — ash
– ash, Commented Aug 3, 2016 at 18:13

Community · Accepted Answer · 2017-05-23 12:00:59Z

13

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, 2017 at 12:00

CommunityBot

11 silver badge

answered Jul 25, 2014 at 14:54

ash

1,2324 gold badges26 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Guilherme Oliveira Over a year ago

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.

Piemol Over a year ago

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!

ArtisticPhoenix Over a year ago

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.

Collectives™ on Stack Overflow

PHP PDO - Using MySQL Variables

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related