Problem with mysqli_multi_query, it does not run when doing multiconsults

Question

Problem with mysqli_multi_query, it does not run when doing multiconsults

Navigation

#1 by (0 votes)

3

The problem is that I do not know how to execute the third multiconsult, I did it this way, the first two queries if they are executed, but the third one no longer. Here the code:

<?php
if (isset($_POST['place_order'])) {
require 'php/lib-generadorID.php';
    $idCarrito  = mysqli_real_escape_string($ConexionBD,  generarIDCARRITO());
    $idSesion   = mysqli_real_escape_string($ConexionBD,  generarIDSESSION());
    $idVenta    = mysqli_real_escape_string($ConexionBD,  generarIDVENTA());
    $codigoVenta= mysqli_real_escape_string($ConexionBD, codigoVenta());

    $order_details = "";
    $detalle_venta = "";

        foreach ($_SESSION["shopping_cart"] as $keys => $values) {
            $sqlCart = "
                     INSERT INTO carrito (idCarrito,idCliente,idSesion,fechaHora,aux) 
                        VALUES ('$idCarrito','$idCliente','$idSesion',NOW(),'1')";

            $order_details .= "
                     INSERT INTO contenido_carrito (idCarrito,idCliente,
                            idSesion,idArticulo,codigoArticulo,codigoAlterno,
                            costo,unidades,subTotal,fechaDeRegistro,aux)
                            VALUES(
                                '$idCarrito',
                                '$idCliente',
                                '$idSesion',
                                '" . mysqli_real_escape_string($ConexionBD, $values["product_id"]) . "',
                                '" . mysqli_real_escape_string($ConexionBD, $values["product_codArticulo"]) . "',
                                '" . mysqli_real_escape_string($ConexionBD, $values["product_codAlterno"])  . "',
                                '" . mysqli_real_escape_string($ConexionBD, $values["product_price"])    . "',
                                '" . mysqli_real_escape_string($ConexionBD, $values["product_quantity"]) . "',
                                '" . mysqli_real_escape_string($ConexionBD, $values["product_quantity"]*$values["product_price"]) . "',
                                NOW(),
                               '1'
                              );";

            $detalle_venta .= "
                    INSERT INTO detalle_venta (idVenta, idArticulo, codigoArticulo,codigoAlterno,
                            unidades, precioDeVenta,subTotal,fechaDeRegistro, codigoVenta, aux)
                            VALUES(
                                '$idVenta',
                                '" . mysqli_real_escape_string($ConexionBD, $values["product_id"]) . "',
                                '" . mysqli_real_escape_string($ConexionBD, $values["product_codArticulo"]) . "',
                                '" . mysqli_real_escape_string($ConexionBD, $values["product_codAlterno"])  . "',
                                '" . mysqli_real_escape_string($ConexionBD, $values["product_quantity"])    . "',
                                '" . mysqli_real_escape_string($ConexionBD, $values["product_price"]) . "',
                                '" . mysqli_real_escape_string($ConexionBD, $values["product_quantity"]*$values["product_price"]) . "',
                                NOW(),
                                '$codigoVenta',
                               '1'
                              );";

                        mysqli_query($ConexionBD, $sqlCart); //primera consulta,insert into carrito

                    }
                    if (mysqli_multi_query($ConexionBD, $order_details) === true) {//segunda consulta,  insert into contenido_carrito

                        while (mysqli_more_results($ConexionBD) === true) {
                            if (mysqli_next_result($ConexionBD) === false) {
                                die('Error SQL: ' . mysqli_error($ConexionBD));
                            }
                        }
                    } else {
                        die('Error SQL: ' . mysqli_error($ConexionBD));
                    }
 /*
 Aqui tengo el problema, no se llega a ejecutar esta tercera consulta, y en la base de datos no se almacena nada.
 */
                    if (mysqli_multi_query($ConexionBD, $detalle_venta) === true) {//tercera consulta, insert into detalle_venta
                        while (mysqli_more_results($ConexionBD) === true) {
                            if (mysqli_next_result($ConexionBD) === false) {
                                die('Error SQL: ' . mysqli_error($ConexionBD));
                            }
                        }
                        unset($_SESSION["shopping_cart"]);
                        echo '<script>alert("You have successfully place an order...Thank you")</script>';
                        echo '<script>window.location.href="venta_refacciones.php"</script>';
                    } else {
                        die('Error SQL: ' . mysqli_error($ConexionBD));
                    }

 }//end isset
 ?>

I do not know how to do that part, can you guide me please?

mysql php mysqli

asked by Armando Bolaños 23.08.2017 в 17:19

source

1 answer

Upload a new app and sign with the Google Play App Signing service How to call a java event from Node JS?

score 0 · Accepted Answer

It is not necessary that you make a INSERT for each record that you want to add to the cart. You can use as many VALUE as you need separating each record by commas:

INSERT INTO <tabla> (
  campo1,
  campo2,
  campo3
) VALUES (
  valor1_campo1,
  valor1_campo2,
  valor1_campo3
), (
  valor2_campo1,
  valor2_campo2,
  valor2_campo3
), ( ... ), ( ... ) ...

I've improved your code slightly to fix and improve a couple of aspects of it.

To avoid calling mysqli_real_escape_string for each element of $values I used array_walk to cycle through the elements and escape them correctly.
The first query I took out of the loop to be inserted only once, not one for each item of the cart (three records would be the same or if you violate some primary / unique key you would not know it because you did not check if it was executed correctly or not).
Each field VALUE I have added it to an array so that later they can be united using as glue the comma , needed to separate each record.
I have used the same multiple SQL to make the two inserts to the different tables, so that the second multiple call is not needed.

The code would look like this:

<?php
if (isset($_POST['place_order'])) {
  require 'php/lib-generadorID.php';
  $idCarrito = mysqli_real_escape_string($ConexionBD, generarIDCARRITO());
  $idSesion = mysqli_real_escape_string($ConexionBD, generarIDSESSION());
  $idVenta = mysqli_real_escape_string($ConexionBD, generarIDVENTA());
  $codigoVenta = mysqli_real_escape_string($ConexionBD, codigoVenta());

  /* Probablemente la primera consulta haya que ejecutarla únicamente una vez */
  $sqlCart = "
    INSERT INTO carrito (
      idCarrito,
      idCliente,
      idSesion,
      fechaHora,
      aux
    ) VALUES (
      '$idCarrito',
      '$idCliente',
      '$idSesion',
      NOW(),
      '1'
    )
  ";
  if (mysqli_query($ConexionBD, $sqlCart) === false)  {
    die('Error SQL: ' . mysqli_error($ConexionBD));
  }

  /* Función de escapado para array_walk */
  function escapar_cadena(&$cadena, $conexion) {
    $cadena = mysqli_real_escape_string($conexion, $cadena);
  }

  /* Definimos los VALUES de cada tabla */
  $contenido_carrito = [];
  $detalle_venta = [];
  foreach ($_SESSION["shopping_cart"] as $keys => $values) {
    /* Escapamos todos los valores de $values */
    array_walk($values, 'escapar_cadena', $ConexionBD);
    /* Introducimos un valor para la tabla contenido_carrito */
    array_push($contenido_carrito, "
      (
        '$idCarrito',
        '$idCliente',
        '$idSesion',
        '{$values['product_id']}',
        '{$values['product_codArticulo']}',
        '{$values['product_codAlterno']}',
        '{$values['product_price']}',
        '{$values['product_quantity']}',
        '{$values['product_quantity']}' * '{$values['product_price']}',
        NOW(),
        '1'
      )
    ");
    /* Introducimos un valor para la tabla detalle_venta */
    array_push($detalle_venta, "
      (
        '$idVenta',
        '{$values['product_id']}',
        '{$values['product_codArticulo']}',
        '{$values['product_codAlterno']}',
        '{$values['product_quantity']}',
        '{$values['product_price']}',
        '{$values['product_quantity']}' * '{$values['product_price']}',
        NOW(),
        '$codigoVenta',
        '1'
      )
    ");
  }
  /* Montamos la consulta agregando al final todos los VALUEs */
  $sql = '';
  if (count($contenido_carrito) > 0) {
    $sql .= '
      INSERT INTO contenido_carrito (
        idCarrito,
        idCliente,
        idSesion,
        idArticulo,
        codigoArticulo,
        codigoAlterno,
        costo,
        unidades,
        subTotal,
        fechaDeRegistro,
        aux
      ) VALUES ' . implode(', ', $contenido_carrito) .';';
  }
  if (count($detalle_venta) > 0) {
    $sql .= '
      INSERT INTO detalle_venta (
        idVenta,
        idArticulo,
        codigoArticulo,
        codigoAlterno,
        unidades,
        precioDeVenta,
        subTotal,
        fechaDeRegistro,
        codigoVenta,
        aux
      ) VALUES ' . implode(', ', $detalle_venta) .';';
  }
  /* De una tacada insertamos primero el carrito y luego los detalles */
  if (strlen($sql) > 0 && mysqli_multi_query($ConexionBD, $sql) === true) {
    while (mysqli_more_results($ConexionBD) === true) {
      if (mysqli_next_result($ConexionBD) === false) {
        die('Error SQL: ' . mysqli_error($ConexionBD));
      }
    }
  } else {
    die('Error SQL: ' . mysqli_error($ConexionBD));
  }
}//end isset
?>