Virtual columns

[takky]

Hi

 

Just trying to save on some php by trying this query:

 

  $query = "SELECT @rank := @rank +1 AS rank, userid, percent FROM ranking ORDER BY percent ASC";

 

Which I have tried directly against the mysql instance with total success.  Trying from within PHP the rank column doesn't seem to be there...  Is this even allowed, do the @ need escaping, any suggestions welcome... even if I have to use a view, please let me know.

 

Heres the code fragment:

 

    $conn = get_db_conn();

    $query = "SELECT @rank := @rank +1 AS rank, userid, percent FROM ranking ORDER BY percent ASC";

    $res = mysql_query($query,$conn);

    while($row = mysql_fetch_array($res)){

        echo $row['rank'], " ";

        echo $row['userid'], " ";

        echo $row['percent'], " <br>";

    }

 

The userid and percent are echoed the rank is simply missing... 

 

Thanks for any hep.

 

PHP newbie

[PFMaBiSmAd]

For debugging, try the following -

 

echo "<pre>",print_r($row,true),"</pre>";

[takky]

that's helpful for me as a newbie, thank you.  I also checked the SQL itself Here's the some resultset:

 

SELECT @rank := @rank +1 AS rank, userid, percent FROM ranking ORDER BY percent DESC

Array

(

    [0] =>

    [rank] =>

    [1] => 573522302

    [userid] => 573522302

    [2] => 11.36

    [percent] => 11.36

)

 

As you can see the rank "virtual column" contains no value...  Do I conclude such virtual columns as unsupported?

[takky]

this forums html render of the arrays first element seems to have gone a little west

[PFMaBiSmAd]

The problem is because @rank is initially a NULL value it won't ever be anything but a NULL.

 

You need to execute the following query first -

 

SET @rank := 0

[takky]

you are a star, I forget that bit of the q...  how dumb of me to get a ctrl-v ctrl-c job wrong! 

[takky]

i remember now... that bit of the query wasn't working so i took it off and left just the it from the SELECT.  Seems I have to set the rank firstly on this connection and then select the rank

 

    $query = "SET @rank := 0";
    mysql_query($query,$conn);
    $query = "SELECT @rank := @rank +1 AS rank, userid, percent FROM ranking ORDER BY percent DESC";
    echo "<pre>",$query,"</pre>";

 

Could these I combine these into one mysql_query() call, seemingly it doesn't like me using a ; to concatenate these statements?

 

... but i'm helped and many thanks

[PFMaBiSmAd]

mysql_query() does not support multiple queries separated by semi-colons because too many people where not validating user supplied input being put into queries to prevent sql injection.

[fenway]

Actually, you can write it this way:

 

SELECT @rank := @rank +1 AS rank, userid, percent FROM ranking, ( SELECT @rank := 0 ) t1 ORDER BY percent DESC

 

And not have to worry about it.