How to update field to subtract value to existing value if that value is lesser than the existing value balance value should be subtract from the next coloumn in MySQL
// These are the inputs you will be getting.
$pid = 321;
$qty = 7;
// Fetch all the relevent rows using the below query statement.
$select_sql = "select * from products_stock where pid = {$pid} and amount > 0";
// This will return the below array.
$data = [
[
'id' => 1,
'zid' => 123,
'pid' => 321,
'amount' => 1,
],
[
'id' => 1,
'zid' => 124,
'pid' => 321,
'amount' => 5,
],
[
'id' => 1,
'zid' => 125,
'pid' => 321,
'amount' => 10,
],
];
$update_data = [];
// You can then loop through your rows to perform your logic
foreach ($data as $row) {
// Exit loop if qty is 0
if ($qty == 0) {
break;
}
$id = $row['id'];
$amount = $row['amount'];
// Subtract the required amount from qty.
$qty -= $amount;
$amount = 0;
// If extra qty was consumed, add back to the amount.
if ($qty < 0) {
$amount =+ ($qty * -1);
$qty = 0;
}
// Update you data here or the best practice would be to avoid sql in a loop and do a bulk update.
// You can do this based on your requirement.
// $update_sql = "update products_stock set amount = {$amount} where id = {$id}";
// Prepare date for bulk update
$update_data []= [
'id' => $id,
'amount' => $amount,
];
echo "Qty {$qty} | Amt {$amount} \n";
echo "--------\n";
}
// Bulk update all your rows
if (count($update_data) > 0) {
$this->db->update_batch('products_stock', $update_data, 'id');
}
echo "\n";
echo "Balance Qty ". $qty . "\n";