Results 1 to 3 of 3

Thread: Subtracting data

  1. #1
    Join Date
    Jul 2004

    Subtracting data

    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.

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

  2. #2
    Join Date
    Jan 2007
    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:
        order_item oi
        left join inventory i on (i.inventory_id=oi.inventory_id)
        i.stock_level = i.stock_level - oi.qty
        oi.order_id = '{$order_id_value}'
    Make sense?
    Last edited by svidgen; 10-05-2009 at 10:20 AM. Reason: forgot a few things in the query ...
    Jon Wire

    thepointless.com | rounded corner generator

    I agree with Apple. Flash is just terrible.

    Use CODE tags!

  3. #3
    Join Date
    Jul 2004
    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center