CODEWITHSUNDEEP
phpcodeignitercodeigniter-2codeigniter-3
Lorn Titya
Lorn Titya

Reputation: 85

How to minus from several MySQL fields a certain value?

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!

Upvotes: 0

Views: 1716

Answers (2)

ibonly
ibonly

Reputation: 133

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

Upvotes: 1

RamC
RamC

Reputation: 1287

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.

Upvotes: 0

Related Questions