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?
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 foo):
Array
(
[0] => Array
(
[@x := @x + 1] => 1
[0] => 1
)
[1] => Array
(
[@x := @x + 1] => 2
[0] => 2
)
[2] => Array
(
[@x := @x + 1] => 3
[0] => 3
)
)
Note that the associative array key is the full expression. You might want to give the column an alias in your query.
I've never used SET like that. Does it work with SELECT?
SELECT @x := @x + 1
FROM some_table CROSS JOIN
(SELECT @x := 0) y;
