duanjie3267 2016-10-18 19:00 采纳率: 0%
浏览 73
已采纳

使用存储在数组中的php值的总和更新MySQL表列

I have an html form that users input data into that saves the values in two MySQL tables, invoices and lineitems. When the "save" button is clicked, these values are inserted as a new row in invoices and lineitems. The page refreshes to display the same form, but with the user-inputted values saved in the input fields. The webpage has multiply 'lineitems' the user can create. This data gets saved to the lineitems table. Each invoice row has a unique id that gets added to each lineitem the user creates. That way the invoice can get each lineitem associated with it from the lineitems table. I need to perform a sum calculation on lineitems.quantity for each invoice and save the value to invoices.totalquantity.

I have tried setting up triggers on the lineitems table itself, but it always fails. I am now trying to do it from the program side.

MY CODE:

// LINE ITEMS CLASS 
  // ==============================================================================================
class lineitems{

    var $queryresult = NULL;

    function lineitems($db, $invoiceid, $invoicetype = "Order"){

        $this->db = $db;
        $this->invoiceid = ((int) $invoiceid);
        $this->invoicetype = $invoicetype;

    }//end method


    function get(){

    $querystatement = "
            SELECT
                products.partname,
                products.partnumber,
                products.claveid,
                products.claveid2,
                products.layupid,

                lineitems.id,
                lineitems.productid,

                lineitems.quantity,




                lineitems.nipple,
                lineitems.polish,
                lineitems.edgeseal,
                lineitems.leads,

                lineitems.leadsl,

                lineitems.width,
                lineitems.widthf,
                lineitems.height,
                lineitems.heightf,
                lineitems.comment



            FROM
                lineitems LEFT JOIN products ON lineitems.productid = products.id
            WHERE
                invoiceid = ".$this->invoiceid." 
            ORDER BY
                lineitems.displayorder";



            /*  
            FROM
                lineitems LEFT JOIN products ON lineitems.productid = products.id
            WHERE
                invoiceid = ".$this->invoiceid."
            ORDER BY
                lineitems.displayorder"; */

        $this->queryresult = $this->db->query($querystatement);

}//end method


    function show(){



        if($this->queryresult === NULL)
            $this->get();

        $count = 1;
        while($therecord = $this->db->fetchArray($this->queryresult)){



            ?><tr id="li<?php echo $count?>" class="lineitems">

                <td colspan="2" class="lineitemsLeft" <?php if($this->invoicetype == "Void" || $this->invoicetype == "Invoice") echo 'nowrap="nowrap"'?>>
                    <input type="hidden" id="li<?php echo $count?>ProductID" value="<?php echo $therecord["productid"]?>"/>

                    <div>
                        <?php if($therecord["partnumber"] || $therecord["partname"] ) {?>
                        <p><?php echo formatVariable($therecord["partnumber"]) ?></p>
        <!--                <p class="important"><?//php echo formatVariable($therecord["partname"])?></p> -->

                        <?php } else
                                echo "&nbsp;";
                        ?>
                    </div>
                </td>



    <!--            <td><input id="li<?//php echo $count?>Memo" class="lineitemMemos" value="<?//php echo formatVariable($therecord["memo"])?>"/></td> -->
            <!--    <td><input id="li<?////php echo $count?>Frontfinish" class="lineitemFrontfinishs" value="<?/////php echo formatVariable($therecord["frontfinish"])?>"/></td>
                <td><input id="li<?////php echo $count?>Backfinish" class="lineitemBackfinishs" value="<?////php echo formatVariable($therecord["backfinish"])?>"/></td> -->
                   <td><input id="li<?php echo $count?>Comment" class="lineitemComments" value="<?php echo formatVariable($therecord["comment"])?>"/></td>

                     <td><input id="li<?php echo $count?>Nipple" class="lineitemNipples" value="<?php echo formatVariable($therecord["nipple"])?>"/></td>



                     <td><input id="li<?php echo $count?>Polish" class="lineitemPolishes" value="<?php echo formatVariable($therecord["polish"])?>"/></td>




                     <td><input id="li<?php echo $count?>Edgeseal" class="lineitemEdgeseals" value="<?php echo formatVariable($therecord["edgeseal"])?>"/></td>

                     <td><input id="li<?php echo $count?>Leads" class="lineitemLeadss" value="<?php echo $therecord["leads"]?>"/></td>



                     <td><input id="li<?php echo $count?>Leadsl" class="lineitemLeadsls" value="<?php echo $therecord["leadsl"]?>"/></td>
                 <!--    <td><input id="li<?///php echo $count?>RQ" class="lineitemRQs" value="<?///php echo $therecord["RQ"]?>"/></td> -->



                     <td><input id="li<?php echo $count?>Width" class="lineitemWidths" value="<?php echo $therecord["width"]?>"/></td>
                 <td>    <input id="li<?php echo $count?>Widthf" class="lineitemWidthfs" value="<?php echo $therecord["widthf"]?>"/></td>

                     <td><input id="li<?php echo $count?>Height" class="lineitemHeights" value="<?php echo $therecord["height"]?>"/>  </td>
                     <td> <input id="li<?php echo $count?>Heightf" class="lineitemHeightfs" value="<?php echo $therecord["heightf"]?>"/></td>
                     <!--
                                             <td><input id="li<?///php echo $count?>Claveid" class="uneditable lineitemClaveids" value="<?////php echo formatVariable($therecord["claveid"])?>"/></td>

                     <td><input id="li<?////php echo $count?>Claveid2" class="uneditable lineitemClaveid2s" value="<?/////php echo formatVariable($therecord["claveid2"])?>"/></td>

    -->
                <td><input id="li<?php echo $count?>Quantity" class="lineitemQuantities" value="<?php echo formatVariable($therecord["quantity"])?>"/></td>

       <td><input id="li<?php echo $count?>Sqft" class="uneditable lineitemSqfts" value="<?php echo formatVariable(ceil($therecord["width"]*$therecord["height"]/144)) ?>"/></td>


       <td><input id="li<?php echo $count?>Sqfttot" class="uneditable lineitemSqfttots" value="<?php echo formatVariable (ceil($therecord["width"]*$therecord["height"]/144* $therecord["quantity"]))?>"/></td> 


      <!--    <td><input id="li<?///php echo $count?>unitweight" class=" uneditable lineitemUnitweight" value="<?///php echo $unitweight ?>"/></td> -->

       <!--    <td><input id="li<?///php echo $count?>totalweight" class="uneditable lineitemTotalweight" value="<?///php echo $totalweight ?>"/></td>   -->                    







                <td class="lineitemsButtonTDs">
                    <div id="li<?php echo $count?>ButtonsDiv" class="lineitemsButtonDivs">
                        <button type="button" id="li<?php echo $count?>ButtonDelete" class="graphicButtons buttonMinus LIDelButtons" title="Remove line item"><span>-</span></button><br />
                        <button type="button" id="li<?php echo $count?>ButtonMoveUp" class="graphicButtons buttonUp LIUpButtons" title="Move Item Up"><span>Up</span></button><br />
                        <button type="button" id="li<?php echo $count?>ButtonMoveDown" class="graphicButtons buttonDown LIDnButtons" title="Move Item Down"><span>Dn</span></button><br />
                    </div>                      
                </td>

            </tr>


            <?php

            $count++;

        }//endwhile

    }//end method


    function set($itemlist, $userid = NULL){

        if(!$userid)
            $userid = $_SESSION["userinfo"]["id"];

        $deletestatement = "
            DELETE FROM
                lineitems
            WHERE
                invoiceid = ".$this->invoiceid;

        $this->db->query($deletestatement);

        $itemsArray = explode(";;", $itemlist);

        $count = 0;

        foreach($itemsArray as $item){

            $itemRecord = explode("::", $item);
            if(count($itemRecord) > 1){

                $insertstatement ="
                    INSERT INTO
                        lineitems(
                            invoiceid, 
                            productid, 
                            comment,
                            nipple,
                            polish,
                            edgeseal,
                            leads,
                            leadsl,
                            width,
                            widthf,
                            height,
                            heightf,
                            quantity,
                            sqft,
                            sqfttot,
                            displayorder,
                            createdby,
                            creationdate,
                            modifiedby,
                            modifieddate
                        )
                    VALUES (
                        ".$this->invoiceid.",
                        ".((int) $itemRecord[0]).",
                        '".$itemRecord[1]."',
                        '".$itemRecord[2]."',
                        '".$itemRecord[3]."',
                        '".$itemRecord[4]."',
                        '".$itemRecord[5]."',
                        '".$itemRecord[6]."',
                        '".$itemRecord[7]."',
                        '".$itemRecord[8]."',
                        '".$itemRecord[9]."',
                        '".$itemRecord[10]."',
                        '".$itemRecord[11]."',
                        '".$itemRecord[12]."',
                        '".$itemRecord[13]."',
                        ".$count.",
                        ".$userid.",
                        NOW(),
                        ".$userid.",
                        NOW()

                    )";

                $this->db->query($insertstatement);


                $count++;

            }//end if

        }//endforeach                       

    }//end method


    //this isn't actually updating my invoices table...
    function updatesglitots() {

        $sglitotalsupdate="UPDATE invoices i LEFT JOIN (SELECT invoiceid, SUM(quantity) AS sgtotqty FROM lineitems li GROUP BY invoiceid) AS t ON i.invoiceid = t.invoiceid SET i.totalquantity = t.sgtotqty WHERE i.invoiceid = ".$this->invoiceid."";


    $this->db->query($sglitotalsupdate);


    }

}//end class

}// end if

I want to do either something like this:

    "UPDATE invoices
        SET totalquantity = SUM('".$itemRecord[11]."')
        WHERE invoices.id = ".$this->invoiceid."
    ;"

NOTE: '".$itemRecord[11]."' is the array value that holds the quantity for each lineitem a user creates.

OR...

     "UPDATE invoices INNER JOIN lineitems
         ON invoices.id=lineitems.invoiceid
         SET invoices.totalquantity = SUM(lineitems.quantity)
         WHERE invoices.id=lineitems.invoiceid
      ;"

Please help as I have tried several different methods...

Thank you!!

  • 写回答

2条回答 默认 最新

  • duanqian9593 2016-10-18 19:33
    关注

    You can use a correlated subquery, or join the invoices table to a derived table. After you have inserted all the line items, this will calculate the total.

    "UPDATE invoices i 
    SET totalquantity = (
        SELECT SUM(quantity) 
        FROM lineitems li 
        WHERE li.invoiceid = i.invoiceid)
    WHERE i.invoiceid = " . $this->invoiceid;
    
    // or
    
    "UPDATE invoices i 
    LEFT JOIN
       (SELECT invoiceid, SUM(quantity) AS total 
        FROM lineitems li 
        GROUP BY invoiceid) AS t
    ON i.invoiceid = t.invoiceid
    SET i.totalquantity = t.total
    WHERE i.invoiceid = " . $this->invoiceid;
    

    i, li, and t are aliases for the invoices table, lineitems table, and the derived table in the second query, respectively.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 请问一下这个运行结果是怎么来的
  • ¥15 这个复选框什么作用?
  • ¥15 单通道放大电路的工作原理
  • ¥30 YOLO检测微调结果p为1
  • ¥20 求快手直播间榜单匿名采集ID用户名简单能学会的
  • ¥15 DS18B20内部ADC模数转换器
  • ¥15 做个有关计算的小程序
  • ¥15 MPI读取tif文件无法正常给各进程分配路径
  • ¥15 如何用MATLAB实现以下三个公式(有相互嵌套)
  • ¥30 关于#算法#的问题:运用EViews第九版本进行一系列计量经济学的时间数列数据回归分析预测问题 求各位帮我解答一下