Get Rank

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

Get Rank

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Code: Select all

$sql = 'SELECT * FROM `'.$db_prefix.'tl_stats` WHERE `cid` = \''. mysql_real_escape_string($cid).'\' LIMIT 1';
.. ?
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

Post 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`?
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Ah.. I knew I misunderstood.. seemed too simple :0
Post Reply