MySQL and php - help needed

Jordan79

I have created a PHP script that allows a user to login and upload an image that is then saved to an image folder. I now want to store the information about this image in a database. I have created 2 tables in MySQL:

user(user_id,first_name,surname,email,username,password)
user_id is the primary key
gallery(image_id,user_id,image_name)
image_id is the primary key,user_id foreign key

How do I go about inserting the image name into the gallery database and linking it to the user table using user_id?
I cant seem to create relationships like in Access.

Thanks

seamus

The relationships don't exactly work like in access.

What you do is insert the image data in an INSERT statement, so

INSERT INTO gallery (user_id, image_name) VALUES (354, 'someimage.gif');

Then when you want to get all images belonging to a certain user, you call

SELECT * FROM gallery WHERE user_id = 354;

The FOREIGN_KEY constraint has a number of functions, but most importantly won't allow you to insert a user_id in to the gallery table if that user_id doesn't exist in the user table.

Jordan79

Thanks for your reply.

If I wanted the user_id to correspond to which user is logged in could I use a session variable within the SQL command?
e.g
INSERT INTO gallery (user_id, image_name) VALUES ($_SESSION, 'someimage.gif');

I dont know if I am on the right track....

seamus

Yep. That would work.

Ideally though you would store the user_id and (hashed) password on the user's machine in a cookie. That way you can authenticate the person every time they access a page and ensure that the person who's uploading the picture is the person who logged on. Any other info can be stored in a session, which can be discarded if the user fails the authentication check.