Generating user ranks in multiple games, based on score, using Subqueries.

[guile]

Hi,
Say, I am building a small gaming portal where people can play small games and score points.
a player can play different games and the scores are logged in.

now, given a user ID, how do I build my dataset such that we can retrieve a table in this sort

user_id | game_id | rank

now, the rank is relative just to that game.

i'm finding it extremely hard, that is why i am calling for some help.
this far, i've been able to fetch a similar table for a user only when the game_id was provided. I used variables in mysql for my small dysfunctional query.

[guile]

odd that nobody replied to this. it was definitely something interesting.

anyways, i found this link to be very useful for the purpose

Emulating Analytic (AKA Ranking) Functions with MySQL: Part 2

as for my case, i had information spread over several tables, so in the end my query was something like - 3 nested select statements, a couple of inner joins and use of some variables inside 'if' clause. quite complex for my standards.
and then cropped up another problem - the query was working great, but not when used through php. after wasting much of my time, i found that it requires a variable to be initialized beforehand as well. dunno why.b

[pbmods]

now, given a user ID, how do I build my dataset such that we can retrieve a table in this sort

user_id | game_id | rank

SELECT `user_id`, `game_id`, `rank` FROM `table` ORDER BY `rank` ASC;

as for my case, i had information spread over several tables, so in the end my query was something like - 3 nested select statements, a couple of inner joins and use of some variables inside 'if' clause. quite complex for my standards.
and then cropped up another problem - the query was working great, but not when used through php. after wasting much of my time, i found that it requires a variable to be initialized beforehand as well. dunno why.b

Try creating a view. That will make your life a lot easier.

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

[guile]

SELECT `user_id`, `game_id`, `rank` FROM `table` ORDER BY `rank` ASC;

That is the problem. 'rank' is not any column in my schema, and it would keep changing with newer game scores. for a simple ordering of players for all the games in order of their scores, i can use

SELECT `game_id`, `user_id`, `score` FROM `table` ORDER BY `score` GROUP BY `game_id`

which should give me something given below - except for the last row

game_id | user_id | score | rank
---------------------------------------------
1.............. ..17........200 0......1
1.............. .. 6.........1820. .....2
1.............. .28........1500 .......3
2.............. .16........3500 .......1
2.............. .19........2500 .......2

The last row is the biggest problem itself. MySQL doesnt have any built-in function for that, afaik. The link I provided in my last post agrees as well.

Try creating a view. That will make your life a lot easier.

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

Thanks for that. I'll definitely use views the next time I create complex queries.

[pbmods]

That is the problem. 'rank' is not any column in my schema, and it would keep changing with newer game scores. for a simple ordering of players for all the games in order of their scores, i can use

SELECT `game_id`, `user_id`, `score` FROM `table` ORDER BY `score` GROUP BY `game_id`

which should give me something given below - except for the last row

game_id | user_id | score | rank
---------------------------------------------
1.............. ..17........200 0......1
1.............. .. 6.........1820. .....2
1.............. .28........1500 .......3
2.............. .16........3500 .......1
2.............. .19........2500 .......2

Ah. Well, look at it this way:

Your scores are already ranked; all you have to do is write the PHP to assign a value to each row when you display it.

Something like this:

[PHP]
$lastGame = null;
$gameRank = 1;
foreach($result as $row => $data)
$data['rank'] = (($data['game_id'] == $lastGame)
? $gameRank++
: (int)(($gameRan k = 1) && ($lastGame = $data['game_id']))
);
[/PHP]

[guile]

Sorry for replying this late, I was held up in some other work, and almost forgot about this.

Ah. Well, look at it this way:

Your scores are already ranked; all you have to do is write the PHP to assign a value to each row when you display it.

Something like this:

[PHP]
$lastGame = null;
$gameRank = 1;
foreach($result as $row => $data)
$data['rank'] = (($data['game_id'] == $lastGame)
? $gameRank++
: (int)(($gameRan k = 1) && ($lastGame = $data['game_id']))
);
[/PHP]

My scores appear ranked (without the actual rank provided), but only for that simple query. The problem is backtracking when you have just the user ID and wish to display the game info, and user's rank (if he/she's played the game).

If I use something similar to what you suggest, and use a counter in my script to calculate ranks, then it will get extremely, extremely bloated. For larger databases, it would mean that I would have 99% of redundant data.

Emulating the oracle ranking functions, as suggested earlier seems the best way out.

[pbmods]

If I use something similar to what you suggest, and use a counter in my script to calculate ranks, then it will get extremely, extremely bloated. For larger databases, it would mean that I would have 99% of redundant data.

You could set up a function in an include file, or work it into your data-abstraction-layer class....

Of course, you could also just use a stored routine like the one you mentioned in your OP. :)

I just tend not to like stored routines because they're less portable than php scripts (though you can add a dumpfile to your version control repository... but you have to manually create it first... grr).

[Atli]

Hi.

If you only want to get the ranking of a single user for a single game, could you not do something like this?
[CODE=sql]

SELECT
COUNT(GameID) AS 'rank' FROM guTable
WHERE
GameID = 1
AND
Score >=
(
SELECT MAX(Score)
FROM guTable
WHERE UserID = 1
AND GameID = 1
)

[/CODE]

[Atli]

Changed the thread title, to better reflect its contents.

MODERATOR