created_on DESC;
END$$
8. Execute the following code, which creates the orders_get_order_short_details stored procedure:
-- Create orders_get_order_short_details stored procedure
CREATE PROCEDURE orders_get_order_short_details(IN inOrderId INT)
BEGIN
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
WHERE o.order_id = inOrderId;
END$$
9. Execute this code, which creates the customer_get_customers_list stored procedure:
-- Create customer_get_customers_list stored procedure
CREATE PROCEDURE customer_get_customers_list()
BEGIN
SELECT customer_id, name FROM customer ORDER BY name ASC;
END$$
Modifying the Business Tier
We need to make a few changes to the business tier as well. We need to modify the UpdateOrder()
method of the Orders class and add three new methods to the Orders and Customers classes:
??? GetByCustomerId()
??? GetOrderShortDetails()
??? GetCustomersList()
CHAPTER 17 ?– STORING CUSTOMER ORDERS 550
These new methods support the new administrative functionality we??™ll need in the admin_
orders.tpl presentation tier template. Create them by following the steps of the exercise.
Exercise: Updating the Business Tier
1. Add a new method named GetByCustomerId() to the Orders class in business/orders.php:
// Gets all orders placed by a specified customer
public static function GetByCustomerId($customerId)
{
// Build the SQL query
$sql = 'CALL orders_get_by_customer_id(:customer_id)';
// Build the parameters array
$params = array (':customer_id' => $customerId);
// Execute the query and return the results
return DatabaseHandler::GetAll($sql, $params);
}
2.
Pages:
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662