I want to determine a users rank which is based upon their `unique` value which is stored in a mysql database. The higher the unique value, the higher their rank. Currently I have a method that determines their rank, but I'm pretty confident it is not very efficient.
$sql = mysql_query("SELECT * FROM `".$db_prefix."tl_stats` ORDER BY `unique` DESC");
for($i = 1; $rows = mysql_fetch_assoc($sql); $i++) {
if($rows['cid'] == $cid) { //$cid was defined earlier
$rank = $i;
}
}
The above code will loop through the entire database, even if the value I'm looking for is the first value - I'm sure a 'break' would work but I want something better. Is there any other better method? And if so, please elaborate.
SELECT COUNT(*) AS rank
FROM myTable
WHERE unique < (SELECT unique FROM myTable WHERE cid = $myEscapedCIDValue)
I haven't tested this, but I think this is what you want. It will return the number of users who have a unique value less than the one with the given cid.
Begby your code looks more like what I'm looking for. Just one quick question, you used the word "UNIQUE" twice, is that refering to the SQL Keyword UNIQUE or my database column `unique`?
Begby your code looks more like what I'm looking for. Just one quick question, you used the word "UNIQUE" twice, is that refering to the SQL Keyword UNIQUE or my database column `unique`?
Whoops, its the column `unique` I forgot to quote it and it appears that the nifty syntax highlighter capitalized it for me.
Thank you! It work's perfect, just had to add a +1 [since, it will return 0 for the user with a rank of 1] and had to reverse your comparison sign. And that syntax highlighter is pretty nifty, never knew it existed