Error in inserting order of the order?

Question

Error in inserting order of the order?

Navigation

#1 by (0 votes)

0

When sending the data to complete order, only the data is inserted in the first table order but in the table order_details does not record data.

And it is addressed 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');
        }
    }
}

php mysqli

asked by Oscar 03.12.2017 в 01:46

source

1 answer

select nested in c # ajax mvc and bootstrap-select Problem when using PHPMailer library

score 0 · Accepted Answer

fetch does not work in INSERT or UPDATE , because it is used for queries that return result sets, in combination with bind_result , to get results of specific columns. In a few words, fetch is for queries of type SELECT , in which you can select columns, link them with bind_result and then read them using fetch .

In mysqli the appropriate method to handle query results of type INSERT or type UPDATE is:

affected_rows

Returns the number of rows affected by the last query INSERT , UPDATE , REPLACE or DELETE .

Return values

An integer greater than zero indicates the number of rows affected or recovered. The zero indicates that there are no records in   an update with a UPDATE statement, there are no rows that   comply with the WHERE clause of the sentence or that no query has   been executed yet. -1 indicates that the query returned an error.

affected_rows in the PHP Manual

Then, if you change:

if ($stmt->fetch()) {

Because of this:

if ($stmt->affected_rows>0){ 
    //Segundo INSERT

That way it should work.

Although I would establish more controls within the loop (code may fail, for example when creating duplicate keys ...).

It would also optimize the code in the following way:

Outside the loop, I would prepare the insert:

$stmt = $con->prepare("INSERT INTO order_details ('id_product', 'quantity', 'price', 'id_order') VALUES (?, ?, ?, ?) ");

Inside the loop, it would build all the inserts to be made. This could be done in different ways. If your tables are in InnoDB it would be best to use transactions. If you use PDO this process would be very simple. I have already stated in other answers that everything that has to do with prepared queries and several records becomes more complicated with mysqli . 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 we have prepared our insert in mass, by any means ... Outside the loop, we execute the insertion. Why do it like that? Because a single call is made to execute the insertion, not several calls. We do not know how many rows will have to be inserted into the loop. But if there are hundreds, thousands, millions ... we would be doing 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 inserts and only insert some rows and not others, because the code failed in any of the inserts ... we will have a certain number of rows inserted and others not. How will we know the rows that were inserted and the ones that did not? The consequence is an inconsistent table in the sense that it will be a very difficult task to determine exactly what insertions were made. Transactions apply the principle of all or nothing . That is, if an insert fails, the previous insertions are undone ... and an error message returns, for example, indicating that no row was inserted for the reason X.

Finally, they are only observations and it might be interesting to discuss this topic in a separate question, in order to have the opinion of several experts of this excellent community.