[SOLVED] Mysql opinion

[metalx]

Lets say we have a website where users register and  some items to their collection

IS it better to have a table for each user that contains items he adds

for example a table named [username/id]_items

and inside this table we have a list items:

itemID itemDesc

 

or we have a global table named userItems

And in this table we have the item and which user chose it

itemID itemDesc user

 

Now when we want to retrieve what the user chose:

 

case 1: we select * from [username/id]_items

 

case  2: We select * where user=specificUser from userItems

 

I'd go for the second method for if we want to add a new property to the item chosen we simply add a new field to 1 table (global table userItems) rather than modifying each user table!

 

But then lets say we have thousands of entries in this table... will the query(retrieving items of a spesific user) take time? Is the user going to wait much longer to check his inventory(where a list of his items are stored)

 

Which way is it better. Method 1  or method 2

If method 1 is there a way of modifing a set of tables that end with _items

 

 

[dreamwest]

Have 1 table to store all users info

 

Theres no benefit to having multiple tables if theyre on the same machine - and its easier to have them in one spot

[metalx]

I am thinking if doing the following

 

1 table for users: ID username password....

1 table for items  ID item desc image....

1 table for userItems  ID itemID userID

 

To retrieve info i can use this method:

 

$query = "SELECT * FROM  userItems WHERE ID  = '$userID' " ;
$results = mysql_query($query);

echo "xml=<?xml version=\"1.0\"?>";
echo "<items>";

while($line = mysql_fetch_assoc($results)){
echo "<val>". $line["itemID"] . "</val>";
}

echo "</items >";

 

 

Now i want to retrieve items info from the table items

I send item ID to a php file and do the following code

 

$query = "SELECT * FROM  userItems WHERE ID  = '$userID' " ;
$results = mysql_query($query);

echo "xml=<?xml version=\"1.0\"?>";
echo "<items>";

while($line = mysql_fetch_assoc($results)){
echo "<val>". $line["ID"] . "</val>";
        echo "<val>". $line["item"] . "</val>";
        echo "<val>". $line["image"] . "</val>";
}

echo "</items >";

 

 

This might work but if u can see i first fetch the items for a user, send them to my website(flashBased) then flash send for each item its id to php and retrieves its info

 

I was wondering is there a way to do all this all in 1 php file other than this way?:

 

$query = "SELECT * FROM  userItems WHERE ID  = '$userID' " ;
$results = mysql_query($query);

echo "xml=<?xml version=\"1.0\"?>";
echo "<items>";

while($line = mysql_fetch_assoc($results)){
		$query2 = "SELECT * FROM  userItems WHERE ID  = '$userID' " ;
		$results2 = mysql_query($query2);
		echo "<item>";
		while($line = mysql_fetch_assoc($results2)){
				echo "<val>". $line["ID"] . "</val>";
				echo "<val>". $line["item"] . "</val>";
				echo "<val>". $line["image"] . "</val>";
		}
		echo "</item >";
}

echo "</items >";

 

Any suggestions or am i on the right track?

[dreamwest]

Your going to end up with a thousand queries if the user has a thousand items

 

$results = mysql_query("SELECT * FROM users AS u, item as i WHERE u.ID = i.ID ");

echo "xml=<?xml version=\"1.0\"?>";
echo "<items>";

while($line = mysql_fetch_assoc($results)){
   echo "<val>". $line["ID"] . "</val>";
        echo "<val>". $line["item"] . "</val>";
        echo "<val>". $line["image"] . "</val>";
   }
   
   echo "</items >";

 

Of couse thats assuming ID relates to the actual user and not auto increment

 

I would have it like this

 

1 table for users: userID(auto increment) username password....

1 table for items  id (auto increment) userID item desc image....

1 table for userItems  id (auto increment) userID itemID

[metalx]

Your going to end up with a thousand queries if the user has a thousand items

 

$results = mysql_query("SELECT * FROM users AS u, item as i WHERE u.ID = i.ID ");

echo "xml=<?xml version=\"1.0\"?>";
echo "<items>";

while($line = mysql_fetch_assoc($results)){
   echo "<val>". $line["ID"] . "</val>";
        echo "<val>". $line["item"] . "</val>";
        echo "<val>". $line["image"] . "</val>";
   }
   
   echo "</items >";

 

Of couse thats assuming ID relates to the actual user and not auto increment

 

I would have it like this

 

1 table for users: userID(auto increment) username password....

1 table for items  id (auto increment) userID item desc image....

1 table for userItems  id (auto increment) userID itemID

 

of cource they be auto increment..

but i didn't get your code.

From what i could understand that we are selecting all from users as u and items as i

but the where indicates u.id=id!

Now this will return back all items in the table userItems

because the table items contain a list of all the items! and table useritems contain all items chosen by different users!

 

I want to fetch items for a specific user  $user

How to modify the code?

 

[dreamwest]

If your table looks like this:

 

1 table for users: userID(auto increment) username password....

1 table for items  id (auto increment) userID item desc image....

1 table for userItems  id (auto increment) userID itemID

 

Then the query will be:

 

$user = 2;

$results = mysql_query("SELECT * FROM users AS u, item as i WHERE u.userID = '{user]' and u.userID = i.userID ");

 

Think of userID as your anchor to other tables

 

 

[metalx]

That should dp the trick..

 

You helped

Thanks