You need to do this at the outset to find out what order_id was generated for the new order. Remember
that the order_id field is an AUTO_INCREMENT column and is automatically generated by the database, so you
need to retrieve its value after inserting a record into orders:
-- Insert a new record into orders and obtain the new order ID
INSERT INTO orders (created_on) VALUES (NOW());
-- Obtain the new Order ID
SELECT LAST_INSERT_ID() INTO orderId;
This is the basic mechanism of extracting the newly generated ID. After the INSERT statement, you can obtain
the last value generated for an AUTO_INCREMENT by reading LAST_INSERT_ID(). The functionality is pretty
straightforward, but for more details, you can check its official documentation page at http://dev.mysql.com/
doc/refman/5.1/en/getting-unique-id.html.
You read the value of LAST_INSERT_ID(), and save it to a variable named orderId. Using the orderId value,
you add the order_detail records by gathering information from the product and shopping_cart tables. You
get the list of the products and their quantities from shopping_cart, get their names and prices from product,
and save these records one by one to the order_detail table.
-- Insert order details in order_detail table
INSERT INTO order_detail (order_id, product_id, attributes,
product_name, quantity, unit_cost)
SELECT orderId, p.
Pages:
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555