D4qSdU2U

1drop table if exists player;
2
3create table player
4(
5player_id int not null auto_increment primary key,
6name varchar(32)
7);
8
9insert into player (name) values ('f00'),('bar'),('raz'),('ten'),('dev'),('dig'),('n00b');
10
11drop table if exists scores;
12
13create table scores
14(
15tourney_id int not null,
16round_id int not null,
17player_id int not null,
18score int not null,
19primary key (tourney_id, round_id, player_id)
20);
21
22insert into scores values (1,1,1,10), (1,1,2,9), (1,1,3,11), (1,1,4,10);
23insert into scores values (1,2,1,12), (1,2,2,14), (1,2,3,8), (1,2,4,16);
24insert into scores values (1,3,1,9), (1,3,2,12), (1,3,3,9), (1,3,4,11);
25
26select * from scores order by 1,2,3;
27select * from player;
28
29delimiter ;
30
31drop procedure if exists list_tourney_round_scores;
32
33delimiter ;
34
35drop procedure if exists list_tourney_round_ranks;
36
37delimiter #
38
39create procedure list_tourney_round_ranks
40(
41in p_tourney_id int,
42in p_round_id int
43)
44
45begin
46
47set @rank = 0;
48
49select
50 @rank:=@rank+1 as rank,
51 s.player_id,
52 s.score,
53 p.name
54from
55 scores s
56inner join player p on s.player_id = p.player_id
57where
58 tourney_id = p_tourney_id and 
59 round_id = p_round_id
60order by 
61 score, player_id
62limit 3;
63	
64end #
65
66delimiter ;
67
68call list_tourney_round_ranks(1,1);
69
70-- so in php you can simply do $sqlCmd = sprintf("call list_tourney_round_ranks(%d,%d)", $touneyID, $roundID);