CODEWITHSUNDEEP
phpmysqldatabasemysqli
Aditya Amit
Aditya Amit

Reputation: 71

What is best way to insert Master/Detail records using PHP-MySQL

I have a Sales Order Master (so_mstr) table and a child table Sales Order Details (sod_det). I am receiving the detail records from $_POST[] array and am preparing the master record inside the script itself. My script is expected to work like following.

  1. Insert Master Record.
  2. Insert Detail Records using a PHP for loop (a query for each detail record.)
  3. Update another table upon success of Step 1 & 2.

The entire process should roll-back in case any of the steps failed.

I have tried the following code so far. It continues to insert detail records even if master insert query fails. Any idea how to ensure that all the queries run successfully or roll back entirely if any of them fail? 

mysqli_autocommit($conn, FALSE);
mysqli_begin_transaction();

$insert_mstr = 'insert into so_mstr(...) values(...)';
mysqli_query($conn, $insert_mstr);

foreach ($order_details['order_details'] as $line_item) {
    $insert_line = 'INSERT INTO sod_det(...) values(...)';
    mysqli_query($conn, $insert_line);
}

if(mysqli_commit($conn)){
    insert_ar_reco(); // inserts into another table
    increment_soseq($conn, $param); // updates a table
}
else{
    echo 'Error occurred! transaction rolled back.';
}

Upvotes: 0

Views: 2226

Answers (2)

Bhavin Solanki
Bhavin Solanki

Reputation: 1364

You can use below way to use rollback and commit functionality.

mysqli_query($con,"SET AUTOCOMMIT=0");
mysqli_query($con,"START TRANSACTION");
$insertMain = mysqli_query($con, '');

// Insert query for Sales Order Master (so_mstr) table foreach(($order_details['order_details'] as $line_item){ $insertChild = mysqli_query($con,''); // Insert query for Sales Order Details (sod_det) table if(!$insertChild){ break; } }

if($insert1 && $insert2) {
    mysqli_query($con,"COMMIT");
} else {
    mysqli_query($con,"ROLLBACK");
}
mysqli_query($con,"SET AUTOCOMMIT = 1" );

update: if you are using mysqli the objectorientated way, you can do the following:

$mysqli->begin_transaction();

$insertMain = $mysqli->query(''); // Insert query for Sales Order Master (so_mstr) table
foreach(($order_details['order_details'] as $line_item) {
   $insertChild = $mysqli->query(''); // Insert query for Sales Order Details (sod_det) table
   if(!$insertChild){
       break;
   }
}

if($insert1 && $insert2) {
    $mysqli->commit();
} else {
    $mysqli->rollback();
}

HINT: if you use an older PHP version as 5.5.0, you can't use $mysqli->begin_transaction(); and have to use $mysqli->autocommit(false); at the begining and $mysqli->autocommit(true); at the end instead

Upvotes: 0

AGK
AGK

Reputation: 86

Use "batch insert" along with "commit" and "rollback"

Upvotes: -1

Related Questions