php - 如果不为空，则从重复的文本字段插入数据库 php sql

Question

我有一个包含 3 个表的数据库 - 作者、研究和案例研究。

作者有列 AuthorId(自动递增，PK)、F_name、L_name、Email , 联系方式。

Study 包含列 StudyID(自动递增，PK)、Title、Summary

Casestudy - AuthorId(FK，引用Author.AuthorId)，StudyId(FK，引用 Study.StudyId)，已提交(日期时间)。

我有一个语句将数据插入到每个表中(获取 AuthorId 和 StudyID 的 ID)并插入到 casestudy 中。

我的问题是，如果只有一位作者，我该如何避免将第二位作者的数据传递到数据库中。否则它只存储空行。作者网络表单示例 https://jsfiddle.net/7un2yf9g/ .它复制整个容器并创建一个具有不同名称的相同副本。

我的代码如下:

$AuthorTitle = mysqli_real_escape_string($con, ($_POST['AuthorTitle']));
$AuthorTitle = mysqli_real_escape_string($con, ($_POST['AuthorTitle_ID2']));
$F_Name = mysqli_real_escape_string($con, ($_POST['first_name']));
$F_Name = mysqli_real_escape_string($con, ($_POST['first_name_ID2']));
$L_Name = mysqli_real_escape_string($con, ($_POST['last_name']));
$L_Name = mysqli_real_escape_string($con, ($_POST['last_name_ID2']));
$Email = mysqli_real_escape_string($con, ($_POST['email']));
$Email = mysqli_real_escape_string($con, ($_POST['email_ID2']));
$Contactauthor = mysqli_real_escape_string($con, ($_POST['contact']));
$Contactauthor = mysqli_real_escape_string($con, ($_POST['contact_ID2']));
$Title = mysqli_real_escape_string($con, ($_POST['Title']));
$Summary = mysqli_real_escape_string($con, ($_POST['Summary']));

mysqli_multi_query($con,"
START TRANSACTION;
    INSERT INTO author(AuthorTitle, F_Name, L_Name, Email, Contact)  
    VALUES('$AuthorTitle','$F_Name','$L_Name','$Email','$Contactauthor'), ('$AuthorTitle_ID2','$F_Name_ID2','$L_Name_ID2','$Email_ID2','$Contactauthor_ID2');
    SET @AuthorId = LAST_INSERT_ID();
    INSERT INTO study(Title, Summary) VALUES('$Title','$Summary');
    SET @StudyId = LAST_INSERT_ID();
    INSERT INTO casestudy(AuthorId, StudyId, Submitted) VALUES(@AuthorId, @StudyId, NOW());
COMMIT;
");

Answer 1

首先，您在 Author_ID2 从未定义时定义变量的方式:

$AuthorTitle = mysqli_real_escape_string($con, ($_POST['AuthorTitle']));
$AuthorTitle = mysqli_real_escape_string($con, ($_POST['AuthorTitle_ID2']));

在这里，您只是用 $_POST['AuthorTitle_ID2'] 中的值覆盖了 $AuthorTitle，根本没有定义 $AuthorTitle_ID2 .

接下来，您拥有的 SQL 语句只会将第二个 author 与 casestudy 中的 study 链接起来，因为 LAST_INSERT_ID() 只会返回最后插入的记录的 ID，即使一条语句实际上插入了多条记录。

最后，有 IF statement in MySQL :

$AuthorTitle       = mysqli_real_escape_string($con, ($_POST['AuthorTitle']));
$AuthorTitle_ID2   = mysqli_real_escape_string($con, ($_POST['AuthorTitle_ID2']));
$F_Name            = mysqli_real_escape_string($con, ($_POST['first_name']));
$F_Name_ID2        = mysqli_real_escape_string($con, ($_POST['first_name_ID2']));
$L_Name            = mysqli_real_escape_string($con, ($_POST['last_name']));
$L_Name_ID2        = mysqli_real_escape_string($con, ($_POST['last_name_ID2']));
$Email             = mysqli_real_escape_string($con, ($_POST['email']));
$Email_ID2         = mysqli_real_escape_string($con, ($_POST['email_ID2']));
$Contactauthor     = mysqli_real_escape_string($con, ($_POST['contact']));
$Contactauthor_ID2 = mysqli_real_escape_string($con, ($_POST['contact_ID2']));
$Title             = mysqli_real_escape_string($con, ($_POST['Title']));
$Summary           = mysqli_real_escape_string($con, ($_POST['Summary']));

mysqli_multi_query($con,"
START TRANSACTION;
    INSERT INTO study(Title, Summary)
    VALUES('$Title','$Summary');
    SET @StudyId = LAST_INSERT_ID();

    INSERT INTO author(AuthorTitle, F_Name, L_Name, Email, Contact)
    VALUES('$AuthorTitle','$F_Name','$L_Name','$Email','$Contactauthor');
    SET @AuthorId = LAST_INSERT_ID();

    INSERT INTO casestudy(AuthorId, StudyId, Submitted)
    VALUES(@AuthorId, @StudyId, NOW());

    IF ('$AuthorTitle_ID2' <> '' OR '$F_Name_ID2' <> '' OR '$L_Name_ID2' <> '' OR '$Email_ID2' <> '' OR '$Contactauthor_ID2' <> '') THEN
        INSERT INTO author(AuthorTitle, F_Name, L_Name, Email, Contact)
        VALUES('$AuthorTitle_ID2','$F_Name_ID2','$L_Name_ID2','$Email_ID2','$Contactauthor_ID2');
        SET @AuthorId = LAST_INSERT_ID();

        INSERT INTO casestudy(AuthorId, StudyId, Submitted)
        VALUES(@AuthorId, @StudyId, NOW());
    END IF;
COMMIT;
");