Bas Bas - 7 months ago 8
SQL Question

MySQL automatically updating field when inserting data

I'm making a web application to make customers order items for antyhing. For that I've made a MySQL database wich has the following tables:


  • customers

  • orders

  • orders-items

  • products



In the
customers
table is all the information about the person such as:


  • The customer ID, for the primary key and auto increment (id)

  • The first name (first_name)

  • The last name (last_name)

  • The email adress (email_adress)

  • Information about the customer (customer_info)



Example:

enter image description here

In the
orders
table is all the specific information about it such as:


  • The order ID, for the primary key and auto increment (id)

  • Wich customer it ordered, linked with
    id
    field from the
    customers
    table (customer_id)

  • Order information (order_info)

  • The location where the order needs to go to (location)

  • The total price the customer has to pay (total_price)

  • When the order was created (created)



Example:

enter image description here

In the
orders-items
table are all the items wich every customer ordered, this is being linked by the
order-id
from the previous table.


  • The ID, for primary key and auto increment, not used for any relation (id)

  • The order ID, used for wich product is for wich order. This is linked with the
    id
    field from the
    orders
    table (order_id)

  • The product ID, this is used for what product they ordered, this is linked with the id field from the
    products
    table. (product_id)

  • The amount of this product they ordered (quantity)



Example:

enter image description here

In the
products
table is all the information about the products:


  • The ID, for primary key and auto incrementing, This is linked with the
    product_id
    field from the
    order_items
    table (id)

  • The name of the product (name)

  • The description of the product (description)

  • The price of the product (price)



Example:

enter image description here

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 dont keep track of the
total_prices
the
customers
have to pay?

Answer

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 ;
Comments