SQL problems

[Desaster]

hello I have tryed on a bank system but my problem is that every time the player quits a new row with his name get created

I want to create just 1 one and just update the ammount of money pls help me

Image:

server Side:

getPlayers = getElementsByType ( 'player' ) 
  
addEventHandler ( 'onResourceStart', resourceRoot, function ( ) 
    for _,p in next, getPlayers do 
        setElementData ( p, 'Balance', 0 ) 
    end 
    executeSQLQuery ( 'CREATE TABLE IF NOT EXISTS banking ( account, balance )' ) 
    startTBS ( ) 
end ) 
  
function startTBS ( ) 
    local TBSTable = executeSQLQuery ( 'SELECT * FROM banking' ) 
    if #TBSTable > 0 then 
        for i = 1, #TBSTable do 
            local TBS = { TBSTable[i]['account'], TBSTable[i]['balance'] } 
            for _,p in next, getPlayers do 
                local account = getAccountName(getPlayerAccount ( p )) 
                if account == tostring( TBS[1] ) then 
                    setElementData ( p, 'Balance', tonumber( TBS[2] ) ) 
                end 
            end 
        end 
    end 
  
addEventHandler ( 'onPlayerJoin', root, function ( ) 
    setElementData ( source, 'Balance', 0 ) 
    local account = getAccountName(getPlayerAccount ( source )) 
    local TBSTable = executeSQLQuery ( 'SELECT * FROM banking' ) 
    if #TBSTable > 0 then 
        for i = 1, #TBSTable do 
            local TBS = { TBSTable[i]['account'], TBSTable[i]['balance'] } 
            if account == tostring( TBS[1] ) then 
                setElementData ( source, 'Balance', tonumber( TBS[2] ) ) 
            else 
                return 
            end 
        end 
    end 
end ) 
  
addEvent ( 'onBalanceWithdraw', true ) 
addEventHandler ( 'onBalanceWithdraw', root, function ( amount ) 
    local balance = getElementData ( source, 'Balance' ) 
    givePlayerMoney ( source, amount ) 
    setElementData ( source, 'Balance', balance - amount ) 
end ) 
  
addEvent ( 'onBalanceDeposit', true ) 
addEventHandler ( 'onBalanceDeposit', root, function ( amount ) 
    local balance = getElementData ( source, 'Balance' ) 
    takePlayerMoney ( source, amount ) 
    setElementData ( source, 'Balance', balance + amount ) 
end ) 
  
addEventHandler ( 'onPlayerQuit', root, function ( ) 
    local account = getAccountName(getPlayerAccount ( source )) 
    local balance = getElementData ( source, 'Balance' ) 
    executeSQLQuery ( 'INSERT INTO banking ( account, balance ) VALUES ( ?, ? )', account, balance ) 
end ) 

[pa3ck]

See the bottom of the picture you just posted? Thats what you need. The 'UPDATE' thing. First you will need to check his name in the table, if the name does not exist then create a new row, else update your row.

[CourtezBoi]

This is what you have:

  
addEventHandler ( 'onPlayerQuit', root, function ( ) 
    local account = getAccountName(getPlayerAccount ( source )) 
    local balance = getElementData ( source, 'Balance' ) 
    executeSQLQuery ( 'INSERT INTO banking ( account, balance ) VALUES ( ?, ? )', account, balance ) 
end ) 
  

This is what you want:

  
addEventHandler ( 'onPlayerQuit', root, function ( ) 
    local account = getAccountName(getPlayerAccount ( source )) 
    local balance = getElementData ( source, 'Balance' ) 
    executeSQLQuery ( "UPDATE banking SET balance = '?' WHERE account = '?'", account, balance ) 
end ) 
  

[Desaster]

how to check if the row exist ?

[CourtezBoi]

  
addEventHandler ( 'onPlayerQuit', root, function ( ) 
    local account = getAccountName(getPlayerAccount ( source )) 
    local balance = getElementData ( source, 'Balance' ) 
    if executeSQLQuery( "SELECT balance FROM banking WHERE account = ?", account ) then 
        executeSQLQuery ( "UPDATE banking SET balance = '?' WHERE account = '?'", account, balance ) 
    else 
        executeSQLQuery( "INSERT INTO banking (account, balance) VALUES ('?', ?)", account, balance ) 
    end 
end ) 
  

Haven't tested this but it should work for you.

[Desaster]

ah and what is INTENGER PRIMERY KEY I know it's a type to give each row a number but how to use it and what is it useful ?

[CourtezBoi]

Integer basically means a whole number, eg 1, 3, 12, etc. Primary key means that the rows are indexed and sorted by that item. So when you fetch the rows, they will be in order by your primary key.

Typically the primary key is an automatically incrementing integer, so when you create a new row it sets the item automatically to the next number after the previously added entry.

For your banking table, you only have account and balance, so your primary key would be account, though it wouldn't automatically be set, because you set it to link to the account ID.

So you might do account REFERENCES accounts.id; or something along those lines to link between banking.account and your account id. This isn't necessary but it enforces referential integrity so you won't have any bank accounts that aren't linked to a valid account.

[Desaster]

that seems logic thnx

but idk why the row won't be created when the player presses a deposit button for the first time

can you help me the code that gave me Courtez ( the update work but when I try an other acc it won't work ) I tryed this but it won't work too check it may you find the error

addEvent ( 'onBalanceDeposit', true ) 
addEventHandler ( 'onBalanceDeposit', root, function ( amount ) 
    local balance = getElementData ( source, 'Balance' ) 
    local account = getAccountName(getPlayerAccount ( source )) 
    takePlayerMoney ( source, amount ) 
    setElementData ( source, 'Balance', balance + amount ) 
    if executeSQLQuery( "SELECT balance FROM banking WHERE account = ?", account ) then 
        executeSQLQuery ( "UPDATE banking SET balance = ? WHERE account = ?", balance + amount, account ) 
    else 
        executeSQLQuery("INSERT INTO banking SET account = ?", account) 
        exports["TopBarChat"]:sendClientMessage ( "Seems you didn't had a back account, we created one for you!",source,255,0,0,TopBar, 5 ) 
    end 
end ) 

[CourtezBoi]

If you enable /debugscript 3 does it show any errors?

Replace:

if executeSQLQuery( "SELECT balance FROM banking WHERE account = ?", account ) then 

with

if #executeSQLQuery( "SELECT balance FROM banking WHERE account = ?", account ) > 0 then 

Might work, I don't often work with SQLite.

[Desaster]

hmmm so this what I made but won't work again ... the problem is that it can't find the row I think pls check it and tell me what's the problem

  
addEventHandler ( 'onPlayerLogin', root, function ( ) 
    --[[setElementData ( source, 'Balance', 0 ) 
    local account = getAccountName(getPlayerAccount ( source )) 
    local TBSTable = executeSQLQuery ( 'SELECT * FROM banking' ) 
    if #TBSTable > 0 then 
        for i = 1, #TBSTable do 
            local TBS = { TBSTable[i]['account'], TBSTable[i]['balance'] } 
            if account == tostring( TBS[1] ) then 
                setElementData ( source, 'Balance', tonumber( TBS[2] ) ) 
            else 
                return 
            end 
        end 
    end]] 
    local account = getAccountName(getPlayerAccount ( source )) 
    local balanceCheck = executeSQLQuery( "SELECT balance FROM banking WHERE account = ?", account ) 
    setElementData ( source, 'Balance', tonumber( balanceCheck ) ) 
end ) 

ps : that thing between --[[ ]] won't work because idk why it work just with 1 acc if you create more it won't work then