Some help with MySQL functions

[dragonofdark]

Hello,

I would like to make a vehicle's upgrades saver with mySQL. Each vehicle created in the server is stocked in a database with an ID for each. In each vehicle line, I have X ref, Y, ref, Z ref, engine state,... I've added the case which will contain all the vehicle upgrades, if it has. But I don't know what I can put in this case to load the upgrades IG. In mind that I must have more than one upgrade in it.

My upgrades case :

I tested [{"1158","1160"}] as the vehicle is a Jester but it doesn't work.

I'm based on an other system of weapons wich had [{"30":89,"43":82,"24":90}] in his case. (By exemple 30 for weapon ID and 89 for ammo)

Also, what can I put in my script to load them ?

The weapon system :

  
                    if char.weapons then 
                        local weapons = fromJSON( char.weapons ) 
                        if weapons then 
                            for weapon, ammo in pairs( weapons ) do 
                                giveWeapon( source, weapon, ammo ) 
                            end 
                        end 
                    end 

The upgrades system :

  
                if data.upgrades then 
                    local upgrades = fromJSON( data.upgrades ) 
                    if upgrades then 
                        for upgrade in pairs( upgrades ) do 
                            addVehicleUpgrade( vehicle, upgrade ) 
                        end 
                    end 
                end 

I think the problem is the "fromJSON" thing and the [{"XX","XX",}]. I had some things to set up in my script and I can't find what can be the problem other than that.

Thanks

[NeXTreme]

I would store the vehicle upgrades in mySQL database in a string, like this

 upgradeString = "1158,1160"  

For creating the string from a table of upgrades, use table.concat

vehicleUpgrades = {1158,1160} 
upgradeString = table.concat(vehicleUpgrades,",")  

And to turn it back into a table

upgradeString = "1158,1160" 
vehicleUpgrades = {} 
vehicleUpgrades = explode(",",upgradeString) 
  
function explode(delimiter, text) 
    local list = {}; local pos = 1 
    if string.find("", delimiter, 1) then 
        outputDebugString("Delimiter matches empty string!") 
    end 
    while 1 do 
        local first, last = string.find(text, delimiter, pos) 
        if first then 
            table.insert(list, string.sub(text, pos, first-1)) 
            pos = last+1 
        else 
            table.insert(list, string.sub(text, pos)) 
            break 
        end 
    end 
    return list 
end 

[dragonofdark]

I can't understand when you want to turn it back into a table =/

[NeXTreme]

Well you weren't completely clear about what you want, imo... anyways... If you want to store vehicle upgrades into the mySQL database, you can convert a table of the current vehicle upgrades the vehicle has into a string and store that. Then, when you want to retrieve those upgrades and load them into a table again so you can add them to the vehicle, use the code I also posted.

                 if data.upgrades then 
                     -- Assuming your data.upgrades is a string like "1158,1160" 
                    local upgrades = explode(",",data.upgrades) 
                    if upgrades then 
                        for upgrade in pairs( upgrades ) do 
                            addVehicleUpgrade( vehicle, upgrade ) 
                        end 
                    end 
                end 
  
function explode(delimiter, text) 
    local list = {}; local pos = 1 
    if string.find("", delimiter, 1) then 
        outputDebugString("Delimiter matches empty string!") 
    end 
    while 1 do 
        local first, last = string.find(text, delimiter, pos) 
        if first then 
            table.insert(list, string.sub(text, pos, first-1)) 
            pos = last+1 
        else 
            table.insert(list, string.sub(text, pos)) 
            break 
        end 
    end 
    return list 
end 
  

[dragonofdark]

It won't work anyway. In mySQL I've tested with "1158,1160" and 1158,1160.

Do you want for I send you the script by PM to see yourself where is the problem ?

[NeXTreme]

Your problem might be in the mySQL database. Make sure the type of the upgrades column is TEXT. You should be fine by storing strings like "1158,1160" in a TEXT field, I've done it myself.

[dragonofdark]

Doesn't work again. Tested with "1158,1160" and 1158,1160.

EDIT : I have tried with the vehiclesaver system and it works now.

[dragonofdark]

Hello,

I restart the topic because I've 2 news problems related to MySQL and upgrades.

1°)

My server loads upgrades from the SQL database and put the upgrades on the vehicles, but if there is only ONE upgrade, it wont load it and stops the script. (In my database I have ie: 1074,1139 if I want it loads correctly and 1074 if I want it fails to load)

The script :

local upgrades = split( data.upgrades, 44 ) 
for index, upgrade in pairs (upgrades) do 
addVehicleUpgrade( vehicle, upgrade ) 
end 

The error :

[2011-08-31 16:34:23] WARNING: vehicles\vehicles.lua:137: Bad argument @ 'split' 
[2011-08-31 16:34:23] ERROR: vehicles\vehicles.lua:138: bad argument #1 to 'pairs' (table expected, got boolean) 

So.. How can I make it to load the upgrade if it has only one upgrade ?

2°)

I want for the script to save all upgrades the vehicle has into my SQL table. How can I make that ?

The string in the table should be just a number if I have only one upgrade and some numbers if I have some.

Ex:

One upgrade (Mega wheel on Sultan) => 1074 (without any quote)

2 upgrades (Mega Wheel and X-flow spoiler on sultan)=> 1074,1139 (without any quote but just a "," behind)

3 upgrades (Mega Rim, X-flow spoiler and rear X-flow on sultan)=> 1074,1139,1140 (without any quote but just a "," behind each.

....

...

Thanks a lot !

(I'm really sorry if my english sucks )

[SDK]

1°) I'm not sure how the data is received, but it might be because data.upgrades is a number when only one upgrade is stored. To make sure it's a string, use tostring():

local upgrades = split( tostring(data.upgrades), 44 ) 

2°) Not that hard, you can use table.concat to convert a table into a string.

local upgradesTable = getVehicleUpgrades(theVehicle) 
-- table will look like this: 
-- upgrades = { [1] = 1074, 
--              [2] = 1139, 
--              [3] = 1110, 
--              ...} 
local upgradesString = table.concat(upgradesTable, ",") 
-- upgradesString = "1074,1139,1140,..." 

[dragonofdark]

Ah ! It works for my first problem

For the second, I did :

function saveUpgrades( vehicle ) 
    if vehicle then 
        local data = vehicles[ vehicle ] 
        if data and data.vehicleID > 0 then 
            local tableUpgrades = getVehicleUpgrades(vehicle) 
            local upgrades1 = table.concat(tableUpgrades, ",") 
            local paintjob1 = getVehiclePaintjob(vehicle) 
            local success, error = exports.sql:query_free( "UPDATE vehicles SET upgrades = " .. upgrades1 .. ", paintjob = " .. paintjob1 .. " WHERE vehicleID = " .. data.vehicleID ) 
            if error then 
                outputDebugString( error ) 
            end 
        end 
    end 
end 

But I have this error :

[2011-08-31 17:24:18] INFO: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1074, paintjob = 1 WHERE vehicleID = 8' at line 1 

The paintjob insn't a problem, it load to the server correctly, and export to SQL correctly too.

[SDK]

Add quotation marks?

local success, error = exports.sql:query_free( "UPDATE vehicles SET upgrades = '" .. upgrades1 .. "', paintjob = " .. paintjob1 .. " WHERE vehicleID = " .. data.vehicleID ) 

[dragonofdark]

Thanks, works perfectly