Inventory

[Sinzygy]

I'm in the process of creating a decent invenotry system, but I feel like I could make it a lot better than what it is at the moment.

Currenty my inventory is based on 2 tables.
The equipment table which contains the name, ID and type of an item.
The inventory table contains the user (who owns this item), the itemID and the amount of this item.

Now when I want to list the inventory, I have to query first the inventory table and yank out all the IDs for the owned items.
After that I have to query the equipment table for each item individually to determine the correct type and then add it to an array of that type.

This might work for a couple of items, but my guess is that it will put quite some stress on the db when there are a lot of items per player.

Does anyone have a better suggestion on how to do this? Oh btw: I'm using this as a spereate function and not as a class.

[dvd871]

The equipment table which contains the name, ID and type of an item.
The inventory table contains the user (who owns this item), the itemID and the amount of this item.

Have your inventory table store the names of the items.  That way you can just query one table (inventory) for all the info.

[Sinzygy]

Well, I actually need a lot more information than just the name. I simply put only the name there, to avoid confusion. But I want to make it so that when the user hovers over each different item he owns, he gets the info (like effect, damage type, cost, etc.) displayed.

[Zeggy]

Mmm, well, for my RPG I use pretty much the same system. One table with owned items, one with the item 'blueprints'.

I can think of one way, which is to add all the item information into the owned items table when a row is inserted, then after that, each time you get the player's items, you will also get the info for those items automatically.
I'm not sure how well that might work though, and if it will be more efficient.

[codestryke]

Code: [Select]

$rs = $db->Execute("SELECT player_equipment.player_equipmentid, player_inventory.amount, equipmentlu.name, equipmentlu.type
FROM player_equipment, equipmentlu
WHERE  player_equipment.equipmentid = equipmentlu.equipmentid
AND player_equipment.playerid = $playerid");
$inventory = "";
while( !$rs->EOF ) {
$key = $rs->fields['type']
$inventory[$key][] = array("name" => $rs->fields['name'], "amount" => $rs->['amount']);
$rs->MoveNext;
}

The above code will place each type of equipment in there own array..
So if you have an equipment type of weapon you can display/show all the weapons with a foreach loop

Code: [Select]

if( isset($inventory['weapon'] && !empty($inventory['weapon'] ) {
  foreach( $inventory['weapon'] as $w ) {
    echo $w['name'], ' - ', $w['amount'];
  }
}

Might be a couple mis-types of small bugs since I just wrote this out really quickly but you should get the idea on how to do this by the examples above

And yes the way you have the database laid out for this is absolutely correct when it comes to proper table structures and db normalization

[Sinzygy]

Good damit! How could I forget something as simple as a join-query?

Thanks a lot Code, but are you sure that it's ok to first define $inventory as an empty string ("") and then calling it an array? Just wondering.

[codestryke]

Never had a problem doing so in the past. I init it to a empty string so PHP doesn't throw a warning about an un-initialized variable and also so later in the code, if I need to, do a simple  is_array($inventory).

[Sinzygy]

In this case never mind then

Probably just personal preference. I probably wouldn't understand my code the day after I wrote it, if I didn't document it or put some obvious hints into it.