Dave Carrera wrote:
I am deving a goods in system that REQUIRES that each delivery of items be entered into raw stock separately. This is why two rows in the table have identical details like,
Supplier Product name
But the qtys may be diff.
Ect ect
Now the PREPARATION dept need to internally order the RAW STOCK to make the final items for sale. SO they order from RAW STOCK items they want and this is where the RAW stock reductions come in.
Do they need to know that when they order 20 widgets, that 10 came from the shipment on Tuesday and 10 came from the shipment on Monday? And that the Monday shipment is all used up?
You could still use a better database structure, I think, but something along these lines may work.
$qty = $_POST['quantity'];
$query = "SELECT shipment_id, quantity FROM shipments WHERE product_id = 'XXX' AND quantity > 0 ORDER BY shipment_date ASC";
$result = mysql_query($query) or die(mysql_error());
while($qty > 0 && $row = mysql_fetch_assoc($result))
{
if($qty > $row['quantity'])
{
$qty = $qty - $row['quantity'];
$query = "UPDATE shipments SET quantity = 0 WHERE shipment_id = {$row['shipment_id'] AND product_id = 'XXX'";
}
else
{
$r = $row['quantity'] - $qty;
$qty = 0;
$query = "UPDATE shipments SET quantity = {$r} WHERE shipment_id = {$row['shipment_id'] AND product_id = 'XXX'";
}
$result2 = mysql_query($query) or die(mysql_error());
}
You'll have to grab what shipments or other information you want as you loop through the results, of course. I'd recommend you use InnoDB tables so you can wrap the whole thing in a transaction, also (or use a database abstraction layer that simulates transactions).
--
John Holmes
php|architect - The magazine for PHP professionals - http://www.phparch.com
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
