By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,606 Members | 2,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,606 IT Pros & Developers. It's quick & easy.

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

P: 18
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.
May 4 '07 #1
Share this Question
Share on Google+
8 Replies


P: 18
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
May 5 '07 #2

pbmods
Expert 5K+
P: 5,821
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
May 5 '07 #3

P: 18
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
  1. 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.

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.
May 6 '07 #4

pbmods
Expert 5K+
P: 5,821
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
  1. 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]
May 6 '07 #5

P: 18
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)(($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.
May 16 '07 #6

pbmods
Expert 5K+
P: 5,821
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).
May 17 '07 #7

Atli
Expert 5K+
P: 5,058
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
  1.  
  2. SELECT 
  3.   COUNT(GameID) AS 'rank' FROM guTable
  4. WHERE
  5.   GameID = 1
  6. AND
  7.   Score >= 
  8.     (
  9.       SELECT MAX(Score)
  10.       FROM guTable
  11.       WHERE UserID = 1
  12.       AND GameID = 1
  13.     )
  14.  
  15.  
May 20 '07 #8

Atli
Expert 5K+
P: 5,058
Changed the thread title, to better reflect its contents.

MODERATOR
May 20 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.