-3

I've looked over a lot of posts on here, but cannot find a solution to my issue. I'm trying to develop a tracking for a small shop of inventory of the same product from different lots. I'm utilizing PHP and MySQL for the DB. I have tried information from the following posts without success:

How to minus from several MySQL fields a certain value?

Keep subtracting value in loop getting mysql result php

PHP with Loop Calculation

Keep the remaining value after subtract in php while loop

There was a few others, but I don't have them open but they all have the same idea. This is for a small family ran shop, and we're using PHPRunner to develop the tracking. I have the following table:

Inventory:

id product lot produced sold remaining
1 ABC 20 165 0 165
2 ABC 29 290 0 290
3 def 37 320 0 320
4 ABC 63 145 0 145
5 xyz 77 900 0 900

Now when someone orders say 500 units of product ABC, I need to loop through the table and subtract 500 total from the rows, so the desired code to update each row that is affected

id product lot produced sold remaining
1 ABC 20 165 165 0
2 ABC 29 290 290 0
3 def 37 320 0 320
4 ABC 63 145 45 100
5 xyz 77 900 0 900

The amount of sale (sold) would come from a form, where the product number is selected along with the amount. I've been racking my brain the last week trying numerous methods to get this to work, with no avail. When I need it to do is loop through each row of product ABC, subtract the max amount from each line until the sold amount is zero, and update each row with the amount sold from each lot and the amount remaining.

Thank you

David
  • 3
  • 3
  • 1
    *I have the following 3 tables* - you have only shown us one? – Stu Jan 22 '22 at 20:31
  • My apologizes. Baby woke up when typing this up. The other 2 tables are for logging of the sale, and for individual transactions. I can get the data needed for the other 2 to work properly, but I'm having trouble creating an array and working with that array to subtract from each row until sale amount is zero. – David Jan 22 '22 at 20:59
  • Welcome, to improve your experience on SO please [take the tour](http://stackoverflow.com/tour) and read [how to ask](https://stackoverflow.com/help/how-to-ask), an [On Topic question](https://stackoverflow.com/help/on-topic), then look at the [Question Check list](https://meta.stackoverflow.com/questions/260648/stack-overflow-question-checklist), the [perfect question](http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/) and how to create a [Minimal, Complete and Verifiable Example](http://stackoverflow.com/help/mcve) – RiggsFolly Jan 22 '22 at 21:55
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. – Community Feb 01 '22 at 14:34

1 Answers1

0

This is a very simple example of how you could allocate the stock. Obviously you will be updating your table rows instead of just an array.

<?php

function dump($var) { echo '<code><pre>' . print_r($var, true) . '</pre></code>'; }

// query for inventory for product ABC
$inventory = [
    ['id' => 1, 'product' => 'ABC', 'lot' => 20, 'produced' => 165, 'sold' => 0, 'remaining' => 165],
    ['id' => 2, 'product' => 'ABC', 'lot' => 29, 'produced' => 290, 'sold' => 0, 'remaining' => 290],
    ['id' => 4, 'product' => 'ABC', 'lot' => 63, 'produced' => 145, 'sold' => 0, 'remaining' => 145]
];

dump($inventory);

$num_to_allocate = 500;

// iterate over inventory and allocate
foreach ($inventory AS &$lot) {

    // check to see if there is enough to satisfy order amount
    if ($lot['remaining'] >= $num_to_allocate) {
        $lot['sold'] = $lot['sold'] + $num_to_allocate;
        $lot['remaining'] = $lot['remaining'] - $num_to_allocate;
        $num_to_allocate = 0;
    } else {
        // allocate everything available
        $num_to_allocate = $num_to_allocate - $lot['remaining'];
        $lot['sold'] = $lot['produced'];
        $lot['remaining'] = 0;
    }
    $sql = "UPDATE inventory SET sold = {$lot['sold']}, remaining = {$lot['remaining']} WHERE id = {$lot['id']}"; echo $sql . "\n\n";
    dump($inventory);

    // we have already allocated required stock so no need to continue
    if ($num_to_allocate === 0) {
        break;
    }
}

This process and the storing of the sold and remaining summaries is redundant, as it is trivial to query the stock position in real-time. The real time stock position is always available as -

/* This is a very simplistic example but hopefully you get the point */
WITH inventory (id, product, lot, produced) AS (
    SELECT 1, 'ABC', 20, 165 UNION ALL
    SELECT 2, 'ABC', 29, 290 UNION ALL
    SELECT 3, 'def', 37, 320 UNION ALL
    SELECT 4, 'ABC', 63, 145 UNION ALL
    SELECT 5, 'xyz', 77, 900
), order_line_items (order_id, product, quantity) AS (
    SELECT 1, 'ABC', 500
)
SELECT (SELECT SUM(produced) FROM inventory WHERE product = 'ABC') - (SELECT SUM(quantity) FROM order_line_items WHERE product = 'ABC');
user1191247
  • 10,808
  • 2
  • 22
  • 32