Page 1 of 1
Get Rank
Posted: Wed Aug 01, 2007 8:48 pm
by SidewinderX
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.
Code: Select all
$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.
Posted: Wed Aug 01, 2007 8:52 pm
by John Cartwright
Code: Select all
$sql = 'SELECT * FROM `'.$db_prefix.'tl_stats` WHERE `cid` = \''. mysql_real_escape_string($cid).'\' LIMIT 1';
.. ?
Posted: Wed Aug 01, 2007 9:19 pm
by Begby
I think you are looking for something like this
Code: Select all
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.
Posted: Wed Aug 01, 2007 9:33 pm
by SidewinderX
Thanks Jcart and Begby.
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`?
Posted: Wed Aug 01, 2007 9:38 pm
by Begby
SidewinderX wrote:Thanks Jcart and Begby.
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.
Posted: Wed Aug 01, 2007 9:52 pm
by SidewinderX
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
Posted: Wed Aug 01, 2007 10:49 pm
by John Cartwright
Ah.. I knew I misunderstood.. seemed too simple :0