我想做的是,当客户从我的购物车购买时,产品的数量将从客户购买的数量中减去?我期待客户会购买多种产品。
这是我的代码。
$x=$_SESSION['products'];
foreach($x as $id => $y){
$insert_row = $mysqli->query("INSERT INTO transaction
VALUES (null,'$buyerName','$buyerEmail','$transactionID','$y[code]','$y[qty]',NOW(),'Pending')");
$result1=mysql_query("SELECT quantity from products where product_code='$y[code]'");
if(mysql_num_rows($result1)>0){
$row=mysql_fetch_object($result1);
$currqty=$row->quantity;
echo "".$currqty;
}else{
echo "No record found";
}
SELECT GROUP_CONCAT(product) AS res FROM table WHERE foo=1 GROUP BY foo;
这样,您将在 foo=1 上获得"res"匹配的所有产品的列表,同时只得到一行。
在此处查找有关group_concat的更多信息:
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
如果您的代码的目的是在成功插入事务后从产品中减去数量,那么下面是代码的优化版本,可以实现这一点:
foreach($_SESSION['products'] as $id => $y) {
$insert_row = $mysqli->query("
INSERT INTO transaction
VALUES (null,'$buyerName','$buyerEmail','$transactionID','$y[code]','$y[qty]',NOW(),'Pending')"
);
if($insert_row) {
## Important: substract the quantity from products only if a successful
## insert has been made in the transaction table
$result1 = mysql_query("UPDATE products SET quantity = quantity - {$y[qty]} WHERE product_code='$y[code]'");
if($result1) {
echo "quantity updated";
}
}
}