Page 1 of 1
Ranking (Ladder) System
Posted: Wed Jul 19, 2006 7:03 am
by azazeldaemon
Hi,
I'm developing an online game site and I just can't figure out the best way to make de users ranking system.
My first approach was to create a Rank field in the user table that would be updated every hour. That approach is the easy one but it's also very CPU intensive because I'm expecting 10000+ users registered on the site.
My second approach was to display the ranking dynamically with a SQL query and PHP. This is a very good solution to TOP tables, justa a SQL query like
Code: Select all
select id,name from users order by points limit 10
to create a TOP 10 table.
I'd like to show the ranking of one specific player on the player info, that is the problem! An easy way to calculate the ranking of a single user is to use the query
Code: Select all
select count(*) from users where points > player_point
where player_point is a known value. This system works very well but it equal rank players with equal points, I don't want this to happen. If there are players with the same points, the rank should follow the number of games won.
Anybody can suggest something?
Thanks
Posted: Wed Jul 19, 2006 8:22 am
by someberry
Your second option is the best way to do it, but why not use some other factor such as last logged in, alphabetical player name to ORDER the query.
You need to decide whether you want people to have equal ranks (join 33nd place for example) or have clear defining ranks.
Posted: Wed Jul 19, 2006 8:36 am
by azazeldaemon
Hi someberry,
Thanks for the reply.
I want a unique rank for each player. The rank would be ordered by points and games won (in case of same points), to create a TOP list of users, the command:
Code: Select all
select id,name from users order by points desc, games_won desc
works pretty well.
Let's imagine that the player "User" was classified in the 9th position by this command. Now, lets imagine that I'm navigating in the site and opened the "User"'s info page, in this page I'd like to show the rank position of this player (9th). How do I do that? That is my problem.
I know that I can repeat de command above and search for the player position in the results, but it's very slow. I'd like to have the postion (rank) of the player direct from an query. The query:
Code: Select all
select count(*) from users where points>User_points
does not work because it doesn't take the games_won field into account.
How do I do that?
Thanks
ps: sorry for my english, it is my second language.
Posted: Wed Jul 19, 2006 9:40 am
by WorldCom
Maybe use something like:
Code: Select all
$i= 0;
// cycle through your query
// and each query
$rank $i + 1;
$update = "UPDATE your_table SET rank='$rank' WHERE user='$user_id'";
$update_info = mysql_query($update) or die ('Database Error_2: ' . mysql_error());
That should set the 'rank' value to a number you could access.
Posted: Wed Jul 19, 2006 9:48 am
by Ollie Saunders
Let's imagine that the player "User" was classified in the 9th position by this command. Now, lets imagine that I'm navigating in the site and opened the "User"'s info page, in this page I'd like to show the rank position of this player (9th). How do I do that? That is my problem.
Say you have a table of player rankings 1-10 and you have the players names hyperlinked. When you click the hyperlink you want to go to a page that displays more information about that player. you don't want to have to recalculate the player again by his position, which incidently might have changed, instead pass the id to the player information page with GET:
Code: Select all
<!-- table of top ranked players -->
<td>Rank 1</td><td><a href="playerInfo.php?id=435">SuperPlayer</a></td>
Code: Select all
// playerInfo.php
$id = (int)$_GET['id'];
$q = 'SELECT stuff about player FROM player WHERE id = '. $id;
// display stuff
Posted: Wed Jul 19, 2006 12:15 pm
by azazeldaemon
WorldCom,
In your solution, I should have a rank field on users table. I don't want this solution because it's very slow to update 10000+ users.
Ole,
Yes, when I click in the hyperlink (name) of the player, I go to an user detail page. In this detail page I'd like to have the rank of the player shown. It's easy to send the rank calculated on the top 10 table to the detail page as a querystring. This approach solve the problem to recalculate the rank but it limits me to only hyperlink players that appears on the top 10 table.
The user details page should be accessible from anywhere on the site. Once there, it should calculate the rank of that player and show it.
Thanks
Posted: Wed Jul 19, 2006 12:21 pm
by Ollie Saunders
This approach solve the problem to recalculate the rank but it limits me to only hyperlink players that appears on the top 10 table.
It does?
How about this:
Code: Select all
// playerInfo.php
if (isset($_GET['id'])) {
$id = (int)$_GET['id'];
} else {
if (!isset($_GET['rank'])) {
exit('No rank or id param');
}
$id = findPlayerFromRank((int)$_GET['rank']);
}
$q = 'SELECT stuff about player FROM player WHERE id = '. $id;
// display stuff
Posted: Wed Jul 19, 2006 1:56 pm
by azazeldaemon
No dude, the problem is not how to "tell" the info page which user to show stuff about, the problem is once in the info page how to make it display the rank of that user.
In the info page, I must have a function that calculates the rank of the user with his points and number of games won values. This calculated rank must be the same that appears in an top list (since the points of every player keeps the same). Got it?
It's simple to make a SQL query ordering the results by number of points, with this I have the players rank. But what I want is to calculate the rank to a specific player.
I guess I have the solution, if I query the DB with this command:
Code: Select all
select (select count(*) from users where points>User_points) + (select count(*) from users where points=User_points and games_won>User_games_won)
That will return me the position of the player i.e. the rank of the player! I guess my question was more about mysql than php.
Well the above solution works, but is it fast? Can I improve it?
Thanks
Posted: Wed Jul 19, 2006 2:09 pm
by Ollie Saunders
No dude, the problem is not how to "tell" the info page which user to show stuff about, the problem is once in the info page how to make it display the rank of that user.
there's nothing stopping you sending that in the query string as well.