Geek asks and answers

Mysql variables not working via mysql php request

I have this query:

$query = " 

 SET @points := -1;
 SET @num := 0;

 SELECT `id`,`rank`,
 @num := if(@points = `rank`, @num, @num + 1) as `point_rank`
 FROM `said`
 ORDER BY `rank` *1 desc, `id` asc";

I am using this request from php; giving me this error:

You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to 'SET @num: = 0;

If I copy and paste this code into the phpmyadmin Sql query panel, it works fine, but from the php code lines it doesn't work, it seems that there are problems setting up Vars.

+5
source share
3 answers

Instead of setting the variables in a separate one SET, have you tried using CROSS JOIN:

$query = " 

SELECT `id`,
  `rank`,
  @num := if(@points = `rank`, @num, @num + 1) as `point_rank`
FROM `said`
CROSS JOIN (SELECT @points:=-1, @num:=0) c
ORDER BY `rank` *1 desc, `id` asc";
+6
source 

$result1 = mysql_query("SET @points := -1 ;");
$result2 = mysql_query("SET @num := 0;");
$result3 = mysql_query($this->query); // <-- without the SET ... query

:

mysql_query() ( )

EDIT:

PDO mysqli, mysql .

-1

More articles:


All Articles