SQL: insert value into foreign key

Question

Hello,

Im using phpmyadmin for a website i'm creating and have a question about a inserting values into foreign key.

Im able to select an image table id when event col in events table is value x i.e...

$event = "Partay";

$sql = "SELECT i.ImageID
FROM images i 
INNER JOIN events e 
ON i.eventID = e.eventID
WHERE e.event ='".$event."'";

This works for selecting images to display. However when i want to insert and image inner join doesnt seem to work the same.

$sql = "INSERT INTO images i (i.image, e.event) 
INNER JOIN event e 
ON i.eventID = e.eventID
VALUES ('".$content."','".$event."')";

-Im trying to insert image into images table and a new event into event table so that this new image belongs to this new event. -Also how would inserting work when inserting image with an existing event?

Any help would be good thank you.

Answer 1

Use two separate insert statements wrapped in a transaction.

To insert an image for an event which already exists, just use the insert statement for the image.

Answer 2

Invalid SQL Syntax. There in no way to do it in one instruction in SQL.

Insert in event, then insert in images. Use a transaction like swarFish said if you want to ensure atomicity (ensure that both are successful or no register is inserted).

Answer 3

You should INSERT first the event, if Ok, then INSERT the image if it has any. Don't know, how you want to do that, but I think you should check if there is an insert or update that you want.

In both situation you should use a transaction to ensure that you insert all that you want or not at all. (Event and Image in this case).

Read something about transaction and isolation level to understand this better.