phpmyadmin Question posted in
The official documentation can be found here.

SQL Variable inside PHP Statement – Phpmyadmin

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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";
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search