我有两个表，tb_empgrocery是父和tb_empgroc_master是子。一个顾客的每一个订单都保存到数据库表tb_empgrocery中，然后tb_empgroc_master计算并输出该顾客订购的商品总数和总金额。
这是我的tb_empgrocery表
empgrocID order_no date_ordered item_no qty_ordered unit_price amount
1 15-09-0000000001 2015-09-16 17:24:36 40933 2 65.00 130.00
2 15-09-0000000001 2015-09-16 17:24:36 40935 43 4.07 175.01
3 15-09-0000000001 2015-09-16 17:24:36 40934 23 4.07 93.61
对于tb_empgroc_master
empgrocmstID order_no date_ordered total_items total_amount order_status
1 15-09-0000000001 2015-09-16 17:24:36 3 398.62 Pending
可以看到，tb_empgroc_master计算客户表单tb_empgrocery订购的所有商品。,,order_no连接这两个表
这是我的两个表的代码
case "Add":
$itemno = $_POST['itemno'];
$qty = $_POST['qty'];
$unitprc = $_POST['unitprc'];
$amt = $_POST['amt'];
$coopmemid = $_SESSION['kiosk']['is_coopmemID_kiosk'];
$totamt = 0;
$totitm = count($itemno);
$a_empgroid = array();
for($x=0; $x<$totitm; $x++) {
$Addquery = "INSERT INTO tb_empgrocery (coopmemID , date_ordered, item_no, qty_ordered, unit_price, amount)
VALUES ('$coopmemid',(NOW()),'$itemno[$x]','$qty[$x]','$unitprc[$x]','$amt[$x]')";
$atecCoop->query($Addquery);
$totamt+=$amt[$x];
$inserted_id = $atecCoop->insert_id;
array_push($a_empgroid,$inserted_id);
}
$Savequery = "INSERT INTO tb_empgroc_master (order_status, date_ordered, total_items, total_amount) VALUES ('Pending', (NOW()), '$totitm', '$totamt')";
$atecCoop->query($Savequery);
$empgrocmstid = $atecCoop->insert_id;
$orderno = date('y-m-').str_pad($empgrocmstid, 10, "0", STR_PAD_LEFT);
$sql = "UPDATE tb_empgroc_master SET order_no='$orderno' WHERE empgrocmstID='$empgrocmstid'";
$atecCoop->query($sql);
foreach($a_empgroid as $empgrocid) {
$sql = "UPDATE tb_empgrocery SET order_no='$orderno' WHERE empgrocID='$empgrocid'";
$atecCoop->query($sql);
}
break;
这是我的Delete查询…
case "Delete":
$Deletequery = "CREATE TRIGGER update_tb_empgrocery AFTER UPDATE ON tb_empgrocery FOR EACH ROW
BEGIN
UPDATE tb_empgroc_master
INNER JOIN
(
SELECT order_no, SUM(amount) AS total_amount, COUNT(empgrocID) AS total_items
FROM tb_empgrocery
GROUP BY order_no
)
SET tb_empgroc_master.total_items = tb_empgrocery.total_items,
tb_empgroc_master.total_amount = tb_empgrocery.total_amount
WHERE tb_empgroc_master.order_no = NEW.order_no;
DELETE FROM tb_empgroc_master WHERE empgrocmstID='".$_POST['empgrocmstID']."';
END;
DELIMITER";
$atecCoop->query($Deletequery);
我的问题是当我在tb_empgrocery中删除一个项目时，tb_empgroc_master不更新。它必须更新total_items，它将成为2和total_amount的值(如果我删除了tb_empgrocery中的一个项目)。
谢谢你的帮助。
PHP代码<?php
if(!session_id()){
session_start();
}
include_once '../fileadmin/dbinit.php';
$todo = $_POST['todo'];
// $con = mysql_connect("localhost","root","","atec_coop");
// if (!$con){
// die("Can't connect".mysql_error());
// }
mysql_select_db("atec_coop",$con);
switch ($todo) {
case "display":
$sql = "SELECT * from tb_empgroc_master";
// $myData = mysql_query($sql,$con);
$result = $atecCoop->query($sql);
// mysql_close($con);
$html = ''; $ctr = 0;
if ($result->num_rows){
while ($row = $result->fetch_object()){
$id = $row->empgrocmstID;
$date_ordered = date("m-d-Y");
$order_no = date($row->order_no);
$total_items = number_format($row->total_items);
$total_amount = number_format($row->total_amount,2);
$order_status = wordwrap($row->order_status);
$Order = $row->empgrocmstID;
$html .= "<tr id='$id'>";
$html .= "<td class='date_ordered' style='text-align:center'>$date_ordered</td>";
$html .= "<td class='order_no' style='text-align:center'>$order_no</td>";
$html .= "<td class='total_items' style='text-align:right'>$total_items</td>";
$html .= "<td class='total_amount' style='text-align:right'>$total_amount</td>";
$html .= "<td id='$Order' class='order_status' style='text-align:center'>$order_status</td>";
$html .= "</tr>";
}
}
echo $html;
break;
case "Cancel":
$Cancelquery = "UPDATE tb_empgroc_master SET order_status='Cancelled' WHERE empgrocmstID='".$_POST['empgrocmstID']."'";
$atecCoop->query($Cancelquery);
break;
case "ApprovePartial":
$sql = "SELECT * from tb_empgroc_master";
// $myData = mysql_query($sql,$con);
$result = $atecCoop->query($sql);
// mysql_close($con);
$html = ''; $ctr = 0;
if ($result->num_rows){
while ($row = $result->fetch_object()){
$id = $row->empgrocmstID;
$date_ordered = date("m-d-Y");
$order_no = date($row->order_no);
$total_items = number_format($row->total_items);
$total_amount = number_format($row->total_amount,2);
$order_status = wordwrap($row->order_status);
$Order = $row->empgrocmstID;
$html .= "<tr id='$id'>";
$html .= "<td class='date_ordered' style='text-align:center'>$date_ordered</td>";
$html .= "<td class='order_no' style='text-align:center'>$order_no</td>";
$html .= "<td class='total_items' style='text-align:right'>$total_items</td>";
$html .= "<td class='total_amount' style='text-align:right'>$total_amount</td>";
$html .= "<td id='$Order' class='order_status' style='text-align:center'>$order_status</td>";
$html .= "</tr>";
}
}
echo $html;
$ApprovePartialquery = "UPDATE tb_empgroc_master SET order_status='Partially Delivered' WHERE empgrocmstID='".$_POST['empgrocmstID']."'";
$atecCoop->query($ApprovePartialquery);
break;
case "Approve":
$Approvequery = "UPDATE tb_empgroc_master SET order_status='Delivered' WHERE empgrocmstID='".$_POST['empgrocmstID']."'";
$atecCoop->query($Approvequery);
break;
case "Delete":
$Deletequery = "DELETE FROM tb_empgroc_master WHERE empgrocmstID='".$_POST['empgrocmstID']."'";
$atecCoop->query($Deletequery);
break;
}
?>
MySQL支持触发器。这些是您可以编写的函数，在插入/更新/删除记录时执行。这两种你都需要一个。
当tb_empgrocery上的记录更新时，您可以使用类似于下面示例的东西来触发触发器，然后更新主表上该订单的数字。
CREATE TRIGGER `update_tb_empgrocery` AFTER UPDATE ON `tb_empgrocery` FOR EACH ROW
BEGIN
UPDATE tb_empgroc_master a
INNER JOIN
(
SELECT order_no, SUM(amount) AS total_amount, COUNT(empgrocID) AS total_items
FROM tb_empgrocery
GROUP BY order_no
) b
SET a.total_items = b.total_items,
a.total_amount = b.total_amount
WHERE a.order_no = NEW.order_no;
END;;
DELIMITER ;
请注意，很少有人使用它们，这可能会导致问题。如果将来代码出现问题，很可能是大多数人都不会想到去检查的。
编辑下面是创建所需的3个触发器的代码。第一行DELIMITER//只是将默认分隔符从a;//。这样，每一行在创建时都不会作为语句执行，而是作为创建触发器函数的代码来处理。首先创建一个触发器，当tb_empgrocery上的现有记录被更新时触发
DELIMITER //
CREATE TRIGGER `update_tb_empgrocery` AFTER UPDATE ON `tb_empgrocery` FOR EACH ROW
BEGIN
UPDATE tb_empgroc_master a
LEFT OUTER JOIN
(
SELECT order_no, SUM(amount) AS total_amount, COUNT(empgrocID) AS total_items
FROM tb_empgrocery
GROUP BY order_no
) b
ON a.order_no = b.order_no
SET a.total_items = COALESCE(b.total_items, 0),
a.total_amount = COALESCE(b.total_amount, 0)
WHERE a.order_no = NEW.order_no;
END;
接下来创建一个触发器，当新记录插入到tb_empgrocery时触发。
DELIMITER //
CREATE TRIGGER `insert_tb_empgrocery` AFTER INSERT ON `tb_empgrocery` FOR EACH ROW
BEGIN
UPDATE tb_empgroc_master a
LEFT OUTER JOIN
(
SELECT order_no, SUM(amount) AS total_amount, COUNT(empgrocID) AS total_items
FROM tb_empgrocery
GROUP BY order_no
) b
ON a.order_no = b.order_no
SET a.total_items = COALESCE(b.total_items, 0),
a.total_amount = COALESCE(b.total_amount, 0)
WHERE a.order_no = NEW.order_no;
END;
最后创建一个触发器，当tb_empgrocery上的现有记录被删除时触发。
DELIMITER //
CREATE TRIGGER `delete_tb_empgrocery` AFTER DELETE ON `tb_empgrocery` FOR EACH ROW
BEGIN
UPDATE tb_empgroc_master a
LEFT OUTER JOIN
(
SELECT order_no, SUM(amount) AS total_amount, COUNT(empgrocID) AS total_items
FROM tb_empgrocery
GROUP BY order_no
) b
ON a.order_no = b.order_no
SET a.total_items = COALESCE(b.total_items, 0),
a.total_amount = COALESCE(b.total_amount, 0)
WHERE a.order_no = OLD.order_no;
END;