Error calculating sum with PHP and MySQL and then insert it into another table [closed]

Question

Error calculating sum with PHP and MySQL and then insert it into another table [closed]

Navigation

#1 by (1 votes)

0

This way I send the form with JS AJAX to PHP:

$(document).ready(function() {
  formSubmit()
})

function formSubmit(){

  $('#formComi').submit(function(e){
    e.preventDefault()

    var idusuario = $('#idusuario').val()
    var desde = $('#fechadesde').val()
    var hasta = $('#fechahasta').val()

    var data = 'idusuario='+idusuario+'&fechadesde='+desde+'&fechahasta='+hasta;
    $.ajax({
      url: 'phps/procesarcomisiones.php',
      type: 'post',
      data: data,
      beforeSend: function(){
        console.log('enviando datos')
      },
      success: function(resp){
        console.log('resp')
      }
    })

  })

Here is what I process in the MySQL query in PHP:

<?php
require ('conexion.php');

$vendedor = $_POST["idusuario"];
$desde = $_POST["fechadesde"];
$hasta = $_POST["fechahasta"];

$query = "SELECT * from venta where idusuario = '$vendedor' AND (fecha BETWEEN '$desde' AND '$hasta');";
$resultado = $mysqli->query($query);

$suma=0;
while ($sumar=mysqli_fetch_array($resultado)) {
    $suma=$suma+$sumar["total"]*0.015;
}

$comision = "INSERT INTO comision (idusuario, fecha_comision, total_comision, desde, hasta)
VALUES ('$vendedor', CURDATE(), '$suma', ' $desde'.''$hasta' )";

if ($mysqli->query($comision) === TRUE){
    echo "registro guardado";
}else {
    echo "error al guardar";
}
?>

mysql php ajax

asked by tato165 05.05.2017 в 06:28

source

1 answer

CodeIgniter - Array to String conversion error Sum in real time Textbox

score 1 · Answer 1

You have a syntax error in the SQL query that you use to insert the data in the table where you put ' $desde'.''$hasta' . Maybe I should put '$desde', '$hasta' , but I preferred to show you how to do it correctly.

Using prepared queries means adding a bit more code, but it makes it easier for you to read the code, debug it and avoid SQL injection attacks.

Here is an example of how to do your homework using prepared queries (below I suggest some changes to the JavaScript code):

<?php
require('conexion.php');

$consulta = $mysqli->prepare("
    SELECT
        SUM(total) * 0.015 suma_total
    FROM venta
    WHERE
        idusuario = ?
        AND fecha BETWEEN ? AND ?
");
if ($consulta === false) {
    die('Error en la consulta');
}
/* Cargamos los datos y ejecutamos la consulta */
$consulta->bind_param("i", $_POST["idusuario"]);
$consulta->bind_param("s", $_POST["fechadesde"]);
$consulta->bind_param("s", $_POST["fechahasta"]);
if ($consulta->execute() === false) {
    die($consulta->error);
}
/* Obtenemos el resultado y obtenemos el primer registro con fetch_assoc */
$resultado = $consulta->get_result();
if ($resultado === false) {
    die("Error al obtener el resultado: ". $consulta->errno);
}
$datos = $resultado->fetch_assoc();
if ($datos === false) {
    die("¿No hay datos? (!)");
}
$suma = $datos['suma_total'];
/* Preparamos la consulta de introducción de datos */
$consulta = $mysqli->query("
    INSERT INTO comision (
        idusuario,
        fecha_comision,
        total_comision,
        desde,
        hasta
    ) VALUES (
        ?,
        CURDATE(),
        ?,
        ?,
        ?
    )
");
/* Cargamos los datos y ejecutamos la consulta */
$consulta->bind_param("i", $_POST["idusuario"]);
$consulta->bind_param("i", $suma);
$consulta->bind_param("s", $_POST["fechadesde"]);
$consulta->bind_param("s", $_POST["fechahasta"]);
if ($consulta->execute() === false) {
    die($consulta->error);
}
echo "registro guardado";

I suggest the following changes to your JavaScript code:

ALWAYS use the end of judgment indicator ; .
If you have already defined a function called formSubmit use it as a parameter of ready and do not create a function to call a function.
It is better to send data in json to PHP than to mount a string that could suffer problems in the interpretation due to badly escaped URL characters.
In the event success sends the content of the variable resp to the console and not a text string that sets "resp" .

Here are the modifications:

function formSubmit() {
  $('#formComi').submit(function(e) {
    e.preventDefault();
    $.ajax({
      url: 'phps/procesarcomisiones.php',
      type: 'post',
      data: {
        'idusuario': $('#idusuario').val(),
        'fechadesde': $('#fechadesde').val(),
        'fechahasta': $('#fechahasta').val()
      },
      beforeSend: function() {
        console.log('enviando datos');
      },
      success: function(resp) {
        console.log(resp);
      }
    });
  });
}
$(document).ready(formSubmit);