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.

Those are 3 queries and one execution. Does your DB library support multiple queries? If it doesn't, you'll need to split it in three. — Álvaro González, Apr 01 '13 at 11:31
i've been using multiple queries through php, but i can see what you mean, the last query depends on the first ones.... any solution for that ? — Emad Ha, Apr 01 '13 at 11:34

score 6 · Accepted Answer · answered Apr 01 '13 at 11:38

6

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";

answered Apr 01 '13 at 11:38

Taryn

242,637
56
362
405

@Emad The nice thing about this is the variable are reset in the query instead of in a separate `set` statement. – Taryn Apr 01 '13 at 12:03
This also worked from java when using a jdbc PreparedStatement. Good tip! – Mazrick Mar 12 '14 at 11:50
oh man for some reason my variables werent working with the other methods and this has literally retained my employment status! THANK YOU! – Craig Wayne Jul 31 '14 at 09:06

echo_Me · Answer 2 · 2013-04-01T11:49:14.860

-1

try this

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

From the Manual:

mysql_query() sends a unique query (multiple queries are not supported)

EDIT:

You should use PDO or mysqli as mysql will soon deprecated .

edited Apr 01 '13 at 11:49

answered Apr 01 '13 at 11:34

echo_Me

37,078
5
58
78

@itachi you dont see he is taging `MYSQL` ? – echo_Me Apr 01 '13 at 11:46
the current code is : $query1= $db->select("SET @points := -1;"); $query2 = $db->select("SET @num := 0"); $query = "SELECT `id`,`rank`, @num := if(@points = `rank`, @num, @num + 1) as `point_rank` FROM `said` ORDER BY `rank` *1 desc, `id` asc"; – Emad Ha Apr 01 '13 at 11:47
1

@itachi means *"eww, the PHP ext-mysql library, which is now deprecated; ext-mysqli or PDO should be used instead."* – deceze Apr 01 '13 at 11:47
1

Hint: there are other apis for db which are NOT DEPRECATED . `MYSQL` != `mysql_*` – itachi Apr 01 '13 at 11:49

Mysql Variables not working through php mysql query

2 Answers2

Linked