CODEWITHSUNDEEP
phpmysqlsqlloopssql-update
Bruno__
Bruno__

Reputation: 1

SQL UPDATE in a loop ( mysql , PHP )

I have two tables :

Order :

date | Product | Quantity

01/03| P1 | 2

01/03| P2 | 2

02/03| P1 | 3

02/03| P2 | 1

02/03| P3 | 5

Stock :

Purchase number | product | Quantity

01/03 | P1 | 4

01/03 | P2 | 1

02/03 | P2 | 2

02/03 | P3 | 5

02/03 | P1 | 1

02/03 | P1 | 1

The first table is my order (what I sold), with this table I want to update the second table (which is my stock) to know exactly what i have in stock. But i want to do that following the date, I first update the Quantity.Stock 01/03 before 02/03 . Right now I have a loop , the problem is I don't want to have any " -1 " and i don't want to update all the rows with the same data . In this example I have 5 P1 in order so in stock the first line of P1 must be 0 , the next P1 line must be 0 too but the last line must stay at 1 .

Any ideas ? or direction ?

Bruno

Upvotes: 0

Views: 454

Answers (1)

Michal Brašna
Michal Brašna

Reputation: 2323

I suppose date and purchase_number are some sort of datetime values. Get orders, which you haven't used yet. I suppose you have some used/processed column or table, where you saved them as marked.

SELECT id, product, quantity
FROM orders
WHERE used = 0 AND quantity > 0
ORDER BY date ASC

Get all relevant purchases you can use in order you want to use them, so from the oldest.

SELECT id, product, quantity
FROM stock
WHERE quantity > 0
ORDER BY purchase_number ASC

You can then iteratively mark orders and update purchases. I assume you saved your results to $orders and $purchases respectively.

foreach ($orders as $order) {
    $remaining = $order['quantity'];

    foreach ($purchases as &$purchase)
        if ($order['product'] !== $purchase['product']
            || $purchase['quantity'] === 0) {
            continue;
        }

        $remaining = $purchase['quantity'] - $remaining;
        $purchase['quantity'] = max($remaining, 0);

        // update purchase, where
        //     :quantity is $purchase['quantity']
        //     :id is $purchase['id']
        // UPDATE stock SET quantity = :quantity WHERE id = :id

        if ($remaining >= 0) {
            break;
        }

        $remaining = -$remaining;
    }
    unset($purchase);

    if ($remaining > 0) {
        // we have problem, we sold more, then we have in stock
    }

    // mark order as used, where :id is $order['id']
    // UPDATE orders SET used = 1 WHERE id = :id
}

Example has time complexity O(M*N), where M is orders and N is purchases. You can change it to O(M+N), when you group data by products and do M SQL queries for purchases or some preprocessing. It has memory complexity O(M+N), which is a tradeoff for less queries.

Upvotes: 1

Related Questions