I get in the order.php page the data verification, and in the updateorder.php I save the purchase data, it corresponds to the following code:
session_start();
require "conexion.php";
$formid = isset($_SESSION['formid']) ? $_SESSION['formid'] : "";
if ($formid != $_POST['formid']) {
echo "E00001 !! ERROR DE SESIÓN REINTENTAR OTRA VEZ.";
} else {
unset($_SESSION['formid']);
if ($_POST) {
$id_user = $_POST['id_user'];
$status = $_POST['status'];
$stmt = $con->prepare("INSERT INTO order (`id_user`, `status`) VALUES (?, ?) ");
$stmt->bind_param('is', $id_user,$status);
$stmt->execute();
if ($stmt->affected_rows>0){
$order_id = $stmt->insert_id;
for ($i = 0; $i < count($_POST['qty']); $i++) {
$quantity = $_POST['qty'][$i];
$price = $_POST['price'][$i];
$id_product = $_POST['id_product'][$i];
$stmt = $con->prepare("INSERT INTO order_details (`id_product`, `quantity`, `price`, `id_order`) VALUES (?, ?, ?, ?) ");
$stmt->bind_param('iisi', $id_product,$quantity,$price,$order_id);
$stmt->execute();
}
unset($_SESSION['cart']);
unset($_SESSION['qty']);
header('location:index.php?a=order');
}else{
$stmt->close();
header('location:index.php?a=orderfail');
}
}
}
I keep the data of the purchase made, what the id
user is, the price of the product with their respective id
status of the purchase, among others.
Although the code works correctly without errors...
I have been told that it is better to use more controls inside the bucle
and that possibly (the code may fail, for example when creating duplicate keys...).
As another additional note it is said that it is not good to launch a
insert
within abucle
and that it stays halfway due to some mistake.
Although there are no failures, it is worrying to know that there may be possible failures.
Another point: It is recommended to prepare the insert, creating all the
insert
and making a singleexecute
outside of thebucle
.
So my question is:
How to do multiple inserts in a row using MySQLi prepared statements correctly?
For more cleanliness, I would recommend using different variables for the first and subsequent statements. (
$stmt1
and$stmt2
, I put them)Second, for loop insertion, you can prepare the statement once and only execute within it.
You could also encapsulate the insert loop in a transaction
Now in this code you are not catching exceptions. We don't know if your statements throw PHP errors or MySQLI exceptions in case of failure.
you would have to put
to the code to ensure that exceptions are thrown and then handle them by putting, for example:
Also the first insert should be wrapped in a try/catch block.
I have two questions left. First, at the beginning you say that
(there are spaces left over, but I guess it's a typo). Then in the nested inserts you put:
So
$_POST['id_product']
is it a scalar or an array?Second, in case the first insert doesn't deliver affected rows, you're doing
$stmt->close();
What's the purpose? That command will close an open cursor that hasn't finished traversing, but after oneINSERT
it's too much. You probably wanted to put$con->close()
but in reality this would not be strictly necessary either because of the way PHP works with sessions to DBs.