Leaderboard/Highscore for game in PHP

Question

I have a problem with PHP leaderboard where I need to create a leaderboard for people that are using my program.

The program is sending scores to database and later displaying all game scores for selected player on separate page. Scores are being cleaned from the database after 24 hours. What I need is to use another database (which I already have) in the same php that is used for sending game scores. But instead the new function is supposed to send scores to other database (that one won't clean the scores up).

The function is supposed to add a new line in database only if the user who played doesn't exist. If player exist and the score is sent I'd like the scores to be added to the same column and calculate them (sum).

For example: A player got 10 kills and 10 deaths. He plays new game and his score is 5 kills and 5 deaths. Database would auto-sum that score with previous one which would cause his DB column to change from 10 kills and 10 deaths to 15 kills and 15 deaths (math/sum/added or w/e you call it the 5 kills and 5 deaths). Well so is there a way I can do this? Purpose of the leaderboard is not to show the user's greatest score but his total kills and total deaths from all the time.

Any suggestions?

I suggest making this look less like a wall of text, and provide concrete issues with code that you need help with. — Kritner, Jun 20 '16 at 14:21
There are no code issues, I just don't know how to do it. I need some sort of function to insert values to database columns that will calculate the scores. If tehre was alrdy a score just do math by summing it for example 10 kills - old score 5 kills = new score so it should change to 15 kills on the database for the players column. — QuestionForPHP, Jun 20 '16 at 15:06
rather than trying to update a sum... just insert new records for each game, then you can group by user and get a sum. — Kritner, Jun 20 '16 at 15:50

score 1 · Answer 1 · answered Jun 20 '16 at 18:31

Rather than doing this psuedo:

Scenario 1:

// user exists
// determine current score
// update user, current score + new game score

Scenario 2:

// user does not exist
// insert user with new game score

Here, you're trying to keep a single row updated with the most up to date total. While this can definitely be done, it can be done more simply as long as you're ok with a group by, and actually keeping track of all games, rather than just the summed total of stats. You can get the summed total stats from the individual pieces.

GameRecord:

playId int primary key identity
userId int
deaths int
kills int

whether or not a user has had a "GameRecord" before does not matter.

-- Game 1
insert into GameRecord (userId, deaths, kills)
values (42, 0, 5)

-- Game 2
insert into GameRecord (userId, deaths, kills)
values (42, 1, 5)

-- Game 3
insert into GameRecord (userId, deaths, kills)
values (1, 0, 1)

With this data, rather than keeping a "rolling total" you're just keeping a record of each game. Then you can do things like:

select    
          userId,
          count(1) as totalGamesPlayed,
          sum(deaths) as totalDeaths,
          sum(kills) as totalKills
from      GameRecord
group by  userId

Returns:

userId     totalGamesPlayed     totalDeaths     totalKills
42         2                    1               10
1          1                    0               1

Hey, thanks for this it's useful but since I have already came up with this: http://prntscr.com/bisq3n few minutes ago. However still one problem, how can I order it by my sum function? it looks like this : http://prntscr.com/bisr11 basically I want the one user who is having the biggest Kills sum number which is $qty to be at #1 table position — QuestionForPHP, Jun 20 '16 at 19:11
Nevermind, I used your steps and then used ORDER BY totalKills DESC thanks alot. — QuestionForPHP, Jun 20 '16 at 19:50

Leaderboard/Highscore for game in PHP

1 Answers1