SQL Variable inside PHP Statement

Question

My code:

 $sql = "SET @row_number = 0; SELECT (@row_number:=@row_number + 1) AS num, player, reinforcees, reinforcers FROM _KoT_villages WHERE o = '".$data[2]."' OR o = '".$data[4]."' ORDER BY CASE WHEN player = '".$user_class->id."' THEN 1 ELSE 2 END";
    $result = mysql_query($sql) or die(mysql_error());

The error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT (@row_number:=@row_number + 1) AS num, player, reinforcees, reinforcers F' at line 1

When I echo the SQL statement, I get the following: SET @row_number = 0; SELECT (@row_number:=@row_number + 1) AS num, player, reinforcees, reinforcers FROM _KoT_villages WHERE o = '1' OR o = '5' ORDER BY CASE WHEN player = '2' THEN 1 ELSE 2 END

When I run that echod statement through phpMyAdmin, it runs successfully, and gives me rows of results.

But why doesn't it work in a PHP statement? What's going on? How do I get PHP to do what SQL is doing?

And before anyone says I haven't tried to find the answer myself, if you Google "sql variable" php site:stackoverflow.com, every question I find is about accessing SQL results in PHP (i.e., loops) or inserting PHP variables in SQL, which is not what I need. I'm trying to insert an SQL variable into the SQL statement.

Also: I realize I should stop using MySQL, and am in the process of converting, but in order to quickly resolve a bug...I'm using MySQL.

You need to use something that supports MySQL multiple queries such as [`mysqli_multi_query`](https://secure.php.net/manual/en/mysqli.quickstart.multiple-statement.php) or [PDO with emulated statements](https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd). — rink.attendant.6, Dec 30 '18 at 05:01

Nick · Answer 1 · 2018-12-31T00:08:13.883

mysql_query doesn't support multiple queries in one call. You would need to upgrade to mysqli or PDO to enable that. In the meantime though, you can implement what you want in a single query using a CROSS JOIN to initialise the row_number variable e.g.

$sql = "SELECT (@row_number:=@row_number + 1) AS num, player, reinforcees, reinforcers 
        FROM _KoT_villages 
        CROSS JOIN (SELECT @row_number := 0) r
        WHERE o = '".$data[2]."' OR o = '".$data[4]."' 
        ORDER BY CASE WHEN player = '".$user_class->id."' THEN 1 ELSE 2 END";

score 0 · Accepted Answer · answered Dec 30 '18 at 05:01

The MySQL client in php expects individual statements

Open MySQL client

First Statement:

SET @row_number = 0

2nd Statement:

SELECT
   (@row_number:=@row_number + 1) AS num,
   player,
   reinforcees,
   reinforcers
FROM
   _KoT_villages 
WHERE
   o = '".$data[2]."'
   OR o = '".$data[4]."'
ORDER BY
    CASE WHEN player = '".$user_class->id."' THEN 1
    ELSE 2 
    END

$result = mysql_query( [2nd Statement] ) or die(mysql_error());

Close MySQL client

SQL Variable inside PHP Statement

2 Answers2