How to run multiple INSERT queries in a transaction and use insert id?

Question

I need to insert data into 3 tables and need to get the id of last inserted query into shopper table. I know this is doable by running

$conn -> insert_id;

in a single query but in my case I need to create a transaction with rollback in case of any failure. something like

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

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

$stmt2 = $conn->prepare("INSERT INTO shipment (shipmentID, usersID,..) VALUES (?, ?, ?, ?)");
$stmt2->bind_param("ssss", $userId, ...);

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

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

$conn->close();

As you can see I am trying to pass last inserted usersID as Foreign Key into shipment and address tables. so how can I do this when committing all of them together like

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

Answer 1

Exceptions offer enormous help with transactions. Hence configure mysqli to throw exceptions. Not only for transactions but because it's the only proper way to report errors in general.

With exceptions your code will be plain and simple

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli(DBHOST, DBUSER, DBPASS, DBNAME);
$conn->set_charset('utf8mb4');

$conn->begin_transaction();

$stmt = $conn->prepare("INSERT INTO shopper (usersID, parentJob, phoneNumber,address) VALUES (null, ?, ?, ?)");
$stmt->bind_param("sss", $parentJob, $phoneB, $addressB);
$stmt->execute();
$userId = $conn->insert_id;

$stmt = $conn->prepare("INSERT INTO shipment (shipmentID, usersID,..) VALUES (?, ?, ?, ?)");
$stmt->bind_param("ssss", $userId, ...);
$stmt->execute();

$stmt = $conn->prepare("INSERT INTO address (addressID, usersID, ...) VALUES (?, ?, ?, ?)");
$stmt->bind_param("ss", $userId, ...);
$stmt->execute();

$conn->commit();

in case of error an exception will be thrown and a transaction will be rolled back automatically.