SQL:将值插入外键

时间:2023-01-14 00:20:56

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

我正在创建一个网站,我正在使用phpmyadmin,我有一个关于将值插入到外键的问题。

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

当事件表中的事件col为值x时,我可以选择一个图像表id,即…

$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?

-Im尝试将图像插入到images表中,将一个新的事件插入到event表中,使这个新的图像属于这个新的事件。-当向已有事件插入图像时,如何插入工作?

Any help would be good thank you.

任何帮助都可以,谢谢。

3 个解决方案

#1


1  

Use two separate insert statements wrapped in a transaction.

使用封装在事务中的两个独立的insert语句。

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

要为已经存在的事件插入图像,只需使用图像的insert语句。

#2


2  

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.

您应该首先插入事件,如果Ok,则插入图像,如果有的话。不知道该怎么做,但我认为您应该检查是否需要插入或更新。

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.

阅读一些关于事务和隔离级别的内容,以便更好地理解这一点。

#3


1  

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

无效的SQL语法。在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).

插入事件,然后插入图像。如果您希望确保原子性(确保两者都成功或不插入寄存器),请使用swarFish所说的事务。

#1


1  

Use two separate insert statements wrapped in a transaction.

使用封装在事务中的两个独立的insert语句。

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

要为已经存在的事件插入图像,只需使用图像的insert语句。

#2


2  

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.

您应该首先插入事件,如果Ok,则插入图像,如果有的话。不知道该怎么做,但我认为您应该检查是否需要插入或更新。

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.

阅读一些关于事务和隔离级别的内容,以便更好地理解这一点。

#3


1  

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

无效的SQL语法。在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).

插入事件,然后插入图像。如果您希望确保原子性(确保两者都成功或不插入寄存器),请使用swarFish所说的事务。