[HELP]Drift points database SQLite

[spoty]

hey

i was wondering if some one wanne help me with making a local save database for drift points

i wanne make a drift database that save's the scores and load the score's into internal.db if thats possible

please can someone help i am verry noob at database scripting

Edited April 24, 2015 by Guest

[spoty]

ok so i have scripted a small one and it works nice but it fails at 1 point

after reconnect it shows me the last saved points and when i get new points it dont add them to the old points but just replace the old points with new points

here is the code i hope someone can help me to fix the points

addEventHandler ("onPlayerLogin",root, 
function (thePreviousAccount, theCurrentAccount, autoLogin) 
    local anterior = getAccountData (theCurrentAccount,"Last_Drift") 
    local total = getAccountData (theCurrentAccount,"Total_Drift") 
    local mejor = getAccountData (theCurrentAccount,"Best_Drift") 
    setElementData(source, "Last Drift", anterior) 
    setElementData(source, "Total Drift", total) 
    setElementData(source, "Best Drift", mejor) 
end) 
  
  
  
addEventHandler ("onPlayerLogout", root, 
function() 
    if not kickPlayer (source, nil, "Logging out is disallowed.") then -- if the player doesn't get kicked(no admin rights) 
        setElementData(source, "Last Drift", false) 
        setElementData(source, "Total Drift", false) 
        setElementData(source, "Best Drift", false) 
    end 
end) 
  
  
addEventHandler ("onPlayerQuit", root, 
function (quitType, reason, responsibleElement) 
    local account = getPlayerAccount (source) 
    if not isGuestAccount (account) then 
        local anterior = getElementData(source, "Last Drift") 
        local total = getElementData(source, "Total Drift") 
        local mejor = getElementData(source, "Best Drift") 
        setAccountData (account,"Last_Drift",anterior) 
        setAccountData (account,"Total_Drift",total) 
        setAccountData (account,"Best_Drift",mejor) 
    end 
end) 

[Dimos7]

in password put the password you have with "" and in database the name of database with = ""

database = mysql_connect("127.0.0.1", "root", password, database) 
  
function saveDrifts()  
     local serial = getPlayerSerial(source) 
     local anterior = getElementData(source, "Last Drift") 
     local total = getElementData(source, "Total Drift") 
     local mejor = getElementData(source, "Best Drift") 
     local q = mysql_query(database, "SELECT * FROM `accounts` WHERE `serial`= ' "..serial.." ' ") 
     if (mysql_num_row(q) == 0) then 
            mysql_query(database, "INSERT INTO accounts(`serial`, anterior, total, mejor VALUES (' "..serial.." ', " ..anterior..", "..total..", "..mejor..")") 
   else 
         mysql_query(database, "UPDATE `accounts` SET anterior = "..anterior..", total = " ..total..", mejor = " ..mejor.." WHERE `serial` = ' " ..serial.. " ' ") 
     end 
end 
  
function loadDrifts() 
      local serial = getPlayerSerial(source) 
      local result = mysql_query(database, "SELECT * FROM `accounts` WHERE `serial` = ' "..serial.." ' ") 
      if result then 
         while true do 
         local row = mysql_fetch_assoc(result) 
         if not row then break end 
         setElementData(source, "Last Drift", anterior) 
         setElementData(source, " Total Drift", total) 
         setElementData(source, "Best Drift", mejor) 
        end 
     end 
end 
addEventHandler("onPlayerJoin", root, loadDrifts) 
addEventHandler("onPlayerQuit", root, saveDrifts) 

[spoty]

in password put the password you have with "" and in database the name of database with = ""

database = mysql_connect("127.0.0.1", "root", password, database) 
  
function saveDrifts()  
     local serial = getPlayerSerial(source) 
     local anterior = getElementData(source, "Last Drift") 
     local total = getElementData(source, "Total Drift") 
     local mejor = getElementData(source, "Best Drift") 
     local q = mysql_query(database, "SELECT * FROM `accounts` WHERE `serial`= ' "..serial.." ' ") 
     if (mysql_num_row(q) == 0) then 
            mysql_query(database, "INSERT INTO accounts(`serial`, anterior, total, mejor VALUES (' "..serial.." ', " ..anterior..", "..total..", "..mejor..")") 
   else 
         mysql_query(database, "UPDATE `accounts` SET anterior = "..anterior..", total = " ..total..", mejor = " ..mejor.." WHERE `serial` = ' " ..serial.. " ' ") 
     end 
end 
  
function loadDrifts() 
      local serial = getPlayerSerial(source) 
      local result = mysql_query(database, "SELECT * FROM `accounts` WHERE `serial` = ' "..serial.." ' ") 
      if result then 
         while true do 
         local row = mysql_fetch_assoc(result) 
         if not row then break end 
         setElementData(source, "Last Drift", anterior) 
         setElementData(source, " Total Drift", total) 
         setElementData(source, "Best Drift", mejor) 
        end 
     end 
end 
addEventHandler("onPlayerJoin", root, loadDrifts) 
addEventHandler("onPlayerQuit", root, saveDrifts) 

its not working it wont save i think because my server dont allow mysql connections is there alsow a local way without mysql?

[Walid]

simply you need to do something like this last points + new points (onPlayerQuit)

Example :

-- Server side

addEventHandler ("onPlayerQuit", root, 
function () 
    local account = getPlayerAccount (source) 
    if not isGuestAccount (account) then 
      local anterior = getElementData(source, "Last Drift")  
        addPlayerDriftScore(source, "Last Drift", tonumber(anterior)) 
    end 
end) 

function addPlayerDriftScore(player, drifttype, amount) 
    if (isElement(player) and drifttype) then 
        local account = getPlayerAccount(player) 
        if (isGuestAccount(account)) then return end 
        local current = getAccountData(account, drifttype) or 0 
        setAccountData(account, drifttype, current + amount) 
    end 
end 

untested but it should work fine

[spoty]

simply you need to do something like this last points + new points (onPlayerQuit)

Example :

-- Server side

addEventHandler ("onPlayerQuit", root, 
function () 
    local account = getPlayerAccount (source) 
    if not isGuestAccount (account) then 
      local anterior = getElementData(source, "Last Drift")  
        addPlayerDriftScore(source, "Last Drift", tonumber(anterior)) 
    end 
end) 

function addPlayerDriftScore(player, drifttype, amount) 
    if (isElement(player) and drifttype) then 
        local account = getPlayerAccount(player) 
        if (isGuestAccount(account)) then return end 
        local current = getAccountData(account, drifttype) or 0 
        setAccountData(account, drifttype, current + amount) 
    end 
end 

untested but it should work fine

ok i gonna make it fully working to total best and last i will reply fast wen i need help

[spoty]

hmm its not working and it also dont give any error and it wont save anything

[Walid]

hmm its not working and it also dont give any error and it wont save anything

just use this when the player get new points you don't need to use setElementData:

function addPlayerDriftScore(player, drifttype, amount) 
    if (isElement(player) and tostring(drifttype)) then 
        local account = getPlayerAccount(player) 
        if (isGuestAccount(account)) then return end 
        local current = getAccountData(account, tostring(drifttype)) or 0 
        setAccountData(account, tostring(drifttype), current + amount) 
    end 
end 

[ALw7sH]

Using sqlite is so much better

[spoty]

i heared ppl say that but i dont know sqlite scripting

[spoty]

can someone please help me i still dind have fixed yet

[spoty]

anyone can help me to make a sqlite database for saving drift points?

[ALw7sH]

Can you post your drift points script?

[spoty]

currently i have this

addEventHandler ("onPlayerQuit", root, 
function () 
    local account = getPlayerAccount (source) 
    if not isGuestAccount (account) then 
      local anterior = getElementData(source, "Last Drift") 
        addPlayerDriftScore(source, "Last Drift", tonumber(anterior)) 
    end 
end) 
  
addEventHandler ("onPlayerQuit", root, 
function () 
    local account = getPlayerAccount (source) 
    if not isGuestAccount (account) then 
      local anterior = getElementData(source, "Total Drift") 
        addPlayerDriftScore(source, "Total Drift", tonumber(total)) 
    end 
end) 
  
addEventHandler ("onPlayerQuit", root, 
function () 
    local account = getPlayerAccount (source) 
    if not isGuestAccount (account) then 
      local anterior = getElementData(source, "best Drift") 
        addPlayerDriftScore(source, "Best Drift", tonumber(mejor)) 
    end 
end) 
  
function addPlayerDriftScore(player, drifttype, amount) 
    if (isElement(player) and tostring(drifttype)) then 
        local account = getPlayerAccount(player) 
        if (isGuestAccount(account)) then return end 
        local current = getAccountData(account, tostring(drifttype)) or 0 
        setAccountData(account, tostring(drifttype), current + amount) 
    end 
end 

and i was trying sqlite but i quited after this

local connection = dbConnect ( "sqlite" , "Drift_DB.db" ) 
local queryHandle = dbQuery ( connection , "CREATE TABLE IF NOT EXISTS someTable (column1 TEXT,column2 TEXT,column3 TEXT)" ) 
executeSQLDropTable("Last Drift") 
executeSQLDropTable("Last Drift") 
executeSQLDropTable("Total Drift") 

[WhoAmI]

-- CHANGE IF NECCESSARY 
dataName  = { 
    ["last"] = "Last Drift", 
    ["total"] = "Total Drift", 
    ["best"] = "Best Drift" 
} 
-- type here what the elementData's names 
  
addEventHandler ( "onResourceStart", root, 
    function ( ) 
        -- creating connection and if true creates column if not exist 
        connection = dbConnect ( "sqlite" , "Drift_DB.db" ) 
        if ( connection ) then 
            dbQuery ( connection , "CREATE TABLE IF NOT EXISTS `drift` ( `data` TEXT, `account` TEXT )" ); 
        end 
        -- creating connection and if true creates column if not exist 
    end 
); 
  
function saveDriftPoints ( ) 
    local account = getPlayerAccount ( source ); -- getting account 
    if ( isGuestAccount ( account ) ) then -- checking if it is valid account 
        return; 
    end 
     
    local accName = getAccountName ( account ); -- getting name 
     
    local t = { } -- creating table for drift's data 
    for k, v in pairs ( dataName ) do 
        local data = getElementData ( source, v ); 
        if ( data ) then 
            t[v] = data; -- inserting values to table from ElementData 
        end 
    end 
     
    local check = dbPoll ( dbQuery ( connection, "SELECT `data` FROM `drift` WHERE `account`=?", accName ), -1 ); -- checking if exist row 
    if ( #check > 0 ) then 
        dbExec ( "UPDATE `drift` SET `data`=? WHERE `account`=?", toJSON ( t ), accName ); -- if yes updating 
    elseif ( #check == 0 ) then 
        dbExec ( "INSERT INTO `drift` ( `data`, `account` ) VALUES ( ?, ? )", toJSON ( t ), accName ); -- if not creating 
    end 
end 
addEventHandler ( "onPlayerQuit", root, saveDriftPoints ); 
  
function loadDriftPoints ( _, account ) 
    if ( isGuestAccount ( account ) ) then -- checking if it is valid account 
        return; 
    end 
     
    local accName = getAccountName ( account ); -- getting name  
     
    local check = dbPoll ( dbQuery ( connection, "SELECT `data` FROM `drift` WHERE `account`=?", accName ), -1 ); -- checking if row exist 
    if ( #check > 0 ) then -- if yes 
        local d = check[1]; 
         
        local t = fromJSON ( d["data"] ); 
        if ( type ( t ) == "table" ) then 
            for k, v in pairs ( t ) do 
                setElementData ( source, k, v ); -- setting ElementData from saved drift's table in table 
            end 
        end 
    end 
end 
addEventHandler ( "onPlayerLogin", root, loadDriftPoints ); 

This is s-side script. Didn't test. Change values in table on top and check.

Create 'Drift_DB.db' file and add also to meta.xml line

<file src="Drift_DB.db" /> 

Edited April 24, 2015 by Guest

[Walid]

[lua]

Add also to meta.xml line

<file src="Drift_DB.db" /> 

Why he need to add Sqlite db to meta file ???

[WhoAmI]

Because if he wants to have db file in resource he has to create the file and add it to meta. That's obvious.

[Walid]

Because if he wants to have db file in resource he has to create the file and add it to meta. That's obvious.

Lol are you serious he can use dbExec and he don't need to add the sqlite db to the meta file.

[WhoAmI]

If you don't know what are you talking about just stop posting. He can create his own SQLite database in his own .db file in his own resource if he wants to.

Now I am asking you. Are you serious?

Nevermind, stop spamming.

[Walid]

If you don't know what are you talking about just stop posting. He can create his own SQLite database in his own .db file in his own resource if he wants to.

Now I am asking you. Are you serious?

Nevermind, stop spamming.

lol i'm not spamming the topic , and i know what i'm talking about just because it's the first time that i saw something like this

Adding sqlite db to the meta file.

Anyways nvm.

[WhoAmI]

So as far as I can see, you have seen low amount of things. If he doesn't want to rubish the internal.db database he is creating new one with name that he wants to set. That's normal.

And yes, you are spamming. Stop.

[Walid]

So as far as I can see, you have seen low amount of things. If he doesn't want to rubish the internal.db database he is creating new one with name that he wants to set. That's normal.

And yes, you are spamming. Stop.

rubbish* , nvm

[spoty]

-- CHANGE IF NECCESSARY 
dataName  = { 
    ["last"] = "Last Drift", 
    ["total"] = "Total Drift", 
    ["best"] = "Best Drift" 
} 
-- type here what the elementData's names 
  
addEventHandler ( "onResourceStart", root, 
    function ( ) 
        -- creating connection and if true creates column if not exist 
        connection = dbConnect ( "sqlite" , "Drift_DB.db" ) 
        if ( connection ) then 
            dbQuery ( connection , "CREATE TABLE IF NOT EXISTS `drift` ( `data` TEXT, `account` TEXT )" ); 
        end 
        -- creating connection and if true creates column if not exist 
    end 
); 
  
function saveDriftPoints ( ) 
    local account = getPlayerAccount ( source ); -- getting account 
    if ( isGuestAccount ( account ) ) then -- checking if it is valid account 
        return; 
    end 
     
    local accName = getAccountName ( account ); -- getting name 
     
    local t = { } -- creating table for drift's data 
    for k, v in pairs ( dataName ) do 
        local data = getElementData ( source, v ); 
        if ( data ) then 
            t[v] = data; -- inserting values to table from ElementData 
        end 
    end 
     
    local check = dbPoll ( dbQuery ( connection, "SELECT `data` FROM `drift` WHERE `account`=?", accName ), -1 ); -- checking if exist row 
    if ( #check > 0 ) then 
        dbExec ( "UPDATE `drift` SET `data`=? WHERE `account`=?", toJSON ( t ), accName ); -- if yes updating 
    elseif ( #check == 0 ) then 
        dbExec ( "INSERT INTO `drift` ( `data`, `account` ) VALUES ( ?, ? )", toJSON ( t ), accName ); -- if not creating 
    end 
end 
addEventHandler ( "onPlayerQuit", root, saveDriftPoints ); 
  
function loadDriftPoints ( _, account ) 
    if ( isGuestAccount ( account ) ) then -- checking if it is valid account 
        return; 
    end 
     
    local accName = getAccountName ( account ); -- getting name  
     
    local check = dbPoll ( dbQuery ( connection, "SELECT `data` FROM `drift` WHERE `account`=?", accName ), -1 ); -- checking if row exist 
    if ( #check > 0 ) then -- if yes 
        local d = check[1]; 
         
        local t = fromJSON ( d["data"] ); 
        if ( type ( t ) == "table" ) then 
            for k, v in pairs ( t ) do 
                setElementData ( source, k, v ); -- setting ElementData from saved drift's table in table 
            end 
        end 
    end 
end 
addEventHandler ( "onPlayerLogin", root, loadDriftPoints ); 

This is s-side script. Didn't test. Change values in table on top and check.

Create 'Drift_DB.db' file and add also to meta.xml line

<file src="Drift_DB.db" /> 

ok cool i gonna test this just need to make new .db with this code right

[WhoAmI]

So as far as I can see, you have seen low amount of things. If he doesn't want to rubish the internal.db database he is creating new one with name that he wants to set. That's normal.

And yes, you are spamming. Stop.

rubbish* , nvm

Thank you, mr. master of English.

@spoty right. Create 'Drift_DB.db' and add it to meta.

[spoty]

hmm looks like it wont save / load i even dont see any error's