User Rank

Krakken

Hi,

A few days ago I encountered a problem when coding a ranking system for my PHP game. What I need is a way to get a specific users rank simply by ordering the MySQL query by scores.

For example:
I may have a database with:

User - Score
krakken - 100
john - 200
markus - 400
malak - 50

And I want to find john's rank compared with the others. (Should be "2").

The only method I can think of at the moment is with a for loop. To query the database and order by "Score" then go through each "User" to see what rank they have which involves querying the database every time the loop goes through. I can predict now that with a lot of users this will dramatically slow doen the load time of the page.

If anyone has a solution I would be most greatful! Thanks!

Krakken

had1980

try to select all players where score is less than the curent player, and than use mysql_num_rows to count how much users are b4 this player.
I think it will work.

Plum-Loco

How about adding another field just for RANKS?

Idea #1

That way you can find out who's below and above then loop through a much smaller selection of names.

USER - SCORE - RANK
user1 - 100 - 1
user2 - 250 - 2
user3 - 50 - 0
user4 - 423 - 4
user5 - 274 - 2

Idea #2

Put the literal rank into the new field. Then when the score changes, you can compare it to another user's rank.

USER - SCORE - RANK
user1 - 100 - 1
user2 - 250 - 2
user3 - 50 - 0
user4 - 423 - 4
user5 - 274 - 3

I'm a novice so others may have a better solution, I just thought I'd throw in my 2 cents 😉

Krakken

Originally posted by had1980
try to select all players where score is less than the curent player, and than use mysql_num_rows to count how much users are b4 this player.
I think it will work.

Hmm that's a brilliant idea! Thanks very much!

Krakken

Originally posted by Plum-Loco
How about adding another field just for RANKS?

Idea #1

That way you can find out who's below and above then loop through a much smaller selection of names.

USER - SCORE - RANK
user1 - 100 - 1
user2 - 250 - 2
user3 - 50 - 0
user4 - 423 - 4
user5 - 274 - 2

Idea #2

Put the literal rank into the new field. Then when the score changes, you can compare it to another user's rank.

USER - SCORE - RANK
user1 - 100 - 1
user2 - 250 - 2
user3 - 50 - 0
user4 - 423 - 4
user5 - 274 - 3

I'm a novice so others may have a better solution, I just thought I'd throw in my 2 cents 😉 [/B]

Ah the problem with this is the way I have done the battle system it would not work. Everything would become jumbled :/

Thanks anyway though 😃

Krakken

Well following onto this. Anyone know of a MySQL command to get rows within a certain boundary. Like get 2 users higher than a score?

Plum-Loco

SELECT `user` FROM `some_table` WHERE `score` >= 200 LIMIT 0,2;

I hope that's what your looking for... It should return the first 2 scores including/above 200.

Weedpacket

WHERE score>certain score LIMIT 2?

I don't like the extra RANK field - it breaks normalisation. I can think of some fairly nightmarish SQL to do it, but it would be easier to work thus:

Get all the names and their scores into an array in PHP (a loop:
while($row=mysql_fetch_assoc($resultset)) $players[]=$row;
Use usort() to sort the players by score (this step would be unnecessary if you just ORDERED BY score in the SQL of course - just thought of that).
$players = array_values($players); (also unnecessary if you ORDERED BY)
The top player's stats are in $player[0], the second-top player in $player[1], etc.

Krakken

Originally posted by Plum-Loco
SELECT `user` FROM `some_table` WHERE `score` >= 200 LIMIT 0,2;
I hope that's what your looking for... It should return the first 2 scores including/above 200. [/B]

Sorry I didn't explain right. I mean select like 10 or so values in the closest boundary to the user.

For example:
Imaging I have a boundary of 4. Now, if the user is rank 1st it should return all users 2nd, 3rd, 4th and 5th. If the user is rank 4th it should return 1st, 2nd, 3rd and 5th (Gets all highest values first then starts with the lower values). If the users rank is 120th it should return 116th, 117th, 118th and 119th (It can return 4 high values with no problem).

Is there a function or group of functions to do this?

Krakken

Originally posted by Weedpacket

I don't like the extra RANK field - it breaks normalisation. I can think of some fairly nightmarish SQL to do it, but it would be easier to work thus:

Get all the names and their scores into an array in PHP (a loop:
while($row=mysql_fetch_assoc($resultset)) $players[]=$row;

Use usort() to sort the players by score (this step would be unnecessary if you just ORDERED BY score in the SQL of course - just thought of that).

$players = array_values($players); (also unnecessary if you ORDERED BY)

The top player's stats are in $player[0], the second-top player in $player[1], etc.

[/B]

I used had1980's method and it works great 🙂

Weedpacket

Great! I should've thought of that.

But if you're only needing the number of rows, then it would be better to use COUNT(*) in the query, getting the database to count the rows and have it send the count to PHP. That way, only one record containing one field (the count) has to be passed from the database, instead of the slower method of sending a whole bunch of rows which then basically get thrown away.

[Edit. Rankings à la had1980]

Name  | Score  | (Computed) Rank
------+--------+----------------
Tom   |  100   |  0
Dick  |  150   |  1
Harry |  150   |  1
Mike  |  200   |  3

Hm...

Weedpacket

Using this query:

select
	count(distinct p1.score)+1 as rank
from
	players p1, players p2
where
	p1.score<p2.score
	and p2.name='$player'

The results returned are:

Name  | Score  | (Computed) Rank
------+--------+----------------
Tom   |  100   |  1
Dick  |  150   |  2
Harry |  150   |  2
Mike  |  200   |  3

Huh, the query didn't turn out as nasty as I thought.