Help with database save script

[Ernoso]

Hi, I need some help here... well..

I simple script that saves what a col has and how many it has.

local tentItensTable = {
{"Assault Rifle"},
{"Motorcycle"},
}

for i, data in ipairs(tentItensTable) do 
	itens = getElementData(thecol,data[1])
end

--[[ saving line --]]
dbExec(thedatabase, "INSERT INTO bau_obj VALUES (?,?,?,?,?,?,?,?,?)", numeros, modelo, x, y, z, rx, ry, rz, itens)
--[[ saving line --]]

The "itens" will save how many items the col has that is inside the "tentItensTable", but it's not saving! it's saving everything, but it's not saving the items... Can you guys help me?

Like: 

Item: Assault Rifle / Amount: 20

Edited December 12, 2016 by Ernoso

[Lpsd]

5 hours ago, Ernoso said:

• local tentItensTable = {
• {"Assault Rifle"},
• {"Motorcycle"},
• }
•  
• for i=1, data in ipairs(tentItensTable) do
• itens = data

end   --[[ saving line --]] dbExec(thedatabase, "INSERT INTO bau_obj (numeros, modelo, x, y, z, rx, ry, rz, itens)  VALUES (?,?,?,?,?,?,?,?,?)" ) --[[ saving line --]]

This might work, but I'm not entirely sure how Lua handles for loops.

Also note the SQL syntax change m, you'll need to feed it the right values where you've got (?,?,?,?,?) 

 Edit: sorry the code quote messed up. I'm on mobile with 1% battery ?

Edited December 12, 2016 by LopSided_

[Lpsd]

local tentItensTable = {
{"Assault Rifle"},
{"Motorcycle"},
}

for i, data in ipairs(tentItensTable) do
itens = getElementData(thecol,data[i])
end   

--[[ saving line --]] 
dbExec(thedatabase, "INSERT INTO bau_obj (col1, col2, col3, col4, col5, col6, col7, col8, col9)  VALUES (?,?,?,?,?,?,?,?,?)", numeros, modelo, x, y, z, rx, ry, rz, itens) 
--[[ saving line --]]

Looks like I'm gonna have to doublepost since I can't edit my original reply.

This might work, but I'm not entirely sure how Lua handles for loops.

Also note the SQL query change, first you tell it which rows the VALUES are going into. So change 'col1', 'col2' etc with the corresponding rows in your database table.

Edited December 12, 2016 by LopSided_

[tosfera]

The script makes like.. seriously no sense. You're overriding the data in 'itens' before you're even saving it. Why would you ever make it like that..? It's quite simple. The fact that it's not saving is because you're not even taking the effort to see if you got an error from your query. You're simply firing the query and hoping it's going to work.

First of all, 'tentItensTable' holds data from a collision. 'thecol' hasn't been defined inside this snippet so I think it's declared somewhere else in the document. If not, then you're basically passing invalid data to your database which would crash right away, check your data, debugscript and also use DbPoll to see if you get a message about the actual warning.

[Lpsd]

Put your dbExec inside the for loop

[Citizen]

@LopSided_ You are providing incomplete chunks of reply which probably makes the original poster confused if he can't follow your logic (I understood what you were trying to do but it's incorrect as there should be one (and only one) line per row (1 row = 1 tent; but each tent can have 0 or more "itens" (items I guess, might be a typo)).

For an easy solution, build key-value based array where the key will be the "itens" name and the value will be the quantity of that itens you get with the getElementData. Once the array is built, use the toJSON function to convert that array into a string.
 

local tentItensTable = {
{"Assault Rifle"},
{"Motorcycle"},
}

local itens = {}
for i, data in ipairs(tentItensTable) do
	local itenName = data[1] -- the 'iten' name
	local itenQuantity = getElementData(thecol, itenName) or 0 -- the 'iten' quantity (0 if 'false')
	table.insert(itens, { name = itenName, quantity = itenQuantity }) -- adding the 'iten' datas in the list
end
--[[
Here 'itens' is like a table with 2 columns that should look like this (for example):
{
	{ name = "Assault Riffle", quantity = 63 },
	{ name = "Motorcycle", quantity = 0 }
}
 
But now as you can't save complex types (like arrays and tables) directly,
you have to convert it into a simple string using the toJSON function:
--]]
local itensJson = toJSON(itens)

--[[ saving line --]]
dbExec(thedatabase, "INSERT INTO bau_obj VALUES (?,?,?,?,?,?,?,?,?)", numeros, modelo, x, y, z, rx, ry, rz, itensJson)
--[[ saving line --]]

 

And to load the itens from database do it like this:

---- Loading the itens back somewhere else ----
local itensJson = REPLACE_ME -- Do your thing to get the 'itens' column value as string from the database in that variable
-- Then convert the Json string back into our table of itens like before the save using fromJSON function:
local itens = fromJSON(itensJson)
-- Loop to read all itens in that tent
for i, data in ipairs(itens) do
	local itenName = data["name"] -- the 'iten' name
	local itenQuantity = data["quantity"] -- the 'iten' quantity (0 if nil/false)
	setElementData(thecol, itenName, itenQuantity)
end

 

But for a clean/not dirty solution as above, you should use another table to only save the 'itens' with an extra column like 'bau_obj_numeros' that will store the value of the bau_obj it is related to:

bau_obj table:

numeros | modelo | x | y | z | rx | ry | rz
   1    |  123   | 0 | 0 | 0 | 0  | 0  | 0    <-- tent 1
   2    |  123   | 9 | 9 | 0 | 0  | 0  | 0    <-- tent 2

bau_obj_itens table:

bau_obj_numeros |      name       | quantity
       1        |  Assault Riffle |    54      <-- tent 1
       1        |    Motorcycle   |    1       <-- tent 1
       2        |  Assault Riffle |    180     <-- tent 2
       2        |    Motorcycle   |    0       <-- tent 2

Here tent 1 has:
  - 54 ammos of Assault Riffle
  - 1 Motorcycle
    
And tent 2 has:
  - 180 ammos of Assault Riffle
  - 0 Motorcycle

See one-to-many relationship.
I hope it makes sense for you. It will require more modifications of your script but that's a standard thing for storing a list for a given row in a database.
Remember this: If a column needs to store a list of another thing, then create another table for that other thing.

Best regards,
Citizen
 

Edited December 15, 2016 by Citizen
French noob ;)

[Lpsd]

@Citizen my bad. I also just read the author's post again and realized I hadn't understood he wanted 

[Ernoso]

Thank you all guys! it's working now, sorry if i'm kinda late.