This is for a game I’m working on, but it have nothing to do with java, so I need to post it here. So far I have set up the database like this
This is the character table, inventory_id is meant to get the slots where the player can put the equipment
[quote]account_id | name | race | health | mana | exp | created | sex | character_id | world_id | inventory_id
2 | Yemto | Humanoid | 100 | 100 | 0 | 2014-06-10 20:44:28 | 1 | 7 | 0 | 1
2 | Fire | Spirit | 100 | 100 | 0 | 2014-06-11 03:00:43 | 0 | 7 | 0 | 2
2 | Test | undead | 100 | 100 | 0 | 2014-06-18 10:44:43 | 1 | 7 | 0 | 3
[/quote]
This is what the item table looks like, it’s meant to hold most of the date of an item
[quote]item_id | name | info | icon | stack_size | untradable | inventory_id
1 | bag | NULL | NULL | 10 | 0 | 2
2 | cheese | NULL | NULL | 100 | 0 | NULL
5 | tea | NULL | NULL | 100 | 0 | NULL
6 | Big bag | NULL | NULL | 100 | 0 | NULL
[/quote]
The inventory table is so far only used to set how many slots and what type of slots a inventory have.
[quote]inventory_id | normal_slots | weapon_slots | bag_slots
1 | 0 | 2 | 1
2 | 6 | 0 | 0
[/quote]
This is the item_stack table, when someone buys an item an entry in this table is created to create the item.
[quote]item_stack_id | item_id | amount | recursive_id | slot | owner | recursive_table
1 | 3 | 1 | 5 | 0 | 7 | character
2 | 5 | 2 | 1 | 0 | 7 | item_stack
3 | 6 | 5 | 1 | 1 | 7 | item_stack
4 | 5 | 6 | 1 | 2 | 7 | item_stack
5 | 3 | 1 | 7 | 0 | NULL | city
[/quote]
To get something usable from item and item_stack I’m using this
$item = $conn -> prepare("SELECT * FROM `item_stack` LEFT JOIN `item` USING (`item_id`) WHERE `item_stack_id`=:id");
$item -> bindParam(":id",$this->id);
$item -> execute();
$this -> item = $item -> fetch();
But this is where I encountered a problem. recursive_id and recursive_table in the item_stack table is to keep track of who holds the item, which works. But if I want to back track to see if the inventory size is valid, I can’t. Lets say a player find a way to put something in slot 8 in a bag, and as you probably can see, the bag don’t have that many slots. I had the idea of using the recursive_table and recursive_id to see which inventory the item have.
This is the code I began coding, until I realized that it doesn’t work.
$inventory = $conn -> prepare("SELECT `inventory_id` FROM `{$recursive_table}` WHERE `{$recursive_table}_id`=:rid");
$inventory -> bindParam(":rid",$recursive_id);
The code works for characters, but not for item_stack since that will just get another entry to another item_stack which don’t contain inventory_id. I thought of doing something like this.
if($recursive_table == "item_stack"){
$recursive_table = "item";
$recursive_id = $stack["item_id"];
}
$inventory = $conn -> prepare("SELECT `inventory_id` FROM `{$recursive_table}` WHERE `{$recursive_table}_id`=:rid");
$inventory -> bindParam(":rid",$recursive_id);
But I don’t know, it don’t seem to be the best say to me, so I was wondering is anyone here had a better solution.
This is a good example of what kind of inventory system I’m going for
http://mmohuts.com/wp-content/gallery/runes-of-magic/runes-of-magic-inventory.jpg?bb7a3b