Having something associated with something...[[MySQL]]

Ok, my title was vague..i know...bare with me for a minute.

I have some what moderate level of php knowledge, but less MySQL knowledge..

I set up a user table called user with rows like first name, last name,..gender etc..

What I want to do is have the user create a "list" and within that list,..there are items. i guess a good example is a photo album..

the user can create a photo album,..and within that photo album there are images..

Can anyone lead me in the direction of how to get my database to understand the relationship between a an item belonging within a list, and a list belonging to the user?..

Thanks for all your help!
-Tim

 

Firstly I will not "bare" with you... thats gross.

But I will "bear" with you.


So basically what you want to do is setup a user id or username and carry that in both tables. Then you want to do:

//connect to your db here
include "mysql.php";

// Get Username thats logged in
$username="jon";

$q="select * from user_table where username='$username'";
$r=mysql_query($q);
while ($row = mysql_fetch_array($r)){
$mysuser=$row['username'];
     $q2="select * from image_table where username='$mysuser'";
     $r2=mysql_query($q2);
     while($row2 = mysql_fetch_array($r2)){
     $img_name=$row2['image_name'];
     echo "Username: $mysusername owns image: $image_name";
     }
}

PHP:

That's the jist of it.

 

thank you. I will try that out. I appreciate the help, and catching the spelling mistake

How do i carry the user id in both tables? I have within the useres table an id row auto incriment, how to i give that relation ship to the image table, or another table?

 

Presumably when the user adds an image you will have already gotten their username/ userID as they have logged in. For the photo table you would have a primary key of something like PhotoID and the username/ userID field will be none unique as presumably the user can have multiple photos.

If you want to ensure some greater integrity then you can set the username/ userID field in the photo table as a foreign key as it will prevent a username/ID being used that does not exist in the usertable... see http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html for mysql instructions on foreign keys

 

Thank you both for your help. Im going to try it now.