How to insert multiple products into order details table-CodePudding

I have 2 tables order and order details as can be seen

My question is how can I proceed to insert multiple products into order_details after creating an order.

I initially did the following procedure but it will be valid for only 1 product. I want to be able to store multiple products at once.

insert
    into
    order_online.`order`
( order_date,
    shipping_method_fk,
    order_total_price,
    order_customer_id_fk,
    order_customer_address_id_fk)
values(current_date(),
shippingMethodID,
totalPrice,
customerID,
customerAddressID);

insert
    into
    order_online.order_details
    
( order_details_product_id_fk,
    order_id_fk,
    order_details_product_qty)
values(
productID,
LAST_INSERT_ID(),
productQty
);

CodePudding user response：

You need to use multiple INSERT statements to insert multiple data records. However, if you are worried about inconsistencies while executing multiple SQL statements, you might want to club them together into a transaction:

START TRANSACTION;
INSERT INTO ...;
...
COMMIT;

This way, all statemets between START TRANSACTION and COMMIT are executed as one single statement and the database is always in a consistent state.