i have two tables in a single database sales... one is product other is order... i want someones help to know how can i do one operation.... i.e

when i click on order i want to subtract that one order from the product table....

can u help me with the query?

could you give us the table structure? it's hard to write a query without knowing what the fields are. any more details will help.

could you give us the table structure? it's hard to write a query without knowing what the fields are. any more details will help.

ok sure..

Order table

Id int
Product text
Dealer text
Quantity int

Product

Id int
Name text
Brand text
Expirydate date
Mfgdate date
Department text
Cost int
Quantity int

If somene orders 1 product:

mysql_query("UPDATE Product SET Quantity=Quantity-1 WHERE Id='".$id."'");

Replace $id with your variable name foe the product id and if someone orders more than 1 of the same product store the total number in a variable like $total and change Quantity-1 to Quantity-'".$total."'

Member Avatar
diafol

Your Order table should store a Product_Id (foreign key on Id in Product table).
Or is this the Product fieldname in Order?

commented: exactly +5

So, the basic idea is that you want to subtract a quantity from the products table when someone makes an order?

<?php 

//assuming the order already exists

$query = "select * from `Order` where ID = {$id} LIMIT 1";

$result = mysql_query($query);

//don't forget to validate the result bool(false) is failed or no rows

while ( $row = mysql_fetch_assoc($result) ) {
	//assuming the field order.Product is the same as product.Name
	
	//pretty much what @simplypixie said 
	mysql_query("UPDATE Product SET Quantity=(Quantity-{$row['Quantity']}) WHERE Name='{$row['Product']}'");
}

?>