通过多个外键插入多个表 [英] INSERT INTO multiple tables through multiple foreign keys

问题描述

我有多个通过几个不同的外键连接在一起的表(请参见附加的图像).

I have multiple table connected together by a few different foreign keys (see attahced image).

我正在尝试插入项目表中.我一直在尝试通过下面的以下代码来完成此操作，但是它不起作用.现在，我收到一条错误消息，说client_id_fk和project_manager_id_fk都没有值.这是有道理的，因为我没有在插入中包括它们，但是它们不是自动递增的，我也不能只向这些字段添加随机整数，因为这也会引发错误.如果我将client_id_fk和project_manager_id_fk设置为NULL，从技术上讲它是可行的，但是其他表中没有数据...请帮助

I am trying to insert into the projects table. I've been trying to accomplish this through the following code below, but it's not working. Right now I am getting an error saying that neither client_id_fk, project_manager_id_fk have a value. That makes sense since I didn't include them in the insert, but aren't auto incrementing and I also can't just add a random int to those fields since that throws an error as well. It technically works if I set the client_id_fk and project_manager_id_fk to NULL, but then there's no data in the other tables...Please help

$sql1 = "INSERT INTO PROJECTS (Project_Name, StartDate) VALUES( '".$_POST["Project_Name"]."','".$_POST["StartDate"]."')";
$sql2 = "INSERT INTO CLIENTS(Client_Name, Client_Email, Client_Phone) VALUES ('".$_POST["Client_Name"]."','".$_POST["Client_Email"]."','".$_POST["Client_Phone"]."')";
$sql3 = "INSERT INTO PROJECT_MANAGERS(ProjectManager_Name,Project_Manager_Email, Project_Manager_Phone) VALUES ('".$_POST["ProjectManager_Name"]."','email', 'phone')";     
$sql4 = "INSERT INTO TYPE_OF_WORK(TypeOfWork) VALUES ('".$_POST["TypeOfWork"]."')";

推荐答案

这是错误的:

$sql1 = "INSERT INTO PROJECTS (Project_Name, StartDate) VALUES( '".$_POST["Project_Name"]."','".$_POST["StartDate"]."')";
$sql2 = "INSERT INTO CLIENTS(Client_Name, Client_Email, Client_Phone) VALUES ('".$_POST["Client_Name"]."','".$_POST["Client_Email"]."','".$_POST["Client_Phone"]."')";
$sql3 = "INSERT INTO PROJECT_MANAGERS(ProjectManager_Name,Project_Manager_Email, Project_Manager_Phone) VALUES ('".$_POST["ProjectManager_Name"]."','email', 'phone')";     
$sql4 = "INSERT INTO TYPE_OF_WORK(TypeOfWork) VALUES ('".$_POST["TypeOfWork"]."')";

因为要尝试将INSERT分为数据库中不存在的projects，client.id和project_manager.id.因此，上面的代码实际上应该是这样写的(以防您使用 mysqli ):

Because you are trying to INSERT into projects a client.id and a project_manager.id that does not exist in the database. So the above should actually be written like this (in case you use mysqli):

$link = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');

/* check connection */
if (!$link) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

// First add the client in case the table is completely empty
$stmt = mysqli_prepare($link, "INSERT INTO clients(name, email, phone) VALUES (?,?,?);");
mysqli_stmt_bind_param($stmt, 'sss', $_POST["Client_Name"], $_POST["Client_Email"], $_POST["Client_Phone"]);
/* execute prepared statement */
mysqli_stmt_execute($stmt);
/* close statement and connection */
mysqli_stmt_close($stmt);

// Then add the project_manager in case the table is completely empty
$stmt = mysqli_prepare($link, "INSERT INTO project_managers(name, email, phone) VALUES (?,?,?);");
mysqli_stmt_bind_param($stmt, 'sss', $_POST["ProjectManager_Name"], 'email', 'phone');
/* execute prepared statement */
mysqli_stmt_execute($stmt);
/* close statement and connection */
mysqli_stmt_close($stmt);

// Also add the type_of_work in case the table is completely empty
$stmt = mysqli_prepare($link, "INSERT INTO type_of_work(TypeOfWork) VALUES (?);");
mysqli_stmt_bind_param($stmt, 's', $_POST["TypeOfWork"]);
/* execute prepared statement */
mysqli_stmt_execute($stmt);
/* close statement and connection */
mysqli_stmt_close($stmt);

// Finally add your project
$stmt = mysqli_prepare($link, "INSERT INTO projects (project_name, start_date, client_id, project_manager_id) SELECT ?,?,client.id,project_managers.id FROM client,project_managers WHERE client.name = ? AND client.email = ? AND client.phone = ? AND project_managers.name = ?;");
mysqli_stmt_bind_param($stmt, 'ssssss', $_POST["Project_Name"],$_POST["StartDate"],$_POST["Client_Name"], $_POST["Client_Email"], $_POST["Client_Phone"],$_POST["ProjectManager_Name"]);
/* execute prepared statement */
mysqli_stmt_execute($stmt);
/* close statement and connection */
mysqli_stmt_close($stmt);

/* close connection */
mysqli_close($link);

这篇关于通过多个外键插入多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！