Two simple MySQL statements causing syntax error

I'm confounded. The following MySQL query:

SET @a := 0;   

SELECT * 
  FROM users;

Gives the error:

Invalid query: 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 'SELECT * FROM users' at line 2`

When I switch the order of the statements, I get the same error, again on line 2 (even though I switched them)

However, either line by themselves runs fine. What could possib开发者_运维技巧ly cause this?

I bet you're trying to perform this query in the mysql_query() (or some similar function from any programming language), but it accepts only single query. So the solution is to split this queries into 2 calls.

you can do it in one query as follows:

The trick

select @a:=@a+1, u.* 
from 
 users u 
join (select @a:=0) a

or be adventerous and use a stored procedure so it's always a single call :P

Stored procedure

drop procedure if exists list_users;
delimiter #

create procedure list_users()
begin
 set @a = 0;
 select @a:=@a+1, u.* from users u;
end #

delimiter ;

call list_users();

PHP script

$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);

$result = $conn->query("call list_users()");

while($row = $result->fetch_assoc()){
 ...
}

$result->close();
$conn->close();