Indie-Resource.com

is there a way that we can assign a primary id to the items, weapons or armors so that whenever we change the details (eg: rating or damage), it will automatically changes the players' item details as well?

Update weapon 1 details under table weapon

All weapon 1 under playerweapon will be updated

What you could do is make another table that stores both a reference to the player that has the item, and a reference to the item the player has. When the item is needed, it could use a query such as:
Just add in the fields you want to retrieve after the SELECT keyword (I am not sure what all you have in your table). The above query will pull a specific item. To get all of the items, say for an inventory screen, remove the "...AND mit.item_name = $itemName AND..."

Basically what what you are doing is having a table that keeps a reference to both the player and the item kept in a separate table, and then using a join to pull the info about the item from the master item database. That way the items only have to be updated in one place and the update will instantly apply to all players.

I just woke up, so I hope I wrote this correctly.

EDIT: And if you have not seen "alias" syntax, the "...FROM table_name AS identifier" part is just giving a smaller name to the table to be used in the query.

Hmm, is it something similar to this?
http://buildingbrowsergames.com/2008/05 ... ts-system/

////////// Another Question//////////
Jack, are you familiar with hall's tutorial video?

Currently for items alone, we have several tables.
say, store, weapons, armors and etc. And if players bought and equip, we will have another three more tables, inventory, playerweap and playerarmor.

Not to mention if we decided to equip monsters with weapons and armors as well, then we'll have extra few more tables.
(you get what I mean)

And if we are to link the monsters with item drop, we'll have to query the particular item's table. Eg: monster drops potion, query potion datas from store.

The codings will be quite messy, as you can see.

Is it better if we lump all the items together under one table, separating them by type so players only query once and it'll be less stressful on the server.

I got the concept when I was reading this
http://buildingbrowsergames.com/2008/07 ... ms-system/

I have not worked all the way through Halls' video tutorials yet, no. However, the reason for this intermediate table between the master item table and player table is because there is a one-to-many SQL relation between the master item table and the player's inventory: A player can have multiples of one specific item type, and multiple players can have the same item type. For these items to automatically update when the master item table is updated, you need to have a reference to that master item table and just pull the info about the item from there as needed, and the easiest way of doing that that I am aware of is to look it up based on the player ID on a joined SQL query.

Plus, don't worry about having 4 or 5 extra tables based on the item types. Each player won't have their own 4 or 5 tables since all players who own that kind of item will be on the same table. A few extra tables really shouldn't be a problem. Many large web applications have hundreds of tables.

jack, help me figure out how to route this.

currently we have a store layout
in which, if players need to buy anything, the script draws data as such:
This is when players are trying to buy the item
After the item is being bought, we insert the item into player's inventory
This is the script when players need to use the items from inventory I am thinking of creating a master items database, so the stores and inventory will just query the datas in one place.
but the question is, players still have to read from the inventory, in which the data has already been inserted into the database right?

so if we have a certain update on one item, say a potion, we have to change the data on the master items db and also the inventory db.

Is there a way that the inventory can mirror the data from the master items db?

I am sorry if I confuse you.

Definitely. All items should only be referencing from the master item table, including both inventory and store tables. The stores and player inventories would only hold the item ID that it references, plus a quantity field, and store field (to show which store it is in for the store table) or player field (for player inventories, to reference to the player who holds it).

When you need the item info, you won't likely be able to use "SELECT * from..." (or at least it won't be performance efficient to do so), because you will be pulling the data for two whole tables. You would need something more like:
What this query does is pull all of the fields from the MasterItem table (where the master info on each item is) by first looking up the playerID and then looking up which items are in both the Inventory and the MasterItem table. It should return an array of all the items the player has and their related fields from the MasterItem table. If you wanted just one item, you could tack on a "...and Inventory.name = '$itemName'" at the end.

I would really have to sit down and go over this and your db structure a bit more to make sure this would work 100% and to ensure it is optimized decently, but a strategy along these lines would ensure that any updates in the one MasterItem table would be reflected on all items instantly.

sorry for the late reply, but I got the idea.

currently going backwards, starting from scratch again trying to build my register and login page.

I'll get there within a few days of time, until then we shall discuss about this further.

and thanks jack,