2. PHPRunner General questions
  3. Some Automatic Additions on Insert
This topic is locked

Some Automatic Additions on Insert

 		3/5/2012 11:53:47 PM
PHPRunner General questions
S
 spacle2xl author

Please am using PHPRunner 5.2 (Build 5482). Please I need help. I have the following tables and columns listed below:
ORDER DETAILS

Category

Product Name

Quantity Ordered
PRODUCTS

Product ID

Product Name

supplier

Category

Quantity Per Unit

Units Received

Units In Stock

Units On Orders

Product Status
In PRODUCTS Table, 'Units Received' should be automatically added to what's already in 'Units In Stock', and in ORDER DETAILS Table, all the quantities ordered for each product should be calculated and added into 'Units On Orders' on PRODUCTS Table
Please help me. Thank you
kujox 3/6/2012

error
kujox 3/6/2012



Please am using PHPRunner 5.2 (Build 5482). Please I need help. I have the following tables and columns listed below:
ORDER DETAILS

Category

Product Name

Quantity Ordered
PRODUCTS

Product ID

Product Name

supplier

Category

Quantity Per Unit

Units Received

Units In Stock

Units On Orders

Product Status
In PRODUCTS Table, 'Units Received' should be automatically added to what's already in 'Units In Stock', and in ORDER DETAILS Table, all the quantities ordered for each product should be calculated and added into 'Units On Orders' on PRODUCTS Table
Please help me. Thank you


On the units on orders.



$sql = "UPDATE products SET units_in_stock=units_in_stock+units_received,units_on_orders=(SELECT SUM(quantity_ordered) FROM order_details WHERE units_on_orders.product_name=products.product_name)";
// then clear down the units_received field
$sql = "UPDATE products SET units_received=0";
// you could try and complete both actions in one statement as follows
$sql = "UPDATE products SET

units_in_stock=units_in_stock+units_received,

units_received=0,

units_on_orders=(SELECT SUM(quantity_ordered) FROM order_details WHERE units_on_orders.product_name=products.product_name)";


I would use the product_id field just in case you have 2 items with the same product name, you would have to add product_id to the units_on_order table