php - MySQLi中如何使用prepare语句将数据插入到三个不同的表中并考虑事务

什么是同时执行以下SQL语句的最完美和最安全的方法，考虑到MySQLi中的事务，以便将数据添加到所有表中或在添加过程发生故障时需要回滚数据 table 。

$conn = new mysqli(DBHOST, DBUSER, DBPASS, DBNAME);

 $stmt1 = $conn->prepare("INSERT INTO stdHouseholder (usersID, parentJob, phoneNumber,address) VALUES (?, ?, ?, ?)");
$stmt1->bind_param("ssss", $userId, $parentJob, $phoneB, $addressB);

$stmt2 = $conn->prepare("INSERT INTO stdConfirmInfo (usersID, commitment, credentials, haveOfficialLetter) VALUES (?, ?, ?, ?)");
$stmt2->bind_param("ssss", $userId, $commitment, $credentials, $NamesEnglish);

 $stmt3 = $conn->prepare("INSERT INTO users_roleTB (usersID, role_id) VALUES (?, ?)");
$stmt3->bind_param("ss", $userId, $role_id);

最佳答案

您可以使用 mysqli 命令的 begin transaction 、 commit 和 rollback 功能来帮助您。

您需要启动一个事务，检查每个插入查询的结果，然后提交(如果它们都执行良好)或回滚(如果它们没有执行):

<?php

$conn = new mysqli(DBHOST, DBUSER, DBPASS, DBNAME);

$stmt1 = $conn->prepare("INSERT INTO stdHouseholder (usersID, parentJob, phoneNumber,address) VALUES (?, ?, ?, ?)");
$stmt1->bind_param("ssss", $userId, $parentJob, $phoneB, $addressB);

$stmt2 = $conn->prepare("INSERT INTO stdConfirmInfo (usersID, commitment, credentials, haveOfficialLetter) VALUES (?, ?, ?, ?)");
$stmt2->bind_param("ssss", $userId, $commitment, $credentials, $NamesEnglish);

$stmt3 = $conn->prepare("INSERT INTO users_roleTB (usersID, role_id) VALUES (?, ?)");
$stmt3->bind_param("ss", $userId, $role_id);

$conn->begin_transaction();
if ($stmt1->execute() && $stmt2->execute() && $stmt3->execute()) {
    $conn->commit();
} else {
    $conn->rollback();
}

$conn->close();

关于php - MySQLi中如何使用prepare语句将数据插入到三个不同的表中并考虑事务，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45270154/