[RESOLVED] get ranking with less resource used?

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?

examancer

There should be a way to accomplish what you are trying to do using nothing but SQL, but I'm not really a SQL expert so I can't really think of how that would be accomplished. As for a PHP solution... something like this?


$SQL = 'SELECT * FROM rank ORDER BY Points DESC';
$result = mysql_query($SQL);

$rank = 0;

while ($row = mysql_fetch_array($result)) {

$members[$rank] = $row;
$rank++;

if ($row['id'] == $id_you_are_looking_for) {
echo ("ID found. Your rank is $rank.");
}

}

This would assume that you want your array to start at 0 and your ranks to start at 1. If you want them both to start at 1, then move $rank++; up a line. If you want them both to start at 0, move it to the bottom of the while loop.

Mgccl

that is good, but if some one's rank is like 10000 need to loop 10000 times, , but the good thing is it only loop once for rank one...
mine solution is the one that load every data into an array and then use array_search() for the id... which cost like more resource than your script....
Anyone else got better ideas? because it will still be server consuming when someone's rank is tooo low

examancer

There should be a way to use subqueries or something to have SQL itself return the ID you are searching for along with its rank

Jack_McSlay

i've just played a bit around and I think this should extract the rank of a certain user:

    SELECT count(*)
    FROM `rank` `r1`, `rank` `r2`
    WHERE `r1`.`points` > `r2`.`points`
    AND `r2`.`id` = $user_id
    GROUP by `r2`.`id`

Mgccl

umm... I kind don't get that one, where does rank, r1, rank r2 come from?

Jack_McSlay

that means you're comparing two different entries from the rank table. r1 being one, r2 being another