0

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?

4
Contributors
5
Replies
6
Views
5 Years
Discussion Span
Last Post by qazplm114477
0

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.

0

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

0

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."'

1

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

Votes + Comments
exactly
0

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

?>
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.