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!

If I get you right, you have a product_stock table that contains many entries product_id 321 and you want to subtract the quantity of an order from all your entries of product_id 321? — ibonly, Dec 18 '17 at 04:57
sorry! i don't know how to write it by use php codeigniter to show the result as below. — Lorn Titya, Dec 18 '17 at 04:58

ibonly · Accepted Answer · 2017-12-18T14:45:46.200

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

If Somebody makes order with pid=321 with qty = 7 and pid=566 with 5. How can i do? Please help, Thank. — Lorn Titya, Dec 18 '17 at 09:15
Upvoting an answer will be enough to say thanks. All you need is to create an array of your order [['pid' => 321, 'qty' => 7],['pid' => 556, 'qty' => 5]], loop through and pass in the code into the loop. — ibonly, Dec 18 '17 at 14:35

RamC · Answer 2 · 2017-12-18T06:37:42.167

You can do so by selecting all the relevent rows from the products_stock table and subtract/update the values one by one in loop until your required quantity gets over.

Example code

// 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";

Output

Qty 6 | Amt 0 
--------
Qty 1 | Amt 0 
--------
Qty 0 | Amt 9 
--------

Balance Qty 0

Refer https://eval.in/920847 for output.

You can try running the same code with different qty.

This is the rough logic for your use case which will work as expected. Still there may be better ways to do this in an optimal way.

Glad if it helps you.

If Somebody makes order with pid=321 with qty = 7 and pid=566 with 5. How can i do? Please help, Thank. — Lorn Titya, Dec 18 '17 at 09:13
You can use the same code inside a loop and perform the two transactions that you had mentioned — RamC, Dec 18 '17 at 10:24
sorry! Ramc, i don't know how write it inside a loop becuase qty and product_id are array request. i am a basic php. could you do it to show me? Thank so much for your help and explain. — Lorn Titya, Dec 18 '17 at 11:18
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/161485/discussion-between-lorn-titya-and-ramc). — Lorn Titya, Dec 19 '17 at 00:47

How to minus from several MySQL fields a certain value?

2 Answers2

Linked