CODEWITHSUNDEEP
phpmysqli
Khorland
Khorland

Reputation: 43

How to Use prepare statement to insert data to three different tables in MySQLi with consideration of transaction

What is the perfect and safest way to execute the following SQL statements simultaneously, with consideration of transaction in MySQLi in order the data to be added to all tables or the data needs to be rolled back when a failure happens to the adding process of one on the tables.

$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);

Upvotes: 4

Views: 891

Answers (2)

Accountant م
Accountant م

Reputation: 7493

You can't insert to multiple tables in one statement

you will have to put them in a transaction

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

$conn->query("BEGIN;");
$failed = false;
$stmt1 = $conn->prepare("INSERT INTO stdHouseholder (usersID, parentJob, phoneNumber,address) VALUES (?, ?, ?, ?)");
$stmt1->bind_param("ssss", $userId, $parentJob, $phoneB, $addressB);
if (!$stmt2->execute()) {
    $failed = true;
    $conn->query("ROLLBACK;");
}
if(!$failed){
    $stmt2 = $conn->prepare("INSERT INTO stdConfirmInfo (usersID, commitment, credentials, haveOfficialLetter) VALUES (?, ?, ?, ?)");
    $stmt2->bind_param("ssss", $userId, $commitment, $credentials, $NamesEnglish);
     if (!$stmt2->execute()) {
         $failed = true;
         $conn->query("ROLLBACK;");
     }
}
if(!$failed){
    $stmt3 = $conn->prepare("INSERT INTO users_roleTB (usersID, role_id) VALUES (?, ?)");
    $stmt3->bind_param("ss", $userId, $role_id);
    if (!$stmt3->execute()) {
        $failed = true;
        $conn->query("ROLLBACK;");
    }
}
if(!$failed){
    $conn->query("COMMIT;");
}

LOCKING TABLES ?

If you want to make a transaction while locking your tables , you are going to need another approach ,because locking a table is going to commit any running transaction, and this is horrifying.

In this case you are going to start the transaction by turning off the auto commit feature of mysql

SET autocommit=0;

Then you lock your tables

LOCK TABLES stdHouseholder WRITE, stdConfirmInfo WRITE, users_roleTB WRITE;

Then run your prepared statements normally

$stmt->execute();

Finally, if the statements succeed then you commit the transaction , and turn the auto commit on again.

 $conn->query("COMMIT;");
 $conn->query("SET autocommit=1;");

Note that if you didn't commit (and didn't roll back) the transaction will be rolled-back when the session ends (but this is not guaranteed).

Upvotes: 0

xsist10
xsist10

Reputation: 3051

You can use the begin transaction, commit and rollback features of the mysqli commands to assist with you.

You'll want to start a transaction, check the result of each insert query and then commit (if they all performed well) or rollback if they didn't:

<?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();

Upvotes: 4

Related Questions