Mysql Variables not working through php mysql query

Question

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'm using this query from php; giving me this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @num := 0;

If I copy and paste that code in phpmyadmin Sql query panel, it works perfectly, but from the php code lines it's not working, seems like there's an issues while setting Vars.

Answer 1

Instead of setting the variables in a separate SET, have you tried using a 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";