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."'
Your Order table should store a Product_Id (foreign key on Id in Product table).
Or is this the Product fieldname in Order?
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']}'");
}
?>We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.