use table data as variable to other tabl

Anon

I have two tables one with users, and one with items they own what i'm doing is to select * from users_tbl and then with a php loop i select all items for items_table where user liky $user_from_users_tbl. The problem is that i've got 15 items category and a list with 20 users generates 400+ querys any idea how can i make it so i don't have that much querys?

Thanks in advance.

fitzbean

Well, assuming you have a unique id for each user, and the user id of the person that each of the items belong to.. ie..

user table -

userid nameofuser

items table -

itemid, userid, nameofitem

SELECT name, nameofitem FROM users, items WHERE users.id = items.userid AND users.id = whateveruseryouareselecting;

Or something like that.. let me know if this helps.

You shouldnt need more than 1 query per user; and if you do, your DB design is probably the cuplret.

Anon

i forget to mention that i want to count how many items a user own to get a table like this:

User|item 1|item 2|item n|Total|
John| 3 | 0 | 2 | 5 |
Erik| 0 | 5 | 7 | 12 |

fitzbean

Can you outline the 2 tables design for me?

Anon

items:

iduser|item_owned|dateadded|

users:
id_user|username|

fitzbean

SELECT username, item_owned, count(item_owned) FROM users,items WHERE iduser=id_user GROUP BY item_owned

This will return IE:

name|item number|total of this item
john|1|3
john|2|1
john|3|2
john|4|3
mary|1|4
mary|2|1
mary|3|3
mary|4|4

Let me know!

Anon

that's almost it, but i want just a line with each user like this:

name|item 1|item 2|total
john| 3 | 1 | 4
mary| 4 | 1 | 5

thanks!

Anon

And if there are no items of some class they will not appear and i need this items to appear with zeros, so we get

name|item 1|item 2|total
john| 3 | 1 | 4

mary| 4 | 0 | 4

fitzbean

Hehe, you'll have to use the results to output them the way you want them to appear. That's beyond the scope of your question. Oh, and your welcome.