I'm making a web application to make customers order items for anything. For that I've made a MySQL database which has the following tables:
customers
orders
orders-items
products
In the customers
table is all the information about the person such as:
Example:
In the orders
table is all the specific information about it such as:
id
field from the customers
table (customer_id)Example:
In the orders-items
table are all the items which every customer ordered, this is being linked by the order-id
from the previous table.
id
field from the orders
table (order_id)products
table. (product_id)Example:
In the products
table is all the information about the products:
product_id
field from the order_items
table (id)Example:
Question:
I've got this query:
SELECT `orders-items`.`order_id` , SUM(`orders-items`.`quantity`* `products`.`price`) total
FROM `orders-items`
INNER JOIN `Products` ON `orders-items`.`products_id` = `products`.`id`
And it shows me a list of all the total prices every order_id
has to pay.
But how do I make this so that this value of the total_price
every order_id
has to pay is automatticly inserted into the orders
table inside the total_price
field at the right order_id
when inserting a product into my orders-list
table?
Or is it still better to not keep track of the total_prices
the customers
have to pay?
A couple things to consider.
Having a total_price
for itself is redundant. You can learn this total by summing the prices of this order's items at any time. It might be interesting to have it for performance reasons, but is this really necessary for your scenario? It rarely is.
Having a price
on each order_item
in the other hand would be useful. And the why is because thoses products prices might change in the future and you don't want to lose information of for how much they were sold at the time of that particular sale.
In any case, you can update your total_price
using triggers like this:
DELIMITER $$
CREATE TRIGGER order_items_insert AFTER INSERT ON `orders-items` FOR EACH ROW
BEGIN
UPDATE orders o INNER JOIN (SELECT i.order_id id, SUM(i.quantity * p.price) total_price FROM `orders-items` i INNER JOIN products p ON p.id = i.products_id AND i.order_id = new.order_id) t ON t.id = o.id SET o.total_price = t.total_price;
END$$
CREATE TRIGGER order_items_update AFTER UPDATE ON `orders-items` FOR EACH ROW
BEGIN
UPDATE orders o INNER JOIN (SELECT i.order_id id, SUM(i.quantity * p.price) total_price FROM `orders-items` i INNER JOIN products p ON p.id = i.products_id AND i.order_id = new.order_id) t ON t.id = o.id SET o.total_price = t.total_price;
END$$
CREATE TRIGGER order_items_delete AFTER DELETE ON `orders-items` FOR EACH ROW
BEGIN
UPDATE orders o INNER JOIN (SELECT i.order_id id, SUM(i.quantity * p.price) total_price FROM `orders-items` i INNER JOIN products p ON p.id = i.products_id AND i.order_id = old.order_id) t ON t.id = o.id SET o.total_price = t.total_price;
END$$
DELIMITER ;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments