php - insert into two tabels in mysql database at the same time

Question

I have a database, which I should able user to upload multiple images to his account,

How could I do this in MySQL database and its PHP code to insert them.

Here what I tried, I created 2 tables

CREATE TABLE `user` (
  `userID` int(11) unsigned NOT NULL auto_increment,
  `imageID` int(11) unsigned NOT NULL,  
  PRIMARY KEY  (`id`)
)

CREATE TABLE `iamges` (
  `imageID` int(11) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`imageID`)
)

so, each user can upload multiple images through imageID and in the images table I will put image1, image2 etc.

When I want to select them I will use :

SELECT user.name, Persons.email, images.image1, images.image2
FROM user
INNER JOIN images
ON user.imageID=images.imageID
ORDER BY Persons.name

But, how could I do insert into the 2 tables at once when the user upload multiple images ?

Answer 1

Use an ownership table. Basically a second table with columns id and image and allow users to each have multiple rows. Then you would retrieve all rows with the users id. This is a common approach when storing "one to many" relationships like Bob owns a pot, pan, spoon; or Bob is friends with Jim, Tom, Alex, Kim.