在一个查询中插入多行的SUM

Question

I need to know how can I made a SUM with a multiple rows insert in one query.

I need to sum the value that is already in the table with the new one in the invoice.

Here is my query (MySql):

        $conn->beginTransaction();
        $sql = "INSERT INTO PRODUCTOS
        (cod, nombreProd, proveedor, existencia, comprado, compra, id_user, nombre, ref_compra, f_compra)
         VALUES ";
        $insertQuery = array();
        $insertData = array();
        foreach ($_POST['cod'] as $i => $cod) {
            $insertQuery[] = '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
            $insertData[] = $_POST['cod'][$i];
            $insertData[] = $_POST['nombreProd'][$i];
            $insertData[] = $_POST['proveedor'][$i];
            $insertData[] = $_POST['existencia'][$i];
            $insertData[] = $_POST['comprado'][$i];
            $insertData[] = $_POST['compra1'][$i];
            $insertData[] = $_POST['id_user'];
            $insertData[] = $_POST['nombre'];
            $insertData[] = $_POST['ref_compra'];
            $insertData[] = $_POST['fecha'];
        }
        if (!empty($insertQuery)) {
            $sql .= implode(', ', $insertQuery);
            $stmt = $conn->prepare($sql);
            $stmt->execute($insertData);
        }
        $conn->commit();

The row I need to sum is "existencia" with the new data in "comprado", like per example:

If in the DB "existencia" have 100 items and in the new invoice is purchase 50 more items in the row "comprado" how can I sum existencia first and them sum the result with the "comprado" value? to save the new total in "existencia"

Best Regards!

Answer 1

Try changing

$insertData[] = $_POST['existencia'][$i];

to

$insertData[] = ($_POST['existencia'][$i] + $_POST['comprado'][$i]);

If they are showing up as strings use intval function.