Browser-Based MMO Inventory Problem

I am wondering how I would create an inventory for a player storing an infinite amount of predefined items. For example, lets say that I have the following items: Apple, Pear, and an Orange, each have pre-defined values (Apple has a Rating of 4 and a Price of 3, Pear has a Rating of 5 and a Price of 6, etc...) and I have a tonne of existing players who can have any amount of any of these items. I have no clue how to do this... currently I have a table called player which stores their session, username, amount of currency, etc... and I have a table called item which has the rating and sell price......

Any suggestions how to create this inventory for a player? I was considering limiting the amount of items a player could have and just literally adding x amount of fields for each inventory slot, but there has to be a significantly better way of doing this, particularly one that does not limit the amount of items each player can have.

you can create a third table that contain the user id, item id and a stock field which will hold the amount the user has of that item.

Still how would you hold different itemids? If I had a field called userid, itemid and amount... I can understand how that would work with an input like "username", "apple", "5", or something, but how would I store the amount of pears and oranges and bananas and anything else aswell in that table?

...

I don't see the problem. You create a table with userid, itemid, amount (not necessary!) and itemname.

taking you example input then "username", "apple","5" is when the user have 5 apple. if a user have 10 orange then you will make an input like "username","orange","10".

"username" should more be a user_id that link to the user table (which contains session, username, amount of currency, etc... ).
Same goes with the item name, this should be a item_id that link to the item table (which store the rating and sell price etc).

hope this answer your questions.

Like these guys have already said:

User Table
==========================
UserID | Username | Password | Money... etc...
==========================

Item Table
==========================
ItemID | ItemName... etc...
==========================

Inventory Table
==========================
UserID | ItemID | Quantity
==========================
Only use quantity field if you want stackable items in set amounts... For example, a user can only have 20 oranges in one stack, before a new stack is made... (Think World Of Warcraft)

Otherwise, leave Quantity out and just do a record count to get the quantity of an item that the user has.

Couldn't you have each user have a userid, then make a separate table with the item info?

user info
=============================
userid | username | password | etc...
=============================

items

========================
userid | serialize array of items |
========================

First make a master array of all items:

$allitems = array();

Then make a web script to add something like this:

$item = array($itemname, $quantity);

Then add it to the bigger array like this:

array_push($allitems, $item);

Then serialize the big array like this:

serialize($allitems);

Then add it to the database...


When you want to get the info back, use this:

$allitems = unserialize(some_database_return_function());


Of course you would want to organize the items into weapons, healing items, keys, etc... just to make it a little faster to work with.

Having an array of items is a bad idea. What you usually want is an 'item' table: every single item in the game, itemid, user who owns it. That's what I'd do, personally. Makes finding items much easier.