product_id, sc.attributes, p.name, sc.quantity,
COALESCE(NULLIF(p.discounted_price, 0), p.price) AS unit_cost
FROM shopping_cart sc
INNER JOIN product p
ON sc.product_id = p.product_id
WHERE sc.cart_id = inCartId AND sc.buy_now;
CHAPTER 14 ?– ACCEPTING CUSTOMER ORDERS 437
?– Tip When joining shopping_cart and product, you get the product_id from product, but you could
also get it from shopping_cart; the result would be the same, because the table join is made on the
product_id column.
The stored procedure also calculates the total amount of the order by multiplying each product??™s price by its quantity.
This value is then saved as the order??™s total_amount:
-- Save the order's total amount
UPDATE orders
SET total_amount = (SELECT SUM(unit_cost * quantity)
FROM order_detail
WHERE order_id = orderId)
WHERE order_id = orderId;
In the end, the function empties the visitor??™s shopping cart by calling the shopping_cart_empty stored procedure
and returns the order??™s ID:
-- Clear the shopping cart
CALL shopping_cart_empty(inCartId);
-- Return the Order ID
SELECT orderId;
Implementing the Business Tier
The business tier of the order placing feature is made of a single method, CreateOrder. Add
this method to the ShoppingCart class inside business/shopping_cart.php:
// Create a new order
public static function CreateOrder()
{
// Build SQL query
$sql = 'CALL shopping_cart_create_order(:cart_id)';
// Build the parameters array
$params = array (':cart_id' => self::GetCartId());
// Execute the query and return the results
return DatabaseHandler::GetOne($sql, $params);
}
The method calls the shopping_cart_create_order data tier stored procedure, which creates
a new order from the shopping cart ID it receives and returns the order_id of the newly
created order.
Pages:
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556