[Solved] How to update stock quantity according to their batch numbers [closed]

by


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

0

solved How to update stock quantity according to their batch numbers [closed]