PHP & MySQL: How can I use “SET @rank=0;” in $query=

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

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

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

But, if I use it in PHP, then I get an error. It doesn’t like the “SET @rank=0;” bit. Is there a way to use “SET @rank=0;” when it’s in “$query=” ? Is there a workaround?

The rest of the code is standard stuff for pulling data from a 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’m trying to use Amerb’s suggestion of using multi-query. I concatenated the query like so:

$query = "SET @rank = 0";

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

I changed the result to:

$result = $mysqli_multi_query($query);

But, it’s failing for some reason. I’m on a machine running PHP 5.2. Any suggestions?

Answer

This guy here seems to have a way of setting the variable in the same query to zero. I don’t have MySQL set on up on this machine to try it, though.

Here’s the query he suggests 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 homework assignment coming due somewhere having to do with computing ranks? This is the third question I’ve seen on this in two days.)

Are you checking for duplicate scores?