How can I apply MySQL transactions feature using php with it?

Question

I am trying to build a sale invoice page that takes multiple products as input and also some information about the customer. It interacts with four MySQL tables (sales, sale_details, posting, and product_inventory). You'll get the idea of what will happen if a query fails. I want to avoid this. For this purpose I am trying to implement transactions feature of the InnoDB database engine using PHP and MySQL (PHPMyAdmin). Another problem is I've heard that autocommit is turned off by default do I need to turn it ON and then OFF every time I fire a query? Here is my PHP code snippet:

  $sal_date = trim($_POST["sale_date"]);
  $cust_id = trim($_POST["cust_id"]);
  $book_no = trim($_POST["sal_book_no"]);
  $rem = $_POST["sal_remarks"];
  $st = trim($_POST["sub_total"]);
  $disc = floatval($_POST["total_disc"]) + floatval($_POST["adj_disc"]);
  $total = trim($_POST["grand_total"]);

  $query1 = mysqli_query($link, "INSERT INTO sales
  (cust_id, book_no, sale_date, sub_total, discount, total, remarks)
  VALUES ('$cust_id', '$book_no', '$sal_date', '$st', '$disc', '$total', '$rem')") or die(mysqli_error($link));

  $query2 = mysqli_query($link, "SELECT LAST_INSERT_ID() as last_row") or die(mysqli_error($link));
  $sal_id = mysqli_fetch_array($query2);
  $sal = intval($sal_id["last_row"]);

  $the_query1 = mysqli_query($link, "INSERT INTO `posting`(`type`, `account_id`, `tr_id`, `tr_date`, `debit`) VALUES ('SI','$cust_id','$sal', '$sal_date', '$total')") or die(mysqli_error($link));
  $the_query2 = mysqli_query($link, "INSERT INTO `posting`(`type`, `account_id`, `tr_id`, `tr_date`, `credit`) VALUES ('SI','10002','$sal', '$sal_date', '$total')") or die(mysqli_error($link));

  for($count=0; $count<$_POST["total_item"]; $count++)
  {
    $prod_id = floatval(trim($_POST["product_name"][$count]));
    $quantity = floatval(trim($_POST["qty"][$count]));
    $disc = floatval(trim($_POST["disc"][$count]));

    $query3 = mysqli_query($link, "INSERT INTO sale_details (sal_id, prod_id, quantity, discount) VALUES ('$sal', '$prod_id', '$quantity', '$disc')") or die(mysqli_error($link));
    $query4 = mysqli_query($link, "INSERT INTO product_inventory (invoice_id, product_id, qty_out, in_date) VALUES ('$sal', '$prod_id', '$quantity', '$sal_date')") or die(mysqli_error($link));
  }

I'll appreciate your suggestions.

Answer 1

You can use this method

mysqli_query($db, "START TRANSACTION");

$query1 = mysqli_query($db, "Query 1");
$query2 = mysqli_query($db, "Query 2");
$query3 = mysqli_query($db, "Query 3");

if($query1 && $query2 && $query3) {
    mysqli_query($db, "COMMIT");
} else {
    mysqli_query($db, "ROLLBACK");
}