Inventory System SQL

[ChaosXero]

I'm currently building an online text game in PHP and need to create an inventory system.  My thought was that there would be a main inventory database and that players would have 1 inventory field w/ items seperated by a '-' so that later I could explode it and put it into an array and get the items from the database.  I dont know if this is the best idea, so if you have suggestions cool.  My real question however is this: If I wanted to see how many of a certain item were in peoples inventory, how would the SQL for that look? Or would it work? 
Example: I need to see item #44543 so I 'SELECT * FROM users WHERE inventory LIKE '#44543' or would that return false positives?

Any suggestions appreciated.

[pixy]

Okay, firstly DONT seperate items by - marks. If you were even going to try to store stuff in a database like that, use serialize() (it stores arrays in text format, which you can unserialize() later)--but that's not a good idea either...far too messy IMO.

Actually, I'm going to be working on an inventory system too, for my own RPG (it's based on Harry Potter) so if you want I could definately help you out. I'm going to be making shops and a bank and such...

[kalivos]

depending on how advanced you would like to make it, you could also make a table like: user, item_id, duribility, creator ...etc This will allow you to make an item "break" or remember where the user got it, and allows for a query to find how many items are in the db that correspond to that user. Just an idea, as it does involve a more complex scheme.

-Kalivos

[ChaosXero]

Wouldn't that evetually create a massive database though?
For example: 1,000 users w/ 1,000 items = 1million rows.  OUCH that hurts.

[redarrow]

SELECT SUM(inventory) FROM users where id='$id'

[pixy]

[quote author=ChaosXero link=topic=100913.msg398838#msg398838 date=1153161081]
Wouldn't that evetually create a massive database though?
For example: 1,000 users w/ 1,000 items = 1million rows.  OUCH that hurts.
[/quote]
Would you SERIOUSLY let your users each keep 1,000 items? There HAS to be a limit somewhere.

What I would do is create and items table with all the items information: rarity, value, store, descpription, imagelink, etc. and then a table called inventory with userid as one column and item ID in another. So yea, you'd have a lot but you wouldn't have to put in NEARLY as much information.