When sending the order completion data, only the data is inserted in the first table order
but order_details
no data is recorded in the table.
and is directed to/index.php?a=orderfail
The structure of my table is as follows:
order
id_order id_product id_user status
1 3 2 complete
order_details
id_order_product id_product quantity price id_order
1 3 1 10.00 1
My code: updateorder.php
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_product = $_POST['id_product '];
$id_user = $_POST['id_user'];
$status = $_POST['status'];
$stmt = $con->prepare("INSERT INTO order (`id_product`, `id_user`, `status`) VALUES (?, ?, ?) ");
$stmt->bind_param('iis', $id_product,$id_user,$status);
$stmt->execute();
if ($stmt->fetch()) {
$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');
}
}
}
fetch
does not work onINSERT
orUPDATE
, because it is used for queries that return result sets, in combination withbind_result
, to get results from specific columns. In a nutshell,fetch
it's for queries of the typeSELECT
, in which you can select columns, bind to them,bind_result
and then read them usingfetch
.In
mysqli
the proper method to handle query results of the typeINSERT
or the typeUPDATE
is :So if you change:
if ($stmt->fetch()) {
For this:
That's how it should work.
Although I would set more controls inside the loop (maybe the code fails, for example when creating duplicate keys...).
I would also optimize the code as follows:
Outside of the loop, I would prepare the insert:
Inside the loop it would build all the inserts to perform. This could be done in different ways. If your tables are in
InnoDB
the best would be to use transactions. If you use PDO this process would be very simple. I've already stated in other answers that anything to do with prepared queries and multiple records is made more complicated withmysqli
. However, it is a technology that is there and you have to find ways to do things for those who persist in using this API.Once our mass insertion is prepared, by any means... Outside the loop we execute the insertion. Why do it like this? Because a single call is made to execute the insert, not multiple calls. We don't know how many rows will have to be inserted inside the loop. But if there are hundreds, thousands, millions... we would be making hundreds, thousands, millions of
execute
, when we can use only one.In point 2 I talked about Transactions. In general, it is advisable to use them when trying to insert multiple rows. Because if, for example, we need to make certain insertions and only some rows are inserted and others are not, because the code failed in one of the insertions... we are going to have a certain number of rows inserted and others not. How are we going to know which rows were inserted and which were not? The consequence is an inconsistent table in the sense that it will be a very difficult task to determine exactly which inserts were made. Transactions apply the all or nothing principle. That is, if any insertion fails, the previous insertions are undone... and an error message is returned, for example, indicating that no row was inserted for reason X.
Anyway, these are just observations and it may be interesting to address this issue in a separate question, in order to have the opinion of various experts from this excellent community.