That being said, implement the new functionality by following this exercise.
Exercise: Creating the Shopping Cart Admin Page
1. Use phpMyAdmin to execute and create the stored procedures described in the following step. Also, don??™t
forget to set $$ as the delimiter before executing the code.
2. Add the following data tier stored procedures to the tshirtshop database:
CHAPTER 12 ?– CREATING YOUR OWN SHOPPING CART 393
-- Create shopping_cart_count_old_carts stored procedure
CREATE PROCEDURE shopping_cart_count_old_carts(IN inDays INT)
BEGIN
SELECT COUNT(cart_id) AS old_shopping_carts_count
FROM (SELECT cart_id
FROM shopping_cart
GROUP BY cart_id
HAVING DATE_SUB(NOW(), INTERVAL inDays DAY) >= MAX(added_on))
AS old_carts;
END$$
-- Create shopping_cart_delete_old_carts stored procedure
CREATE PROCEDURE shopping_cart_delete_old_carts(IN inDays INT)
BEGIN
DELETE FROM shopping_cart
WHERE cart_id IN
(SELECT cart_id
FROM (SELECT cart_id
FROM shopping_cart
GROUP BY cart_id
HAVING DATE_SUB(NOW(), INTERVAL inDays DAY) >=
MAX(added_on))
AS sc);
END$$
3. Add the following business tier method to business/shopping_cart.php:
// Count old shopping carts
public static function CountOldShoppingCarts($days)
{
// Build SQL query
$sql = 'CALL shopping_cart_count_old_carts(:days)';
// Build the parameters array
$params = array (':days' => $days);
// Execute the query and return the results
return DatabaseHandler::GetOne($sql, $params);
}
// Deletes old shopping carts
public static function DeleteOldShoppingCarts($days)
{
// Build SQL query
$sql = 'CALL shopping_cart_delete_old_carts(:days)';
// Build the parameters array
$params = array (':days' => $days);
CHAPTER 12 ?– CREATING YOUR OWN SHOPPING CART 394
// Execute the query
DatabaseHandler::Execute($sql, $params);
}
4.
Pages:
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504