使用存储在数组中的php值的总和更新MySQL表列 [英] Update MySQL table column using the sum of a php value stored in an array
本文介绍了使用存储在数组中的php值的总和更新MySQL表列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个html表单，用户将数据输入到其中，将值保存在两个MySQL表，发票和行项目中.单击保存"按钮后，这些值将作为新行插入到发票和订单项中.页面刷新以显示相同的表单，但用户输入的值保存在输入字段中.该网页具有用户可以创建的多个行项目".此数据将保存到lineitems表中.每个发票行都有一个唯一的ID，该ID会添加到用户创建的每个订单项中.这样，发票可以从lineitems表中获取与其关联的每个lineitem.我需要对每个发票的lineitems.quantity执行总和计算，并将值保存到invoices.totalquantity.
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.
我尝试在lineitems表本身上设置触发器，但始终失败.我现在正在尝试从程序方面进行操作.
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.
我的代码:
// 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 " ";
?>
</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
我想做这样的事情:
"UPDATE invoices
SET totalquantity = SUM('".$itemRecord[11]."')
WHERE invoices.id = ".$this->invoiceid."
;"
注意:'.$ itemRecord [11]."'是保存用户创建的每个订单项数量的数组值.
NOTE: '".$itemRecord[11]."' is the array value that holds the quantity for each lineitem a user creates.
或...
"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...
谢谢！
推荐答案
您可以使用相关子查询，也可以将发票表连接到派生表.插入所有订单项后，这将计算总数.
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和t分别是第二个查询中的invoices表，lineitems表和派生表的别名.
i, li, and t are aliases for the invoices table, lineitems table, and the derived table in the second query, respectively.
这篇关于使用存储在数组中的php值的总和更新MySQL表列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
