MySQL query returns null values in PHP, but works normally
I have following code (simplified to highlight error):
SELECT @x := @x + 1 FROM some_table, (SELECT @x := 0) y
It works in mysql, but when I use it in PHP, it breaks and returns all NULL values. I know it has to do with the portion "@x + 1" because if I replace this portion with something else, for example 'test', it will work.
What could be the issue?
I've never used
SETlike that. Does it work with
SELECT @x := @x + 1 FROM some_table CROSS JOIN (SELECT @x := 0) y;
This works fine for me (tested with PHP 5.6.30 and MySQL 8.0.0).
$stmt = $pdo->query("SELECT @x := @x + 1 FROM foo, (SELECT @x := 0) y"); print_r($stmt->fetchAll(PDO::FETCH_BOTH));
Output (after I added three rows to my table
Array (  => Array ( [@x := @x + 1] => 1  => 1 )  => Array ( [@x := @x + 1] => 2  => 2 )  => Array ( [@x := @x + 1] => 3  => 3 ) )
Note that the associative array key is the full expression. You might want to give the column an alias in your query.