Jump to content

Your suggestions, ideas, and thoughts.


Rifts

Recommended Posts

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

Link to comment
Share on other sites

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 :D )

 

Thanks, Paul.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

$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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.  :P

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.