How to minus from several MySQL fields a certain value?

Question

I am writing products stock script. I have a MySQL table "products_stock":

id   zid   pid  amount
1    123   321   1
2    124   321   5
4    124   566   7
3    125   321   10

So, total amount of product id = 321 in stock is 16. Somebody makes order with pid=321 and qty = 7. I need some php-function, which will minus 7 from column amount starting from the first zid, and update records in table products_stock so that it lookes after like this:

id   zid   pid  amount
1    123   321   0
2    124   321   0
4    124   566   7
3    125   321   9

I am stucked from this point. Thank you for answers!

Answer 1

I don't use codeigniter but going through the documentation on how to perform a select operation and batch update. The main issue is getting your logic right... You select all the product entry for that particular item and you iterate through and subtract the amount of each from the ordered product.

<?php

//your order 
$orders = [
    ['id' => 321, 'qty' => 7],
    ['id' => 501, 'qty' => 20],
];


$data = [];


foreach($orders as $order) {

    $items = $this->db->where('pid', $order['id']);
    $order_qty = $order['qty'];

    foreach($items as $item){
        $item_qty = $item->amount - $order_qty;

        if ($item_qty <= 0) {
            // set amount to 0 because it is less than order quantity
            $data[] = ['id' => $item->id, 'amount' => 0];

            // Subtract amount from order quantity to get the remaining order
            $order_qty = $order_qty - $item->amount;

        } else {
             //If amount is more than order quantity set to calculated amount
             $data[] = ['id' => $item->id, 'amount' => $item_qty];

             // update order to 0
             $order_qty = 0;
        }

        //exit if order quantity is 0
        if ($order_qty === 0 ){
            break;
        }
    }
}

$this->db->update_batch('product_stock', $data, pid');