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
– Álvaro González, Commented Apr 1, 2013 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
– Emad Ha, Commented Apr 1, 2013 at 11:34

Taryn · Accepted Answer · 2013-04-01 11:38:43Z

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 1, 2013 at 11:38

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Taryn Over a year ago

@Emad The nice thing about this is the variable are reset in the query instead of in a separate set statement.

Mazrick Over a year ago

This also worked from java when using a jdbc PreparedStatement. Good tip!

Craig Wayne Over a year ago

oh man for some reason my variables werent working with the other methods and this has literally retained my employment status! THANK YOU!

echo_Me · Accepted Answer · 2013-04-01 11:49:14Z

-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 1, 2013 at 11:49

answered Apr 1, 2013 at 11:34

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

4 Comments

echo_Me Over a year ago

@itachi you dont see he is taging MYSQL ?

Emad Ha Over a year ago

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

deceze Over a year ago

@itachi means "eww, the PHP ext-mysql library, which is now deprecated; ext-mysqli or PDO should be used instead."

itachi Over a year ago

Hint: there are other apis for db which are NOT DEPRECATED . MYSQL != mysql_*

Collectives™ on Stack Overflow

Mysql Variables not working through php mysql query

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related