[MySQL] Inserting into a Column - Making a list.

[MrGregsWorld]

Hi,
I'm still new to MySQL and have encountered a problem.
I wanted to know if it was possible to insert information to a column; but not replacing it. I would want it to continue in a list, rather than deleting the column and replacing it with the newest one. { Eggs, Potatoes, Cheese }, etc.

[EvacX]

Can you clarify better? I'm not entirely sure what you want.

[MrGregsWorld]

[IMG]http://i.imgur.com/fhRF4QS.png[/IMG]

For the Inventory Column, I need the items added in to go to the end or beggining of the list.
i.e.( {"1":"freb4", "NewItem", "NewItem2", etc.}

I've tried Update:
[lua]
UPDATE users SET inventory = '"..Item.."' WHERE steamid = '"..SteamID.."'
[/lua]
But it only replaces the contents, not adding to it.

[EvacX]

Do [I]not[/I] store encoded arrays/tables in a single column. It's very bad SQL practice. Just have a different table with a foreign key to replace it. That way, you can have an auto increment column too.

[MrGregsWorld]

[QUOTE=EvacX;41446204]Do [I]not[/I] store encoded arrays/tables in a single column. It's very bad SQL practice. Just have a different table with a foreign key to replace it. That way, you can have an auto increment column too.[/QUOTE]

It actually shouldn't be encoded like that, it was just from a test I had forgotten to delete off.

I'll move the Inventory to a Seperate Table, but when doing so I get the same problem.

[EvacX]

You're probably only using 2 columns in that table. You need 3.

1 for the steamid, 1 for the item and the last as an identifier column.

Since every row is uniquely identified by 2 columns, you need a composite primary key consisting of the steamid and identifier column.

Use this:

PRIMARY KEY ( `steamid`, `itemid` )

To make a composite primary key.

Then, assuming your itemid column is auto incrementing, when you insert you only insert 2 values. The steamid, and the item column.

[KKlouzal]

What's the matter with using TableToJSON andJSONToTable to store into a TEXT column? granted you only have a table with a max of say 10-15 keys

[EvacX]

[QUOTE=kklouzal23;41452821]What's the matter with using TableToJSON andJSONToTable to store into a TEXT column? granted you only have a table with a max of say 10-15 keys[/QUOTE]

First of all the performance. TEXT is stored off the table with the table just having a pointer to its location, this increases access times.

Secondly, to modify any encoded column (in this case using JSON), you either have to use some string altering method (which is even worse) or first read the column, then decode it, then add whatever you wanted to the table, then encode it again and finally update the column with your new encoded table in the database.

Both previously mentioned methods require that you first read from the column, then modify it and then insert/update it, which can easily take several times longer. It also ruins the point of a relational database, you gain none of the advantages that MySQL has to offer such as constraints and ways you can handle your data.

If you do it the proper way, you will only have to insert the value and the database takes care of the rest, which is clearly a way simpler and faster process than going through all of the above. After all, good programming is all about keeping it simple.

[OzymandiasJ]

[QUOTE=EvacX;41453003]First of all the performance. TEXT is stored off the table with the table just having a pointer to its location, this increases access times.

Secondly, to modify any encoded column (in this case using JSON), you either have to use some string altering method (which is even worse) or first read the column, then decode it, then add whatever you wanted to the table, then encode it again and finally update the column with your new encoded table in the database.

Both previously mentioned methods require that you first read from the column, then modify it and then insert/update it, which can easily take several times longer. It also ruins the point of a relational database, you gain none of the advantages that MySQL has to offer such as constraints and ways you can handle your data.

If you do it the proper way, you will only have to insert the value and the database takes care of the rest, which is clearly a way simpler and faster process than going through all of the above. After all, good programming is all about keeping it simple.[/QUOTE]

To add to this, you should be using VarChar for names and SteamIDs, and a limited int for 64bit id.s

[KarmaLord]

In answer to the base question, instead of just updating the single item do something like this:

inventory = {"item1","item2","item3"}

function addItem(ply,item)
   local SteamID = ply:SteamID()
   table.insert(inventory,item)
   local update = table.concat(inventory,";")
   local query = "UPDATE users SET inventory = '"..update.."' WHERE steamid = '"..SteamID.."'"
   --Some SQL method here
end

function getInventory(ply)
   local SteamID = ply:SteamID()
   local query = "SELECT inventory FROM users WHERE steamid='"..SteamID.."'"
   -- Some SQL stuff here
   inventory = string.Explode(";",results)
end