[SOLVED] Value from one table subtracting another multiple results

[zimmo]

I 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");    }
}
}

 

 

 

in are stored with the php session as the grouping tool to keep all the orders together. The customer can order any of the 20 items or all of them if they wish. Each item in the

[zimmo]

The bottom bit was in error please ignore this section

*****

in are stored with the php session as the grouping tool to keep all the orders together. The customer can order any of the 20 items or all of them if they wish. Each item in the

*******

 

I have been reading and it seems sum is what I need to look at, but not sure how to work it

[cags]

Well as far as your SQL goes the theory seems correct. Integers though shouldn't be sent as strings so remove the single quotes from around $qty. If pnumer is an integer you should really do the same for $pnumber.

 

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

[zimmo]

Thanks cags... I changed that before, but just done again and getting no errors but the stock is not changing, I have a feeling I have this set up wrong for what it needs to do

[cags]

Assuming the products table has at least a stock_level and a pnumber field, that code is correct. If nothing is updating the most likely cause is $qty level has a null value or the value $pnumber is not found in the table.

 

As a test I'd suggest echo'ing out $sql before/after running the query so you can see what is actually being sent to the database.

 

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

#execute SQL statement
$result = mysql_db_query( *****,"$SQL",$connection );
echo "$SQL";
// or if you redirect with header at some point after this
die($SQL);

[zimmo]

I have tried to echo out the result, but its showing nothing at all on the page? No error, no result nothing?

[zimmo]

Sorry just noticed a problem my fault the following was incorrect

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

 

should have been

 

while($row = mysql_fetch_array($sql_result, MYSQL_ASSOC)) {

 

Am now getting results back and they look fine to me having multiple updates.

They seem to work fine now I have fixed that problem, no wonder I was getting nothing returning. Thanks cags, have now got this working.

[cags]

Glad you fixed it, don't forget to mark it as SOLVED (button bottom left).