mySQL Not Saving

[Lloyd Logan]

server = dbConnect ( "mysql", "dbname=servermta;host=127.0.0.1","root")

dbExec(server, "CREATE TABLE IF NOT EXISTS accounts (serial TEXT NOT NULL, money INT NOT NULL)")

  
function submitReg() 
    firstserial = getPlayerSerial(source) 
    moneys = getPlayerMoney(source) 
    local data = dbPoll(dbQuery(server, "SELECT money FROM accounts WHERE serial = ?", firstserial), -1) 
    if data and type (data) == "table" and #data > 0 then 
    dbExec( server, "UPDATE `accounts` SET `money`=`"..moneys.."`"  ) 
    else 
    dbQuery ( server, "INSERT INTO accounts (serial, money) VALUES (?, ?)", tostring (theserial), tostring (themoney) ) 
    end 
end 
addEventHandler( "onPlayerQuit", root, submitReg ) 
     
     
exports.scoreboard:addScoreboardColumn("Money") 
  
function setMoney() 
    local Serial = getPlayerSerial(source) 
    local data = dbPoll(dbQuery(server, "SELECT money FROM accounts WHERE serial = ?", Serial), -1) 
    if data and type(data) == "table" then 
        setPlayerMoney(source, data[1]["money"]) 
        setElementData(source, "Money", data[1]["money"]) 
    else 
    outputChatBox("You have not previously logged in!", getRootElement(), 255, 0, 0) 
    end 
 end 
addEventHandler("onPlayerJoin", root, setMoney)Why does this save the serial as nil and money as 0? 
  

[Castillo]

What do you mean by "save the serial as nil"? the setMoney function is meant to load the money, so how can it "save the serial"?

[Lloyd Logan]

What do you mean by "save the serial as nil"? the setMoney function is meant to load the money, so how can it "save the serial"?

Sorry there was some blatant mistakes in that code!

I'm now able to save the money when the player quits, and load it when they join, so now I am stuck on how to update it if their info is already in the table!

function submitReg() 
    firstserial = getPlayerSerial(source) 
    moneys = getPlayerMoney(source) 
    local data = dbPoll(dbQuery(server, "SELECT money FROM accounts WHERE serial = ?", firstserial), -1) 
    if data and type (data) == "table" and #data > 0 then 
    dbExec( server, "UPDATE accounts SET money=`"..moneys.."` WHERE serial = '"..firstserial.."'"   ) 
    else 
    dbQuery ( server, "INSERT INTO accounts (serial, money) VALUES (?, ?)", tostring (firstserial), tostring (moneys) ) 
    end 
end 
addEventHandler( "onPlayerQuit", root, submitReg ) 

[Castillo]

function submitReg ( ) 
    local firstserial = getPlayerSerial ( source ) 
    local moneys = getPlayerMoney ( source ) 
    local data = dbPoll ( dbQuery ( server, "SELECT money FROM accounts WHERE serial = ?", firstserial ), - 1 ) 
    if ( type ( data)  == "table" and #data > 0 ) then 
        dbExec ( server, "UPDATE accounts SET money = ? WHERE serial = ?", moneys, firstserial ) 
    else 
        dbQuery ( server, "INSERT INTO accounts ( serial, money ) VALUES ( ?, ? )", tostring ( firstserial ), tostring ( moneys ) ) 
    end 
end 
addEventHandler ( "onPlayerQuit", root, submitReg ) 

[Lloyd Logan]

I'll try that!

Is

dbExec ( server, "UPDATE accounts SET money = ? WHERE serial = ?", moneys, firstserial ) 

All you changed?

[Castillo]

Yes, the method you were using wasn't safe.

[TAPL]

You should use dbExec for INSERT as you aren't selecting anything, therefore there will not be any return and you will not have to use dbFree nor dbPoll.

If you used dbQuery for INSERT, you will also need to use dbFree as this showed in wiki example.

I don't know what will happen if you didn't used dbFree, but i think the database could be locked or something.

[Castillo]

Oh yeah, I didn't notice he was using dbQuery to insert.