FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Ranking system, how to do it without use too much resource





Mgccl
I have to make an ranking system which contains things that show a person's rank in points.
So let's just say there is a database only contains 2 things:

id* | Points|

and now what I want is get an id and can show people what place of rank it is in points, like rank 1 will have most points and rank 2 have 2nd most points....
and I have developed a way that:
1. SELECT id
2. ORDER by Points DESC
3. make them into PHP arrays
4. Search array for the ID I want
5. Find the row the id is in

This first sounds to me like an good idea until I found out that there might be 10000 different ids. That is going to take up a lot resource....
So anyone can think of a better way?
hexkid
Mgccl wrote:
I have to make an ranking system which contains things that show a person's rank in points.
So let's just say there is a database only contains 2 things:

id* | Points|

and now what I want is get an id and can show people what place of rank it is in points, like rank 1 will have most points and rank 2 have 2nd most points....
and I have developed a way that:
1. SELECT id
2. ORDER by Points DESC
3. make them into PHP arrays
4. Search array for the ID I want
5. Find the row the id is in

This first sounds to me like an good idea until I found out that there might be 10000 different ids. That is going to take up a lot resource....
So anyone can think of a better way?


I can do that with 3 queries ... not sure if it's better (seems like it is):
Code:
// ...
$sql = "select count(id) from YOURTABLE";
$res = mysql_query($sql);
$total = mysql_result($res, 0);

$sql = "select Points from YOURTABLE where id=$id";
$res = mysql_query($sql);
$points = mysql_result($res, 0);

$sql = "select count(id) from YOURTABLE where Points >= $points";
$res = mysql_query($sql);
$pos = mysql_result($res, 0);
$rank_percent = 100 * ($pos / $total);
$rank_n_outof = $pos . '/' . $total;
// ...


Maybe you can create a stored procedure out of this, or a subselect works and you can reduce it all to 2 queries ...
Code:
$sql = "select count(id) from YOURTABLE where Points >= (select Points from YOURTABLE where id=$id)"
Related topics
3 things about google
psychostats
No Media Player Windows
Ranking system
Brazil Still On Top
COMMAND & CONQUER: GENERALS - I LOVE! THIS GAME!
Ranking system
Which free e-mail service do you prefer?
The Flood distribution of Animals
Should quality be taken into account for ranking?
what is Page Rank what is Page Rank ?
Star Craft 2
Ranking system
Take Care Of The Mac System
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.