[SOLVED] How to : User Item Count

[gizmoman]

Hi, i am new to MySQL and i am trying to make a simple online game. I searched the web about my problem about 1 hour and nothing found. May be it's about my little english huh  Let me ask what i am trying to do. And i dont have any idea about that.

 

I have an item table that has about 50 items. And a user table. What i want to do is inserting database how many and which item does a user have. Like "A user has 35 item B", "A user have 20 item C" and so... I thought about it some and i just found creating fields for EVERY item on user table and insert the items count on there. And this would be so much effort for mysql i think. Any other suggestion to do that easier please? I bet there is but i dunno yet Thanks...

[AjBaz100]

I think something like this may work.

 

Users table

user_id | user_name

--------------------

1        | Some Name

2        | Some other name

 

Users Items table

item_id | user_id

----------------

1        | 1       

1        | 1

 

Items table

item_id | item_name

-------------------

1        | A

2        | B

3        | C

 

select count( i.item_id ) from Items i left join Users u on i.user_id = u.user_id where u.user_id = 1 group by i.user_id;

 

Or something like that.

 

 

 

[gizmoman]

thank you very much it is really easy.