help retrieving data

sumday

I type this code in and it prints out potion1sword1 (item)(ammount) However.... the only item that has 1 in ammount is potion and for some reason sword is printing from the database too. Can seombody tell me what I did wrong?

$username = $data['Username'];
$result=mysql_query("SELECT Name from items");
while($file=mysql_fetch_assoc($result)){
      $factor=mysql_query("Select Name,$file[Name] from items,inventory where Username='$username'&& $file[Name] > 0");
      while($col=mysql_fetch_assoc($factor)){
          print join('',$col);

   }
}

zhabala

This code looks like it is quite messed up.

I am assuming you have two tables Items and Inventory.

And you want to select all items for one particular person or user. It could be done with one select statement, looking like that:

SELECT Name, Amount FROM items, inventory WHERE inventory.id = items.inventory_id AND inventory.username ='$username' an items.amount > 0

but it really depends on the database structure, if I am wrong, please post your DB structure here.

sumday

TABLE ITEMS:

Name | Price
Potion | 10
Sword | 10

TABLE INVENTORY

Username | Potion | Sword
test | 1 | 0

if you have a better way to setup the tables please tell me.

zhabala

TABLE ITEMS

id | NAME | Price
1 | Potion | 10
2 | Sword | 10
3 | Shield | 100

TABLE INVENTORY

Username | Item ID | Amount
zhabala | 1 | 1
zhabala | 2 | 0
zhabala | 3 | 2
other | 1 | 0
other | 2 | 1

means user zhabala has one potion and two shields in his inventory and user other has one sword in his inventory.