Your suggestions, ideas, and thoughts.

[Rifts]

I'm in the process of making a PHP/MYSQL game and I'm trying to figure out the best way to set the database up specifically to deal with items.

 

All day I have been wrestling with how to do this. How do I link items to players?  How do I keep track of who has what items?

 

I know this question isn't at all specific sorry for that, it's hard to explain exactly what I mean thought text.

 

Thanks

[Garethp]

A 9 part video on how to properly construct a database for efficiency and integrity. Start here.

 

Come back only after you can tell me what the three normalization forms are, and if you still have some trouble

 

[Rifts]

I've made numerous websites that use MYSQL databases I just cant figure out how to set it up for something this large.

[PaulRyan]

Hello rifts, I too am in the process of making a game (Text based MMORPG to be precise) and fell short at the exact same problem.

I did however come up with a solution, which I will soon implement myself but read on.

 

Firstly, you should create a table called item_database or whatever you want.

This will hold the items within your game and store such details as:

item_id - The ID number of the item (unique)

item_name - The name of the item (varchar)

item_description - A brief description of the item ect (longtext)

item_type - The type of item this is, head, torso, legs, shield, weapon, aug, necklace, gloves, boots, potion, collectable card ot whatever.

item_attack, item_defence, item_hp - These will be the attributes of the item (int)

Any other details you require

 

Secondly, you create a table called player_items or whatever you want.

This database will be used to link to player profiles aswell as the item database.

Such fields would include:

id - A unique identifier (unique)

main_item_id - The Item ID number from the item_database that this represents (int)

player_id - The ID number of the player that owns the item (int)

Other details could be whether the item is upgraded, or how rare it is ect.

 

Finally, and the users database add some fields such as:

head_slot - The id number of the item in the player_items database that is in this players head slot (int)

 

Then the databases would work something like the following:

// Lets fetch the head_slot ID Number here
$myQuery1 = mysql_fecth_assoc(mysql_query("SELECT head_slot FROM user_database WHERE user_id='$user_id' LIMIT 1"));

// Use the head_slot ID number to select the item from the player_items database
$myQuery2 = mysql_fetch_assoc(mysql_query("SELECT main_item_id FROM player_items WHERE id='$myQuery1[head_slot]' LIMIT 1"));

// Use the item_id number from the previous query
// Now we select the item that matchs the item_id from the main item_database
$myQuery3 = mysql_fetch_assoc(mysql_query("SELECT item_id FROM item_database WHERE item_id='$myQuery2[item_id]' LIMIT 1"));

 

Hopefully this will make some sense to you and can use this to help with you situation.

(I just hope I havent written all this for nothing )

 

Thanks, Paul.

 

[Rifts]

Thank you PualRyan that really does help a lot!

 

How were you planing on implementing a inventory?

[PaulRyan]

An inventory could be implemented quite simply by adding a new field to the database player_items named in_inventory

Then set the default value to 'Yes'.

That means when the player aquires a new item it will be automatically in the inventory of that player.

 

You could the equip and unequip the item by updating the table and changing the in_inventory field to either 'No' or 'Yes'.

The following functions could do that:

 

function equipItem($itemID,$slotName,$userID) {
  // Check if player has an item already equipped in the select slot
  $itemEquipped = mysql_fetch_assoc(mysql_query("SELECT $slotName FROM user_database WHERE user_id='$userID' LIMIT 1"));

  // If they have an item equipped we'll unquip it here
  if($itemEquipped) {
    mysql_query("UPDATE player_items SET in_inventory='Yes' WHERE id='$itemEquipped[$slotName]' AND player_id='$userID' LIMIT 1"));
  }

  // Previous item should now be in inventory
  // Now we equip the item we want here
  mysql_query("UPDATE user_database SET $slotName='$itemID' WHERE user_id='$userID' LIMIT 1");
}

function unequipItem($itemID,$slotName,$userID) {
  // Firstly send the item to the inventory
  mysql_query("UPDATE player_items SET in_inventory='Yes' WHERE id='$itemID' AND player_id='$userID' LIMIT 1"));

  // Now we will remove that item from the slot selected
  mysql_query("UPDATE user_database SET $slotName='' WHERE user_id='$userID' LIMIT 1");

}

 

That should work in theory, but I have not tested this code, using my intuitive knowledge with this...

Hopefully this will shed some light on your situation rifts

 

Thanks, Paul.

[ignace]

$itemEquipped = mysql_fetch_assoc(mysql_query("SELECT $slotName FROM user_database WHERE user_id='$userID' LIMIT 1"));

 

Strange code. If you query on primary key how many records do you think you'll possibly get back? If you perform one single mysql_fetch_assoc() how many records do you think you'll get back?

[PaulRyan]

Strange code. If you query on primary key how many records do you think you'll possibly get back? If you perform one single mysql_fetch_assoc() how many records do you think you'll get back?

 

Im pretty sure I'd get one back...

Isn't that what "LIMIT 1" actually does, or am I missing something? :-\

 

Thanks, Paul.

[ignace]

If you query on a primary key you get - at maximum - 1 result as a primary key is a unique index back so their is no need for the LIMIT-clause.

[PaulRyan]

Ohh right, I understand now still no harm in keeping it there though, not that I know of anyhow.

 

Thanks, Paul.

[jcbones]

Guys I think it would be valuable for you two, to take 30 minutes, and look at the video/s that Garethp linked to.  They will get you on track for an EFFICIENT system, that when you have a lot of people tied to it, it will not fail.

 

Running your tables through Normalization Forms will help you build efficient queries as well.  Eliminating the overhead of multiple database interactions.

 

Of course, this is just my opinion. 

[Rifts]

thanks jc it couldnt hurt to check out the video I will =]