MySQL Query result string to table

[DrAceMisanthrope]

@Mobius01

Tables are stored as json strings in a DB so use:

json.decode(columnData)

That response hit the nail on the head. They are correct.

To go into more detail, when you have a Lua table and you want to save it to the database, you save it in a TEXT or LONGTEXT column of a DB table, after converting/encoding it to a string. When fetching the data, it comes out as a string and you convert/decode it to a table.

Lua → Database:

json.encode(luaTable) -- Converts {x=1,y=2,z=3} into '{"x":1,"y":2,"z":3}'

SQL → Lua:

json.decode(dbString) -- Converts '{"x":1,"y":2,"z":3}' into {x=1,y=2,z=3}

---

@ossiedk

So I would be able to do someting like this?

local myTable = json.decode(columnData)
print(myTable[1]) -- prints water
print(myTable[2]) -- prints 6

Probably not. It depends what the SQL query is. Here is an example of a very typical query for you.

Let’s assume we have a table called items in the database like this;
name | data
water | ‘{“label”:“Water”,“carry_limit”:6}’
burger | ‘{“label”:“Hamburger”,“carry_limit”:6}’
etc.

local result = MySQL.Async.fetchAll("SELECT * from items WHERE name = @name", {
	["@name"] = "water"
})

Since “fetchAll” will always return a table, even if it’s empty, result will look like this…

result = {
	{
		name = "water",
		data = '{"label":"Water","carry_limit":6}'
	}
}

We want it to look like this…

result = {
	{
		name = "water",
		data = {
			label = "Water",
			carry_limit = 6
		}
	}
}

So we can do the following to read it like table…

if result[1] ~= nil then
	local waterData = json.decode(result[1].data)
	print(result[1].name) -- water
	print(waterData.label) -- Water
	print(waterData.carry_limit) -- 6
end

OR convert it to a table…

if result[1] ~= nil then
	result[1].data = json.decode(result[1].data)
	print(result[1].name) -- water
	print(result[1].data.label) -- Water
	print(result[1].data.carry_limit) -- 6
end

And if we wanted to scrap the data and just have it look like this…

result = {
	{
		name = "water",
		label = "Water",
		carry_limit = 6
	}
}

We can do the following…

if result[1] ~= nil then
	result[1].data = json.decode(result[1].data)
	result[1].label = result[1].data.label
	result[1].carry_limit = result[1].data.carry_limit
	result[1].data = nil
	print(result[1].name) -- water
	print(result[1].label) -- Water
	print(result[1].carry_limit) -- 6
end

Not sure why you would bother… But you can!