PHP and MySQL: how can I use "SET @rank = 0;" in $ query =

Question

In my PHP file, I use this line to retrieve data from my mySQL database:

$query = "SET @rank=0; SELECT @rank: =@rank +1 as rank, Blah Blah..."; 

If I check the SELECT statement in the phpMyAdmin SQL window (without $ query =), it works fine.

But, if I use it in PHP, I get an error message. He doesn't like "SET @rank = 0;" Little. Is there any way to use "SET @rank = 0;" when is it in "$ query ="? Is there a workaround?

The rest of the code is standard material for pulling data from db:

 public function getmyData() { $mysql = mysql_connect(connection stuff); $query = "SELECT @rank: =@rank +1 as rank, formatted_school_name, blah blah"; $result = mysql_query($query); $ret = array(); while ($row = mysql_fetch_object($result)) { $tmp = new VOmyData1(); $tmp->stuff1 = $row-> stuff1; $tmp->stuff2 = $row->stuff2; $ret[] = $tmp; } mysql_free_result($result); return $ret; } 

Update: I am trying to use the Amerb multitasking suggestion. I configured the request as follows:

 $query = "SET @rank = 0"; $query .= "SELECT @rank: =@rank +1 as rank... 

I changed the result to:

 $result = $mysqli_multi_query($query); 

But, for some reason this fails. I am on a machine with PHP 5.2. Any suggestions?

Answer 1

This guy here has a way to set the variable in the same request to zero. However, I do not have MySQL installed on this machine to try.

Here is the query he offers in his blog post:

 select @rownum: =@rownum +1 'rank', p.* from player p, (SELECT @rownum:=0) r order by score desc limit 10; 

(Is there some kind of homework that must happen due to the fact that it is related to computing? This is the third question I saw on this two days later.)

Do you check recurring points?

Answer 2

You need to enable the use of multiple queries in one, but I forgot how to do this at the moment. This is a safety feature.

Answer 3

Try it as two separate consecutive queries.