如何使用外键php [MariadB]自动将数据自动添加到表中

Question

我如何使用PHP向外国添加数据，以使IDS匹配：

在此示例中，我有一个用于"成员"的表和"电子邮件"的单独表。成员可以有多个电子邮件，因此创建了一个单独的表格，将其以成员ID作为外键存储电子邮件。

在提交字段时，如何编写将数据插入两个表的代码？

我最近（不成功）尝试：

php块：

$query=
"INSERT INTO Members(FIRST_NAME, LAST_NAME, TITLE, INSTITUTION) VALUES ('$first_name',
'$last_name','$title','$institution');
INSERT INTO EMAIL(Members_ID, EMAIL) VALUES
(LAST_INSERT_ID(), '$email');
INSERT INTO WEBSITE(Members_ID, WEBSITE) VALUES
(LAST_INSERT_ID(), '$website');";
mysqli_query($connection,$query) or die(mysqli_error($connection));

SQL块：

CREATE TABLE Members(
    ID  INT  UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    FIRST_NAME  TEXT(16),
    LAST_NAME  TEXT(16,
    TITLE  TEXT(7), /** 7 CHARS for  'Student'*/
    INSTITUTION  VARCHAR(2048),
    PRIMARY KEY(ID)
  );
  CREATE TABLE EMAIL(
    Members_ID  INT  UNSIGNED NOT NULL UNIQUE,
    EMAIL  VARCHAR(2048),
    FOREIGN KEY (Members_ID) REFERENCES Members(ID)
  );
  CREATE TABLE WEBSITE(
    Members_ID  INT  UNSIGNED NOT NULL UNIQUE,
    WEBSITE  VARCHAR(2048),
    FOREIGN KEY (Members_ID) REFERENCES Members(ID)
  );

Answer 1

您可以使用，例如mysqli_multi_query，在检索了分配给第一个插入的ID之后（在这种情况下我不会太依赖LAST_INSERT_ID()）：

>

$conn = mysqli_connect(...);
$query = "INSERT INTO Members (FIRST_NAME,LAST_NAME,TITLE,INSTITUTION) VALUES ('$first_name','$last_name','$title','$institution')";
mysqli_query($conn, $query);
$lid = mysqli_insert_id($conn);   
$query  = "INSERT INTO EMAIL (Members_ID,EMAIL) VALUES ($lid,'$email');";
$query .= "INSERT INTO WEBSITE (Members_ID,WEBSITE) VALUES ($lid,'$website');";
mysqli_multi_query($conn, $query);