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

Quote:

Originally Posted by guile

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;

Quote:

Originally Posted by guile

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

Quote:

Originally Posted by pbmods

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

Expand|Select|Wrap|Line Numbers

 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........2000......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.

Quote:

Originally Posted by pbmods

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

Quote:

Originally Posted by guile

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

Expand|Select|Wrap|Line Numbers

 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........2000......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)(($gameRank = 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.

Quote:

Originally Posted by pbmods

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)(($gameRank = 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

Quote:

Originally Posted by guile

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?

Expand|Select|Wrap|Line Numbers

  
SELECT 

  COUNT(GameID) AS 'rank' FROM guTable

WHERE

  GameID = 1

AND

  Score >= 

    (

      SELECT MAX(Score)

      FROM guTable

      WHERE UserID = 1

      AND GameID = 1

    )

Atli

Changed the thread title, to better reflect its contents.

MODERATOR

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

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

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

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

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

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

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

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

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

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about