Good way to store Items in a mySQL database?

Questions & Answers
1

Hello all,

I’m just wrecking my head how to add my generated Items to a database to load it each time the webplayer is used.

I’ve created a simple Item class and several Weapons, Armor, etc that inherit from that Item Class. The Inventory is working well, I store generated Items with random values into my Item-List and show them via GUI Buttons.

The problem here is I can’t think of a way to store them into a database (along with other players) to load them when the player comes back. I thought about auto_increment the database so every added item will have a unique ID, but how can I assign that ID to the Item in Unity to prevent adding the same Item again?

I have a userID, atm 25 Inventoryslots (i think they wont change in future), ItemTypes (Weapon, Torso, etc) and the Itemvalues (price, dmg, several boni stats, etc).

I tried creating an Inventory database with userID, itemType and itemValue to store them and receive them by getting the userID. The problem here is how to select the right item when I want to delete it? Let’s say the player sells the item and I have to remove it from the database, too. How should i select it? I could possibly select it by it’s values, but there could be another item with the exact same values (also if it would be very lucky ^^). So I need a unique value (primary key) to select items from my db, but how to create such a unique value without the danger to insert a duplicate into the db?

I’m no good programmer or database creator ^^ But I really stuck here, can’t figure a way out to correctly store and retrieve or delete the correct items for my Inventory.

Hope you could understand at least a little bit, sry for bad english. Thanks in advance!

2

I’ve found a decent way I guess.

I created a database called “inventory” and added these columns: ID, itemID, itemType, itemName, itemStats

ID → playerID
itemID → primary key (auto_increment)
itemType → example: Weapon, Torso, etc.
itemName → Helm, Sword, etc.
itemStats → 1,2,3,4,5, (the length varies depending on the item, I save all values the item have like damage, buffs, etc in a single varchar column)

Now I simply added the itemInfo from Unity via WWWForm into the database like usual and used an mysql command to retrieve the last inserted itemID, send it back to unity and assigned it to the item in Unity. Now I have a unique connection and be able to surely delete the correct items from the database…

The only concerns I have is the auto_increment. For example if thousands of people play the game and items will continously be created, the numbers from auto_increment will increase a lot, therefore the lines send back to unity get longer and longer (also there could be a limit or a too high number to store?)

What’s a solution for that? filling the gaps from deleted items? Resetting the table from time to time to new auto_increment values that start again at 0?

3

Work with mysql databases by using of how to fix mysql error 1046

Read item http://www.mysql.fixtoolboxx.com it would allow you to work with mysql databases