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

Question

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

Wrap the statements in a $conn->BeginTransaction() and $conn->Commit(). Use a try and catch block to catch an error and $conn->Rollback() within the catch — TVA van Hesteren, Jul 23 '17 at 21:50

score 4 · Accepted Answer · answered Jul 23 '17 at 21:53

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

Accountant م · Answer 2 · 2017-07-23T22:10:57.080

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).

Thanks for your reply, I already used your second approach but as you said it is not guaranteed and it is not working as I have been expecting, for this do you recommend me to use the first approach? — Khorland, Jul 24 '17 at 20:14
@Khorland The second approach(locked tables) is used when you want to make a transaction while your tables are locked *in the same time* . if you need to lock your tables and do a transaction you **must** go with the second approach . .. what I meant by *not guaranteed* is that if you did not rollback and did not commit, the transaction will be rolled-backed(this is not guaranteed) **you better issue a rollback query** — Accountant م, Jul 25 '17 at 08:52

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

2 Answers2

LOCKING TABLES ?

Linked