I'm building a multi-game tournament scoreboard, where many players can play many games, many times. I want to assign points to players per game based on their rank for each game, not their actual score.
e.g.
Donkey Kong
Rank | Player | Score | Points Awarded
1 | Player2 | 34,000 | 1,000
2 | Player1 | 32,000 | 999
3 | Player3 | 29,000 | 998
Robotron
Rank | Player | Score | Points Awarded
1 | Player1 | 39,000 | 1,000
2 | Player3 | 32,000 | 999
3 | Player2 | 21,000 | 998
Tournament Standings
Player1 - 1,999 Points
Player2 - 1,998 Points
Player3 - 1,997 Points
So far I have ranking and points calculations working just fine...
SELECT
`id`,
`userID`,
`gameID`,
`gamescore`,
`rank`,
1001.0 - (rank) AS points
FROM (
SELECT
`id`,
`userID`,
`gameID`,
`gamescore`,
@curr_rank := IF(@prev_rank = id, @curr_rank, @curr_rank + 1) AS rank,
@prev_rank := id
FROM
`submit_score`,
(SELECT @curr_rank := 0) y,
(SELECT @prev_rank := NULL) z
WHERE `submit_score`.`tournID` = 2
ORDER BY `gamescore` DESC
) ranked_game;
But I need to be able to assign the points by rank-per-game and then have a grand total of points for each player that I can then show in a list.
Your DB should to look something like
Players
ID | Player Nickname
1 | Player1
2 | Player2
3 | Player3
Donkey Kong
PlayerID | Score
Player2ID | 34,000
Player1ID | 32,000
Player3ID | 29,000
Robotron
PlayerID | Score
Player1ID | 39,000
Player3ID | 32,000
Player2ID | 21,000
DB should be only used to store data. Rank and awarded points should be moved to the code as those are redundant parts.
Then query each table with desc sort on score columns. That will create ranked tables with top scorer as #1 etc. That retrieved data store in arrays for each game with Key->Values where Key will be a rank # (1+ incremental) and Values can be other array with stored players data (after join) and a score or a string something like PlayerX | Score# if you need a score data for anything because for real you need only Players data sort on scores per game.
After that you need to loop through player table and create an array of players where you will store tournament points retrieved from looping each game array and decrease tournament points on each rank and assign to a proper player.
Hope it helps
PS. For existing data I will create views something like
set @max_points=1000;
select Rank, Player,
@current_points := IF(Rank = 1, @max_points, @current_points-1) AS points
from DonkeyKong
for each game
then do final view to sum all tournaments points
select dk.Player AS Player,(dk.Points + ro.Points) AS Total
from RO_view AS ro
left join DK_view AS dk
on dk.Player = ro.Player