[SOLVED] PHP & MySQL: How can I use "SET @rank=0;" in $query=

Issue

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

$query = "SET @rank=0; SELECT @rank:[email protected] +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:[email protected] +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:[email protected] +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?

Solution

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:[email protected]+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?

Answered By – Marvo

Answer Checked By – Dawn Plyler (BugsFixing Volunteer)