Subtracting data

[zimmo]

have built an ordering system and trying to get the values in the stock level field to change based on customers ordering. The set up is as follows:

1 table called 'products'.
There are 20 items in the table and all have a stock level field with number values.

I then have another table which stores the customers orders as they use the ordering system this table is called 'orders'

The table called 'orders' uses a php session to identify the customer and their order. The customer can order any or all of the 20 items from the 'products' table. Each item though has an option for the quanity. The customer can enter any quantity for the item. The quantity is stored in a field in the 'orders' table.

Once the customer has finished they go to the checkout page and enter some personal information and then submit the order.

SOLUTION NEEDED????
What I need is that when the submit the order the quantity of each of the items they ordered needs to be subtracted from the 'products' table. The field 'stock_level' is what needs to be updated so that the numbers are correct.

The identifier for each table is a field called 'pnumber' this contains a unique number for each product.

I do not know how to perform this, I have tried the following which I know is completely wrong, as this is above my sql skill level, its the first time I have been stumped, somebody out there will have a solution or point me in the right direction.

here is the code I tried which does nothing


$sql = "SELECT * FROM orders WHERE sid = '$PHPSESSID' ";
$sql_result = mysql_query($sql);
if (mysql_num_rows($sql_result) ==0)
{
header("Location: http://www.*****.co.uk/index.php");
exit;
}
else
{
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$pnumber = $row['pnumber'];
$qty = $row['qty'];

$SQL = " UPDATE products SET stock_level = stock_level - '$qty' WHERE pnumber = '$pnumber' ";

#execute SQL statement
$result = mysql_db_query( *****,"$SQL",$connection );

# check for error
if (!$result) { echo("ERROR: " . mysql_error() . "\n$SQL\n"); }
}
}

[svidgen]

From your code, it appears as though your inventory and order tables are on the same database. Take advantage of that and use a single atomic query. It will be much more efficient than running a query for each order-item. It may look something like this:

update
order_item oi
left join inventory i on (i.inventory_id=oi.inventory_id)
set
i.stock_level = i.stock_level - oi.qty
where
oi.order_id = '{$order_id_value}'
;
Make sense?

[zimmo]

svidgen thanks for that. Yes does make sense and more practical and less intensive on the server, I will change it to the more effecient way. Thanks for your input