dqh1992
dqh1992
2015-11-06 22:36
浏览 89
已采纳

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

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!

图片转代码服务由CSDN问答提供 功能建议

我需要知道如何在一个查询中插入多行插入SUM。 \ n

我需要将表中已有的值与发票中的新值相加。

这是我的查询(MySql): \ n

  $ 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]; 
 $ insert  Data [] = $ _POST ['id_user']; 
 $ insertData [] = $ _POST ['nombre']; 
 $ insertData [] = $ _POST ['ref_compra']; 
 $ insertData [] = $  _POST ['fecha']; 
} 
 if(!empty($ insertQuery)){
 $ sql。= implode(',',$ insertQuery); 
 $ stmt = $ conn-> prepare(  $ sql); 
 $ stmt->执行($ insertData); 
} 
 $ conn-> commit(); 
   
 
 

行 我需要将“existencia”与“comprado”中的新数据相加,例如:

如果在数据库“existencia”中有100个项目且在新发票中是购买50 “comprado”行中的更多项目如何首先对existencia求和,并将结果与​​“comprado” 值相加? 在“existencia”中保存新的总数

最好的问候!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • drhozgt6007
    drhozgt6007 2015-11-12 21:57
    已采纳

    Try changing

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

    to

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

    If they are showing up as strings use intval function.

    点赞 评论