Because the changes are
extensive, we??™ll deal with them separately for the data, business, and presentation tiers.
Modifying the Data Tier
We need to make several changes here. We??™ll update these stored procedures from the
database:
??? orders_get_most_recent_orders
??? orders_get_orders_between_dates
??? orders_get_orders_by_status
??? orders_get_order_info
??? orders_update_order
We??™ll create three new stored procedures:
??? orders_get_by_customer_id
??? orders_get_order_short_details
??? customer_get_customers_list
Exercise: Updating the Data Tier
1. Use phpMyAdmin to create the stored procedures described in the following steps. Don??™t forget to set the $$
delimiter before executing the code of eachstep.
CHAPTER 17 ?– STORING CUSTOMER ORDERS 547
2. Delete the old orders_get_most_recent_orders stored procedure, and create a new one by executing
this code:
-- Drop orders_get_most_recent_orders stored procedure
DROP PROCEDURE orders_get_most_recent_orders$$
-- Create orders_get_most_recent_orders stored procedure
CREATE PROCEDURE orders_get_most_recent_orders(IN inHowMany INT)
BEGIN
PREPARE statement FROM
"SELECT o.order_id, o.total_amount, o.created_on,
o.shipped_on, o.status, c.name
FROM orders o
INNER JOIN customer c
ON o.customer_id = c.customer_id
ORDER BY o.created_on DESC
LIMIT ?";
SET @p1 = inHowMany;
EXECUTE statement USING @p1;
END$$
3.
Pages:
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659