如何根据批号更新库存数量 [英] How to update stock quantity according to their batch numbers

问题描述

我正在开发一个应用程序，我在其中保存具有不同批号的项目的数量，请查看数据库示例:

I am developing an application in which I save quantity of an item with its different batch numbers please view Database Sample:

.

SUM(qty_avbl) 显示可供出售的总数量为 17.我想出售一件数量为 7 的商品.如何从上表中选择并将该商品插入销售表

The SUM(qty_avbl) shows that total qty available for sale is 17. I want to sell one item in a quantity of 7. How can I select from the upper table and insert that item in the sales table

我试过这个代码:

$pid = 7901;
$order = 7;
$gets = mysqli_query($con, "SELECT * 
                            FROM `purchase_order` 
                            WHERE `pid` LIKE '$pid' 
                            AND `qty_avbl` > 0 ")
            or die(mysqli_error($con));
while($rows = mysqli_fetch_array($gets)){
    $getBatchNumber = $rows['batch_number'];
    $qty_avble = $rows['qty_avbl'];
            
    mysqli_query($con,"UPDATE `purchase_order` 
                            SET `qty_avbl`=`qty_avbl` - $order 
                        WHERE `qty_avbl` > 0 
                        LIMIT 1")
        or die("err Update ".mysqli_error($con));
}

推荐答案

警告 您当前的代码容易受到 SQL 注入的攻击.确保始终使用参数化的预处理语句，并且永远不要在代码中使用 die(mysqli_erro($con)).

WARNING Your current code is vulnerable to SQL injection. Make sure to always use parameterized prepared statements and never use die(mysqli_erro($con)) in your code.

无需任何PHP逻辑即可解决问题.在 SQL 中一步完成这样的操作总是更好.不过，这项任务一步完成并不容易，但一招就能搞定.诀窍是在 UPDATE 语句中使用一个临时变量来跟踪使用过的库存.

You can solve the problem without any PHP logic. It is always better to do such operations in one step in SQL. However, this task is not very easy to do in one step, but it is possible with one trick. The trick is to use a temporary variable in the UPDATE statement to keep track of used inventory.

$con->begin_transaction();

$con->query('SET @qty = 7');
$stmt = $con->prepare('UPDATE purchase_order 
    SET qty_avbl = qty_avbl-(@bought := LEAST(@qty,qty_avbl)), 
    qty_avbl = if(@qty := @qty-@bought, qty_avbl, qty_avbl) 
    WHERE pid = ?
    ORDER BY batch_number');
$stmt->bind_param('s', $pid);
$stmt->execute();

$con->commit();

这篇关于如何根据批号更新库存数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！