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?

2 answers

  • answered 2017-06-17 18:35 Gordon Linoff

    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;
    

  • answered 2017-06-17 18:35 Bill Karwin

    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.