How to update stock quantity according to their batch numbers

Question

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

.

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

I have tried this code:

$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));
}

Good code indentation would help us read the code and more importantly it will help **you debug your code** [Take a quick look at a coding standard](https://www.php-fig.org/psr/psr-12/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. — RiggsFolly, Feb 17 '21 at 17:18
Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187) You should alway use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenating user provided values into the query. Never trust ANY user input! — RiggsFolly, Feb 17 '21 at 17:19
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Feb 17 '21 at 22:21

score 0 · Accepted Answer · answered Feb 17 '21 at 22:56

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.

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();

How to update stock quantity according to their batch numbers

1 Answers1

Linked