SQL help :/

AstroBurn · March 12, 2013

hello,

i have never used database saving or sql, and i am stuck with this code, it's creating like 20 time my account, and it's not rly working could anyone help me ?

function loadPlayerData (player,datatype) 
sourceAccount = getPlayerAccount ( player ) 
if isGuestAccount ( sourceAccount ) then 
return 0 
else 
  local playerIP = getAccountName (getPlayerAccount(player)) 
  if (playerIP) then 
        local playerRootNode = executeSQLQuery("SELECT accountName FROM userpanel WHERE accountName='"..playerIP.."'") 
        if ( type( playerRootNode ) == "table") then 
          local playerRootNode = executeSQLQuery("INSERT INTO userpanel (accountName) VALUES ('"..playerIP.."')") 
          return 0 
        else 
          local playerData = executeSQLQuery("SELECT "..datatype.." FROM userpanel WHERE accountName='"..playerIP.."'") 
          if (playerData) then 
          outputChatBox('bruh') 
            return playerData 
          else 
            executeSQLQuery("INSERT INTO userpanel ("..datatype..") VALUES ('"..datatype.."') WHERE accountName='"..playerIP.."'") 
            return 0 
          end 
        end 
  end 
end 
end 
  
function savePlayerData (player,datatype,newvalue) 
sourceAccount = getPlayerAccount ( player ) 
if isGuestAccount ( sourceAccount ) then 
return 0 
else 
  local playerIP = getAccountName (getPlayerAccount(player)) 
  if (playerIP) then 
        local playerRootNode = executeSQLQuery("SELECT accountName FROM userpanel WHERE accountName='"..playerIP.."'") 
        if not (playerRootNode == false) then 
          local newNodeValue = executeSQLQuery("UPDATE userpanel SET "..tostring(datatype).."='"..tostring(newvalue).."' WHERE accountName='"..playerIP.."'") 
          return newNodeValue 
        else 
          local playerRootNode = executeSQLQuery("INSERT INTO userpanel (accountName) VALUES ('"..playerIP.."')") 
          local newNodeValue = executeSQLQuery("INSERT INTO userpanel ("..tostring(datatype)..") VALUES ('"..tostring(datatype).."') WHERE accountName='"..playerIP.."'") 
          return newNodeValue 
        end 
      end 
end 
end

Jaysds1 · March 12, 2013

I don't get this script???

What is 'datatype' suppose to be???

AstroBurn · March 12, 2013

Datatype is the data info to be stored/loaded

like savePlayerData(thePlayer,"cash",50000)

"cash" is the datatype

Jaysds1 · March 12, 2013

ok, got it, try this:

function loadPlayerData (player,datatype) 
     datatype = tostring(datatype) 
     local sourceAccount = getPlayerAccount (player) 
     if isGuestAccount ( sourceAccount ) then return 0 end 
  
     local playerIP = getAccountName (sourceAccount) 
     if not playerIP then return 0 end 
  
     local playerRootNode = executeSQLQuery("SELECT accountName FROM userpanel WHERE accountName='"..playerIP.."'") 
     if not playerRootNode or playerRootNode[0]=="" then 
          //local playerRootNode = executeSQLQuery("INSERT INTO userpanel (accountName) VALUES ('"..playerIP.."')") 
          return 0 
     else 
          local playerData = executeSQLQuery("SELECT "..datatype.." FROM userpanel WHERE accountName='"..playerIP.."'") 
          if (playerData) then 
               outputChatBox('bruh') 
               return playerData 
          else 
               executeSQLQuery("INSERT INTO userpanel ("..datatype..") VALUES ('"..datatype.."') WHERE accountName='"..playerIP.."'") 
               return 0 
          end 
     end 
end 
  
function savePlayerData (player,datatype,newvalue) 
     datatype,newvalue = tostring(datatype),tostring(newvalue) 
  
     local sourceAccount = getPlayerAccount ( player ) 
     if isGuestAccount ( sourceAccount ) then return 0 end 
  
     local playerIP = getAccountName (sourceAccount) 
     if not playerIP then return 0 end 
  
     local playerRootNode = executeSQLQuery("SELECT accountName FROM userpanel WHERE accountName='"..playerIP.."'") 
     if playerRootNode then 
          local newNodeValue = executeSQLQuery("UPDATE userpanel SET "..datatype.."='"..newvalue.."' WHERE accountName='"..playerIP.."'") 
          return newNodeValue 
     else 
          local playerRootNode = executeSQLQuery("INSERT INTO userpanel (accountName) VALUES ('"..playerIP.."')") 
          local newNodeValue = executeSQLQuery("INSERT INTO userpanel ("..datatype..") VALUES ('"..datatype.."') WHERE accountName='"..playerIP.."'") 
          return newNodeValue 
     end 
end

Edited March 12, 2013 by Guest

AstroBurn · March 12, 2013

i tried, but it's still making 219 records with the same accountName ...

Castillo · March 12, 2013

function loadPlayerData ( player, datatype ) 
    local sourceAccount = getPlayerAccount ( player ) 
    if isGuestAccount ( sourceAccount ) then 
        return false 
    else 
        local accountName = getAccountName ( sourceAccount ) 
        if ( accountName ) then 
            local playerRootNode = executeSQLQuery ( "SELECT * FROM userpanel WHERE accountName = ?", tostring ( accountName ) ) 
            if ( type ( playerRootNode ) == "table" and #playerRootNode == 0 or not playerRootNode ) then 
                return executeSQLQuery ( "INSERT INTO userpanel ( accountName, ".. tostring ( datatype ) .." ) VALUES ( ?, ? )", tostring ( accountName ), newvalue ) 
            else 
                return playerRootNode [ 1 ] [ datatype ] 
            end 
        end 
    end 
end 
  
function savePlayerData ( player, datatype, newvalue ) 
    local sourceAccount = getPlayerAccount ( player ) 
    if isGuestAccount ( sourceAccount ) then 
        return false 
    else 
        local accountName = getAccountName ( sourceAccount ) 
        if ( accountName ) then 
            local playerRootNode = executeSQLQuery ( "SELECT accountName FROM userpanel WHERE accountName = ?", tostring ( accountName ) ) 
            if ( type ( playerRootNode ) == "table" and #playerRootNode == 0 or not playerRootNode ) then 
                return executeSQLQuery ( "INSERT INTO userpanel ( accountName, ".. tostring ( datatype ) .." ) VALUES ( ?, ? )", tostring ( accountName ), newvalue ) 
            else 
                return executeSQLQuery ( "UPDATE userpanel SET ".. tostring ( datatype ) .." = ? WHERE accountName = ?", newvalue, tostring ( accountName ) ) 
            end 
        end 
    end 
end

Try it.

AstroBurn · March 12, 2013

tried, and in debugscript it's saying no such column (myaccountname)

so with your code it's not adding new account

but i appreciate your help

Castillo · March 12, 2013

Post your function to create the table.

AstroBurn · March 12, 2013

addEventHandler("onResourceStart", getResourceRootElement(getThisResource()), 
    function() 
        executeSQLCreateTable("userpanel", tostring(sqliteData)) 
    end)

and the table is fucking long x)

Castillo · March 12, 2013

Mind posting it?

Edit: I just tested it with a table with 2 columns and it works, no errors, no duplicates.

AstroBurn · March 12, 2013

here it is

local sqliteData = "accountName STRING, cash INT, mapsPlayed INT, mapsWon INT, everPurchasedNametagColor INT, level INT, unlockedAchievements INT, totalTimesJoined INT, totalHunters INT, totalToptimes INT, totalMoneyEarned INT, totalMoneySent INT, totalBets INT, totalBetsEarned INT, totalBetsWon INT, totalDeaths INT, totalPlayingTimeMinutes INT, totalPlayingTimeHours INT, totalReactionTests INT, bestReactionTime INT, ach1 INT, ach2 INT, ach3 INT, ach4 INT, ach5 INT, ach6 INT, ach7 INT, ach8 INT, ach9 INT, ach10 INT, ach11 INT, ach12 INT, ach13 INT, ach14 INT, ach15 INT, ach16 INT, ach17 INT, ach18 INT, ach19 INT, ach20 INT, ach21 INT, ach22 INT, ach23 INT, ach24 INT, ach25 INT, ach26 INT, ach27 INT, ach28 INT, ach29 INT, ach30 INT, ach31 INT, ach32 INT, ach33 INT, ach34 INT, ach35 INT, ach36 INT, ach37 INT, ach38 INT, ach39 INT, ach40 INT, everPurchasedSkin INT, everSetCustomVehicleColor INT, useCustomVehicleColor STRING, c1 INT, c2 INT, c3 INT, c4 INT, hlcRed INT, hlcBlue INT, hlcGreen INT, admin_redo INT, admin_nextmap INT, allowPersonalDetails STRING, allowStats STRING, useCustomNametag STRING, useCustomSkin STRING, soundMessage STRING, soundRequest STRING, showOnDeath STRING, useHunterSkin STRING, chatAcceptMode INT, joinedBefore INT, horn1 INT, horn2 INT, horn3 INT, horn4 INT, horn5 INT, horn6 INT, horn7 INT, lastSetHorn INT, pName STRING, pAge STRING, pEmail STRING, pMsn STIRNG, pSkype STRING, pXfire STRING, pICQ STRING, pXBL STRING, convertedTotal INT"

Castillo · March 12, 2013

Read my latest post, I edited it.

I used this code to test it:

local sqliteData = "accountName STRING, cash INT, mapsPlayed INT, mapsWon INT, everPurchasedNametagColor INT, level INT, unlockedAchievements INT, totalTimesJoined INT, totalHunters INT, totalToptimes INT, totalMoneyEarned INT, totalMoneySent INT, totalBets INT, totalBetsEarned INT, totalBetsWon INT, totalDeaths INT, totalPlayingTimeMinutes INT, totalPlayingTimeHours INT, totalReactionTests INT, bestReactionTime INT, ach1 INT, ach2 INT, ach3 INT, ach4 INT, ach5 INT, ach6 INT, ach7 INT, ach8 INT, ach9 INT, ach10 INT, ach11 INT, ach12 INT, ach13 INT, ach14 INT, ach15 INT, ach16 INT, ach17 INT, ach18 INT, ach19 INT, ach20 INT, ach21 INT, ach22 INT, ach23 INT, ach24 INT, ach25 INT, ach26 INT, ach27 INT, ach28 INT, ach29 INT, ach30 INT, ach31 INT, ach32 INT, ach33 INT, ach34 INT, ach35 INT, ach36 INT, ach37 INT, ach38 INT, ach39 INT, ach40 INT, everPurchasedSkin INT, everSetCustomVehicleColor INT, useCustomVehicleColor STRING, c1 INT, c2 INT, c3 INT, c4 INT, hlcRed INT, hlcBlue INT, hlcGreen INT, admin_redo INT, admin_nextmap INT, allowPersonalDetails STRING, allowStats STRING, useCustomNametag STRING, useCustomSkin STRING, soundMessage STRING, soundRequest STRING, showOnDeath STRING, useHunterSkin STRING, chatAcceptMode INT, joinedBefore INT, horn1 INT, horn2 INT, horn3 INT, horn4 INT, horn5 INT, horn6 INT, horn7 INT, lastSetHorn INT, pName STRING, pAge STRING, pEmail STRING, pMsn STIRNG, pSkype STRING, pXfire STRING, pICQ STRING, pXBL STRING, convertedTotal INT" 
  
addEventHandler ( "onResourceStart", getResourceRootElement(getThisResource()), 
    function ( ) 
        executeSQLCreateTable ( "userpanel", tostring ( sqliteData ) ) 
    end 
) 
  
function loadPlayerData ( player, datatype ) 
    local sourceAccount = getPlayerAccount ( player ) 
    if isGuestAccount ( sourceAccount ) then 
        return false 
    else 
        local accountName = getAccountName ( sourceAccount ) 
        if ( accountName ) then 
            local playerRootNode = executeSQLQuery ( "SELECT * FROM userpanel WHERE accountName = ?", tostring ( accountName ) ) 
            if ( type ( playerRootNode ) == "table" and #playerRootNode == 0 or not playerRootNode ) then 
                return executeSQLQuery ( "INSERT INTO userpanel ( accountName, ".. tostring ( datatype ) .." ) VALUES ( ?, ? )", tostring ( accountName ), newvalue ) 
            else 
                return playerRootNode [ 1 ] [ datatype ] 
            end 
        end 
    end 
end 
  
function savePlayerData ( player, datatype, newvalue ) 
    local sourceAccount = getPlayerAccount ( player ) 
    if isGuestAccount ( sourceAccount ) then 
        return false 
    else 
        local accountName = getAccountName ( sourceAccount ) 
        if ( accountName ) then 
            local playerRootNode = executeSQLQuery ( "SELECT accountName FROM userpanel WHERE accountName = ?", tostring ( accountName ) ) 
            if ( type ( playerRootNode ) == "table" and #playerRootNode == 0 or not playerRootNode ) then 
                return executeSQLQuery ( "INSERT INTO userpanel ( accountName, ".. tostring ( datatype ) .." ) VALUES ( ?, ? )", tostring ( accountName ), newvalue ) 
            else 
                return executeSQLQuery ( "UPDATE userpanel SET ".. tostring ( datatype ) .." = ? WHERE accountName = ?", newvalue, tostring ( accountName ) ) 
            end 
        end 
    end 
end 
  
addCommandHandler ( "save", 
    function ( thePlayer, _, data, value ) 
        savePlayerData ( thePlayer, data, value ) 
    end 
) 
  
addCommandHandler ( "load", 
    function ( thePlayer, _, data ) 
        local value = loadPlayerData ( thePlayer, data ) 
        outputChatBox ( tostring ( value ) ) 
    end 
)

"/save cash 1000"
"/load cash".

AstroBurn · March 12, 2013

Well im sorry to bother you even more, but it's not adding a new account if the account doesn't exist in the userpanel database so i can't rly test it

Castillo · March 12, 2013

Works perfect here.

AstroBurn · March 12, 2013

Ah yeah it's working now, just some shit from me ^^

thx a lot for helping me.

Castillo · March 12, 2013

You're welcome.

AstroBurn · March 13, 2013

sorry to up this thread again,

your script is working well, but i can't make it works within the script itself, it just doesn't create a new accountName, or even if i make it byusing your command function, it doesn't load/save on it. I am using this for exemple:

savePlayerData(source,"level",1)

it should set the player's level to one, and create a column if there is none, but it don't do it

Castillo · March 13, 2013

Columns get created when you create the table, and if it worked on my script, it also has to work on yours.

Brunoo · March 14, 2013

i wonder how to remove tables

Castillo · March 14, 2013

executeSQLDropTable ( "userpanel" )

SQL help :/

Recommended Posts

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members