mysql 在完整订单数组上更新数量 [英] mysql update qty on complete order array

问题描述

当订单在销售点系统中完成时，我正在尝试更新订单中所有商品的库存数量

i'm trying to update quantities left in stock on all items in the order, when the order is completed in a point of sale system

到目前为止我的代码是

$order=$_GET["order"];

include('php/config.php');


$invoice=$order;
$voucher=$_GET['voucher'];
$card=$_GET['card'];
$cash=$_GET["cash"];
$date = date('Y-m-d');

$sql="SELECT * FROM `orders` WHERE  `invoice` = '".$invoice."'";

$result = mysql_query($sql);

while ($row = mysql_fetch_array($result)) {
$parts[] = array(
"part" => $row['part'], 
"qty" => $row['qty'], 
 );

$sqlstock = "UPDATE `stock` SET available='available - $parts[qty]' WHERE 

part = '".$parts['part']."'";
}

推荐答案

可能最好将其作为一条 SQL 来执行:-

Probably best to do this as a single piece of SQL:-

UPDATE stock a INNER JOIN order b ON a.part = b.part 
SET a.available = a.available - b.qty
WHERE  b.invoice` = '$order'

注意不要在没有某种方式检查订单尚未用于更新库存的情况下多次重新运行此操作

Watch out that you don't just rerun this multiple times without some way of checking that an order hasn't already been used to update the stock

这样做，如果您的订单中有 1000 件商品，那么它就是一个查询.执行选择然后循环结果需要 10001 次查询.

Doing it this way, if you had 1000 items on the order then it is a single query. Doing a select and then looping around the results would require 10001 queries.

这篇关于mysql 在完整订单数组上更新数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！