Solved How to create database for highscores?

[whitevo]

Ok, so i wanted to add feature to my game what collects data about player achivements and failures against the bosslike creatures I have on my server.
This way players have something to compare themselves, how well they are doing in PVE envoirment.

Query is now ready and working like i wanted

for x=1, #spectators do
    highscore(spectators[x]:getAccountId(), monsterName, spectators[x]:getVocation():getName())
end

function highscore(accID, monster, vocation)
local results = db.storeQuery("SELECT * FROM highscores WHERE account_id = "..accID.." AND vocation = '"..vocation.."' AND monster = '"..monster.."'")

    if not results then
        db.query("INSERT INTO highscores(account_id, vocation, monster) VALUES ("..accID..", '"..vocation.."', '"..monster.."')")
    end
   
local tempAttempts = db.storeQuery("SELECT attempts, kills, deaths FROM highscores WHERE account_id = "..accID.." AND vocation = '"..vocation.."' AND monster = '"..monster.."'")
local attempts = result.getDataInt(tempAttempts, "attempts")
local kills = result.getDataInt(tempAttempts, "kills")
local lastAttempt = 0

    if kills == 0 then lastAttempt = 1 end
    db.query("UPDATE highscores SET attempts = "..attempts+(lastAttempt).. ", kills = "..kills+(1).." WHERE account_id = "..accID.." AND vocation = '"..vocation.."' AND monster = '"..monster.."'")
end

 

[WibbenZ]

First of all, you call it "player" but you insert the account id?
W/e the value is it should be a primary value, otherwise it will be imposible to edit / delete rows without exporting and importing the schema for that table.

There is also no reason to insert the vocation, mainly since a vocation can change (eg. promotion), so it's better to use player->getVocation() or w/e the function is called (in your website).

You can use this to check if you get anything back:

'SELECT `player` FROM `table` WHERE `player` = ' .. accId .. ';'

 

[whitevo]

First of all, you call it "player" but you insert the account id?
W/e the value is it should be a primary value, otherwise it will be imposible to edit / delete rows without exporting and importing the schema for that table.

There is also no reason to insert the vocation, mainly since a vocation can change (eg. promotion), so it's better to use player->getVocation() or w/e the function is called (in your website).

You can use this to check if you get anything back:

'SELECT `player` FROM `table` WHERE `player` = ' .. accId .. ';'

what about website? i don't use website at all. (highscores will be pulled inside the game when player looks board)
Not use vocation? buts it very important to know what is the player vocation, because I want to see statistics between vocation difference too.
1 player can produce 4*'bossamount' different highscore values (because there will be 4 different vocations and x amount of bosses)


Aight good point changed the player to account xD

So i should do select like this?
'SELECT `account` FROM `highscore` WHERE `account` = ' .. accId .. 'AND 'vocation' = '..vocation..' AND 'monster' =' ..monster.. ';'

If one of the values does not exist then it will be NIL right?

W/e the value is it should be a primary value

What does it mean?
How do I create the new entry if SELECT does not exist?

 

[WibbenZ]

what about website? i don't use website at all. (highscores will be pulled inside the game when player looks board)
Not use vocation? buts it very important to know what is the player vocation, because I want to see statistics between vocation difference too.
1 player can produce 4*'bossamount' different highscore values (because there will be 4 different vocations and x amount of bosses)


Aight good point changed the player to account xD

So i should do select like this?
'SELECT `account` FROM `highscore` WHERE `account` = ' .. accId .. 'AND 'vocation' = '..vocation..' AND 'monster' =' ..monster.. ';'

If one of the values does not exist then it will be NIL right?


What does it mean?
How do I create the new entry if SELECT does not exist?

Then use Player:getVocation():getName(), because the vocation can be changed as I said.
In that case then use SELECT `bossId`.

You should use a repeat loop and until not results:next().

Well since you wanna use account (want to display acc name or id? xD) Then you will set the accId to the primary id, google it if you don't know how to do it.

You use INSERT INTO bla bla.

 

[whitevo]

Then use Player:getVocation():getName(), because the vocation can be changed as I said.

they can only change vocation when they start the game.

Ok so something like this?
function highscore(accID, monster, vocation)
local result = db.storeQuery("SELECT `monster` FROM `highscores` WHERE `account` = "..accID.." AND 'vocation' = "..vocation)

while not result:next() then
--What i do here?
end
db.query("INSERT INTO `highscore`(`account`, `attempts`, `kills`, `deaths`, `vocation`) VALUES ("..accID..","..attempts+(1)..","..kills+(1)..","..deaths..","..vocation..")"
end

 

[WibbenZ]

they can only change vocation when they start the game.

Ok so something like this?
function highscore(accID, monster, vocation)
local result = db.storeQuery("SELECT `monster` FROM `highscores` WHERE `account` = "..accID.." AND 'vocation' = "..vocation)

while not result:next() then
--What i do here?
end
db.query("INSERT INTO `highscore`(`account`, `attempts`, `kills`, `deaths`, `vocation`) VALUES ("..accID..","..attempts+(1)..","..kills+(1)..","..deaths..","..vocation..")"
end

local MONSTER_DEMON = 1

local results = db.storeQuery("SELECT `monster` FROM `highscores` WHERE `account` = "..accID.." AND 'vocation' = "..vocation)
if(not results or result.getString(results, 'monster') ~= MONSTER_DEMON) then
    db.query("INSERT INTO `highscore`(`account`, `attempts`, `kills`, `deaths`, `vocation`) VALUES ("..accID..","..attempts+(1)..","..kills+(1)..","..deaths..","..vocation..")"
end

I don't really know if that is correct, to me the things you posted look like a mess xD
But you get the point of it and should be able to handle the rest by yourself.

 

[whitevo]

local MONSTER_DEMON = 1

local results = db.storeQuery("SELECT `monster` FROM `highscores` WHERE `account` = "..accID.." AND 'vocation' = "..vocation)
if(not results or result.getString(results, 'monster') ~= MONSTER_DEMON) then
    db.query("INSERT INTO `highscore`(`account`, `attempts`, `kills`, `deaths`, `vocation`) VALUES ("..accID..","..attempts+(1)..","..kills+(1)..","..deaths..","..vocation..")"
end

I don't really know if that is correct, to me the things you posted look like a mess xD
But you get the point of it and should be able to handle the rest by yourself.

Alright seems enough information, i will now google about the primary value, fix the table and start testing scripts until i go mad or succeed.
Either way i will come here with the results in ~hour or 2

 

[whitevo]

Alright seems SELECT is not good solution.
I get so many syntax errors because it doesn't have the values in the database before and it doesn't create one.

Spoiler: error

function highscore(accID, monster, vocation)
print("ERROR HERE?")
local results = db.storeQuery("SELECT 'monster' FROM 'highscore' WHERE 'account' = "..accID.." AND 'vocation' = "..vocation)
print("OR ERROR HERE?")
    if not results or result.getString(results, 'monster') ~= monster then
        db.query("INSERT INTO highscore('account', 'attempts', 'kills', 'deaths', 'vocation', 'monster') VALUES ("..accID..","..(0)..","..(0)..","..(0)..","..vocation..","..monster..")")
    end
  
local tempAttempts = db.storeQuery("SELECT 'attempts' FROM 'highscore' WHERE 'account' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)
local attempts = result.getDataInt(tempAttempts, "attempts")
local tempKills = db.storeQuery("SELECT 'kills' FROM 'highscore' WHERE 'account' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)
local kills = result.getDataInt(tempAttempts, "kills")
local tempDeaths = db.storeQuery("SELECT 'deaths' FROM 'highscore' WHERE 'account' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)
local deaths = result.getDataInt(tempAttempts, "deaths")

print(attempts)
print(kills)
print(deaths)

    db.query("UPDATE 'highscore' SET 'attempts' = "..attempts+(1).. ", 'kills' = "..kills+(1)..", 'deaths' = "..deaths+(1).." WHERE 'account' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)
end

Killed the boss few times and the database still has zero rows.

 

[Anti]

Before you proceed I'd make the DB table look right. You should always have a field set to primary auto increment, e.q. a field called "id" just to keep track of everything (you can read this http://www.w3schools.com/sql/sql_autoincrement.asp).
Then, there's no need to have a vocation field since, as WibbenZ said, the vocation can change and you can easily get the vocation if you got the player id.

My suggestion:

CREATE TABLE IF NOT EXISTS `highscores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL DEFAULT '0',
  `player_id` int(11) NOT NULL DEFAULT '0',
  `kills` int(11) NOT NULL DEFAULT '0',
  `deaths` int(11) NOT NULL DEFAULT '0',
  `attempts` int(11) NOT NULL DEFAULT '0',
  `monster` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

 

[whitevo]

Before you proceed I'd make the DB table look right. You should always have a field set to primary auto increment, e.q. a field called "id" just to keep track of everything (you can read this http://www.w3schools.com/sql/sql_autoincrement.asp).
Then, there's no need to have a vocation field since, as WibbenZ said, the vocation can change and you can easily get the vocation if you got the player id.

My suggestion:

CREATE TABLE IF NOT EXISTS `highscores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL DEFAULT '0',
  `player_id` int(11) NOT NULL DEFAULT '0',
  `kills` int(11) NOT NULL DEFAULT '0',
  `deaths` int(11) NOT NULL DEFAULT '0',
  `attempts` int(11) NOT NULL DEFAULT '0',
  `monster` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

ok so having a value "none" was just bad? and that gave me errors?

Ok lets say I make it possible for players to change vocation some day.
And now i want to pull data:
How many times vocation druid has died to boss Big Daddy?
What happens then?
I use Player id, get vocation? and he has changed his vocation or deleted his character?
I get no data?
I don't want that.
I want this data to stay in database forever even if the accounts/chars does no longer exist.

The point of that table is:
To increase the values if conditions match.
and if not, create new row with the new conditions (account id, vocation and monster name)
apparently i need the primary key for autoincrement for ID value then?
I will try it out and see if i start getting errors.

 

[WibbenZ]

Try
"SELECT * FROM `TABLE` WHERE `IDD` = " .. id

You won't be able to edit, remove etc etc without exporting and importing the table as I said above.
You would have to loop them, then add them to a variable and then return the integer.

With your thread I expeceted more from you? ;o

local player = Player(cid)
if(not player) then
     return false
end

print(player:getVocation():getId())

It's up to you how you want the schema to be, we are just trying to guide you to make it more optimized etc.
If you wish to use the system you are you can, nothing is stopping you.

 

[whitevo]

With your thread I expeceted more from you? ;o

I know, I hate messing with database too, makes no sense to me yet.

player:getVocation():getId()

So you suggest use vocation ID instead of the vocation name?
Would it matter? I think its much easier to read the highscore table later on when I use text.

You won't be able to edit, remove etc etc without exporting and importing the table as I said above.

So isn't this what are we doing right now with LUA?

ou would have to loop them, then add them to a variable and then return the integer.

Why i need to loop? there can only be one row with these conditions, CANT be 2 of them.

This should ask does it exist? if it gets NIL VALUES it should not give errors and proceed with NIL
local results = db.storeQuery("SELECT 'monster' FROM 'highscore' WHERE 'account' = "..accID.." AND 'vocation' = "..vocation)

And if its NIL it "exports" the new row right?
db.query("INSERT INTO highscore('account', 'attempts', 'kills', 'deaths', 'vocation', 'monster') VALUES ("..accID..","..(0)..","..(0)..","..(0)..","..vocation..","..monster..")")

now this row should exist and should get values not ERRORS because I just inserted this row into database.
local tempAttempts = db.storeQuery("SELECT 'attempts' FROM 'highscore' WHERE 'account' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)


Anyway I will use Anti table (with vocation instead of player_id) and see what happens then if i have id.

EDIT: (fixed the account>vocation typo, still same error..)
Used the new table and i still get same error. Syntax is wrong.
My function looks like this now and DB picture is inside the spoiler.

function highscore(accID, monster, vocation)
print("ERROR HERE?")
local results = db.storeQuery("SELECT 'id' FROM 'highscores' WHERE 'account_id' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)
print("OR ERROR HERE?")
    if not results then
        db.query("INSERT INTO highscores('account_id', 'attempts', 'kills', 'deaths', 'vocation', 'monster') VALUES ("..accID..","..(0)..","..(0)..","..(0)..","..vocation..","..monster..")")
    end
  
local tempAttempts = db.storeQuery("SELECT 'attempts' FROM 'highscores' WHERE 'account_id' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)
local attempts = result.getDataInt(tempAttempts, "attempts")
local tempKills = db.storeQuery("SELECT 'kills' FROM 'highscores' WHERE 'account_id' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)
local kills = result.getDataInt(tempAttempts, "kills")
local tempDeaths = db.storeQuery("SELECT 'deaths' FROM 'highscores' WHERE 'account_id' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)
local deaths = result.getDataInt(tempAttempts, "deaths")

print(attempts)
print(kills)
print(deaths)

    db.query("UPDATE 'highscores' SET 'attempts' = "..attempts+(1).. ", 'kills' = "..kills+(1)..", 'deaths' = "..deaths+(1).." WHERE 'account_id' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)
end

Spoiler: db structure

 

[whitevo]

Created new query I found in stackOverflow, but I still get this hopeless syntax error.

db.query("INSERT INTO 'highscores'('account_id', 'vocation', 'kills', 'deaths', 'attempts', 'monster') SELECT * FROM (SELECT "..accID..","..vocation..","..(0)..","..(0)..","..(0)..","..monster.." WHERE NOT EXISTS ( SELECT 'account_id' FROM 'highscores' WHERE 'account_id' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster..") LIMIT 1;")

I did find a problem, is it possible that query is too long? because the error message stop at the last "=" symbol and doesn't write more stuff.

EDIT:
I MANAULLY wrote correct row into highscores
and then i tried to select it with the above db.storeQuery line i have AND STILL THIS SYNTAX ERROR

 

[Anti]

No that "query" is a complete mess.
Let's assume your table looks like this:

account_id
player_id
vocation
kills
deaths
attempts
monster

Then highscore() would look something like this:

function highscore(player, monster, killed)
    -- If no record, create new
    local results = db.storeQuery("SELECT * FROM 'highscores' WHERE 'account_id' = ".. player:getAccountId() .." AND 'player_id' = ".. player:getGuid() .." AND 'monster' = ".. monster)
    if not results then
        db.query("INSERT INTO highscores ('account_id', 'player_id', 'vocation', 'kills', 'deaths', 'attempts', 'monster') VALUES (".. player:getAccountId() ..",".. player:getGuid() ..",".. player:getVocation() ..",0,0,0,".. monster ..")")
 
    -- Else update current record
    else
        local vocation = result.getNumber(results, "vocation")
        local kills = result.getNumber(results, "kills")
        local deaths = result.getNumber(results, "deaths")
        local attempts = result.getNumber(results, "attempts")

        -- Check if player was killed or did kill the monster
        kills = if killed then kills else (kills + 1) end
        deaths = if killed then (deaths + 1) else deaths end

        db.query("UPDATE 'highscores' SET 'vocation = ".. vocation ..", 'kills' = ".. kills ..", 'deaths' = ".. deaths ..", 'attempts' = ".. (attempts + 1) .. " WHERE 'account_id' = ".. player:getAccountId() .." AND 'player_id' = ".. player:getGuid() .." AND 'monster' = ".. monster)
    end
   result.free(results)
end

Notice the parameter 'killed', if killed is set to 1, you will only update deaths (and not kills) and vice-versa. The parameter 'player' should be a player instance. I have not tested the code, it's just a guidance of how you could do it.

 

[whitevo]

No that "query" is a complete mess.
Let's assume your table looks like this:

account_id
player_id
vocation
kills
deaths
attempts
monster

Then highscore() would look something like this:

function highscore(player, monster, killed)
    -- If no record, create new
    local results = db.storeQuery("SELECT * FROM 'highscores' WHERE 'account_id' = ".. player:getAccountId() .." AND 'player_id' = ".. player:getGuid() .." AND 'monster' = ".. monster)
    if not results then
        db.query("INSERT INTO highscores ('account_id', 'player_id', 'vocation', 'kills', 'deaths', 'attempts', 'monster') VALUES (".. player:getAccountId() ..",".. player:getGuid() ..",".. player:getVocation() ..",0,0,0,".. monster ..")")

    -- Else update current record
    else
        local vocation = result.getNumber(results, "vocation")
        local kills = result.getNumber(results, "kills")
        local deaths = result.getNumber(results, "deaths")
        local attempts = result.getNumber(results, "attempts")

        -- Check if player was killed or did kill the monster
        kills = if killed then kills else (kills + 1) end
        deaths = if killed then (deaths + 1) else deaths end

        db.query("UPDATE 'highscores' SET 'vocation = ".. vocation ..", 'kills' = ".. kills ..", 'deaths' = ".. deaths ..", 'attempts' = ".. (attempts + 1) .. " WHERE 'account_id' = ".. player:getAccountId() .." AND 'player_id' = ".. player:getGuid() .." AND 'monster' = ".. monster)
    end
   result.free(results)
end

Notice the parameter 'killed', if killed is set to 1, you will only update deaths (and not kills) and vice-versa. The parameter 'player' should be a player instance. I have not tested the code, it's just a guidance of how you could do it.

Thx but i found out my both querys are working. Problem was, i used too much ' symbols and didn't round strings with them.
Your code also wont work, because it missing ' symobols around string and you are using on everywhere else where you should not.

I will add the working script tomorrow on Post 1.
Too tired to clean it up right now. DB is exhausting..

 

[WibbenZ]

I know, I hate messing with database too, makes no sense to me yet.


So you suggest use vocation ID instead of the vocation name?
Would it matter? I think its much easier to read the highscore table later on when I use text.

So isn't this what are we doing right now with LUA?

Why i need to loop? there can only be one row with these conditions, CANT be 2 of them.

This should ask does it exist? if it gets NIL VALUES it should not give errors and proceed with NIL
local results = db.storeQuery("SELECT 'monster' FROM 'highscore' WHERE 'account' = "..accID.." AND 'vocation' = "..vocation)

And if its NIL it "exports" the new row right?
db.query("INSERT INTO highscore('account', 'attempts', 'kills', 'deaths', 'vocation', 'monster') VALUES ("..accID..","..(0)..","..(0)..","..(0)..","..vocation..","..monster..")")

now this row should exist and should get values not ERRORS because I just inserted this row into database.
local tempAttempts = db.storeQuery("SELECT 'attempts' FROM 'highscore' WHERE 'account' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)


Anyway I will use Anti table (with vocation instead of player_id) and see what happens then if i have id.

EDIT: (fixed the account>vocation typo, still same error..)
Used the new table and i still get same error. Syntax is wrong.
My function looks like this now and DB picture is inside the spoiler.

function highscore(accID, monster, vocation)
print("ERROR HERE?")
local results = db.storeQuery("SELECT 'id' FROM 'highscores' WHERE 'account_id' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)
print("OR ERROR HERE?")
    if not results then
        db.query("INSERT INTO highscores('account_id', 'attempts', 'kills', 'deaths', 'vocation', 'monster') VALUES ("..accID..","..(0)..","..(0)..","..(0)..","..vocation..","..monster..")")
    end
 
local tempAttempts = db.storeQuery("SELECT 'attempts' FROM 'highscores' WHERE 'account_id' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)
local attempts = result.getDataInt(tempAttempts, "attempts")
local tempKills = db.storeQuery("SELECT 'kills' FROM 'highscores' WHERE 'account_id' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)
local kills = result.getDataInt(tempAttempts, "kills")
local tempDeaths = db.storeQuery("SELECT 'deaths' FROM 'highscores' WHERE 'account_id' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)
local deaths = result.getDataInt(tempAttempts, "deaths")

print(attempts)
print(kills)
print(deaths)

    db.query("UPDATE 'highscores' SET 'attempts' = "..attempts+(1).. ", 'kills' = "..kills+(1)..", 'deaths' = "..deaths+(1).." WHERE 'account_id' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)
end

Spoiler: db structure

Ye I hate it too haha

Fail by me, you should use name if you wish to display it, was just something I wrote up.
It would matter since you then would have to recompile the vocation userdata to get the name

Im talking about in phpmyadmin, you need a primary value for it to work.

As I said I might be lost, but you are trying to display X amount of players who have killd boss X?
In that case you need to loop the results using the repeat loop to display them.

Yes, that is why we use the if(not results) then ..., you can either use the "not" or results == nil etc etc (and im not 100% if it returns a false boolean value or a nil value (google if you want to learn more about the if statment part))

Your query is really messy to me, but it seems correct.

It will return the amount? of attempts.

Damn you write messy scripts...
First of all, you check if results exists or not and that is good.
But you then just create the column without actually adding it to the "results" variable.

You should return the script there, since you actually don't do anything more with it.
Otherwise you have to overwrite results and then continue the script.
(As of now results will still be nil, even if you insert a new column.

 

[whitevo]

As I said I might be lost, but you are trying to display X amount of players who have killd boss X?
In that case you need to loop the results using the repeat loop to display them.

no im not trying to do anything like that right now. This is future stuff.
Here all I wanted is to make entries when boss is killed.

Your query is really messy to me, but it seems correct.

well i updated my post 1 and added my "messy" script there. Why you zo hatin' on my scripts xD, I like this way of script display the most.

 

[WibbenZ]

no im not trying to do anything like that right now. This is future stuff.
Here all I wanted is to make entries when boss is killed.

well i updated my post 1 and added my "messy" script there. Why you zo hatin' on my scripts xD, I like this way of script display the most.

Then it should be enough to check if you have the playerId in the SELECT query, if not INSERT and then return the script.

I hate on all the scripts that are messy imo xD And having negative indentation does not better your case