• /
  • /
  • Rewriting the E-Commerce Stored Procedures with Standard PHP-MySQL #3, Chapter 10

Rewriting the E-Commerce Stored Procedures with Standard PHP-MySQL #3, Chapter 10

December 28, 2010

This is my third post in a series in which I’m rewriting the stored procedures used in my “Effortless E-Commerce with PHP and MySQL” book as standard PHP and MySQL. Although stored procedures offer lots of benefits over standard PHP-MySQL logic, not everyone has an environment that supports stored procedures, so I’m writing these posts to help out those readers. [intlink id=”2084″ type=”post”]In my first post[/intlink], I rewrote the examples from Chapter 8, “Creating a Catalog”. Those examples are really simple, running only basic SELECT queries. [intlink id=”2151″ type=”post”]In the second post[/intlink], I presented an alternative version of the stored procedures—and the PHP scripts that call them—for Chapter 9, “Building a Shopping Cart.” Those procedures aren’t too complex either. In this post, I’ll rewrite the stored procedures and update the PHP scripts that call them for Chapter 10, “Checking Out.” This chapter has the most complicated—and important—stored procedures, so the PHP scripts will need to be reworked more than in the other chapters. All three chapters are from the third part of the book, in which an e-commerce site is developed for the sake of selling physical products (viz., coffee).

The first procedure in Chapter 10 is add_customer(), called in the checkout.php script, after the customer has successfully completed the shipping form. The code in the PHP script calls the procedure, which will add a record to the customers table. The procedure also returns the customer ID via an outbound argument named @cid. This value needs to be stored in the session for later use. Here is the original code:

$r = mysqli_query($dbc, "CALL add_customer('$e', '$fn', '$ln', '$a1', '$a2', '$c', '$s', $z, $p, @cid)");

// Confirm that it worked:
if ($r) {

// Retrieve the customer ID:
$r = mysqli_query($dbc, 'SELECT @cid');
if (mysqli_num_rows($r) == 1) {

    list($_SESSION['customer_id']) = mysqli_fetch_array($r);

Without a stored procedure, you would replace that code with:

$r = mysqli_query($dbc, "INSERT INTO customers VALUES (NULL, '$e', '$fn', '$ln', '$a1', '$a2', '$c', '$s', $z, $p, NOW())");

// Confirm that it worked:
if (mysqli_num_rows($r) == 1) {

    // Retrieve the customer ID:
    $_SESSION['customer_id'] = mysqli_insert_id($r);

There is one caveat, however: the variables aren’t safe to use in the query as they haven’t been sanctified (they’ve been validated, though). So I would run all the strings through an escaping function, such as mysqli_real_escape_string() first. You could do so when assigning values to the variables earlier in the script. You’ll also need to remove one closing curly bracket from the original PHP script, as this structure has one fewer conditionals.

Next, the customer is taken to billing.php, where they’re presented with a form for entering their billing information. The form data is then validated and the add_order() procedure is called. This procedure has the most logic, as it needs to:

  • Add a record to the orders table.
  • Get the new order ID value
  • Add one record to the order_contents table for each item in the order
  • Calculate the subtotal of the order
  • Calculate the total of the order (which includes the shipping)
  • Add the total of the order to the orders table
  • Return both the order total and the order ID

The original stored procedure does all this using two INSERT queries, three SELECT queries, and one UPDATE. (This is an excellent example of the benefit of stored procedures, as the PHP script only needs to execute two queries to do all of the above.)

The billing.php script has the following code:

$r = mysqli_query($dbc, "CALL add_order({$_SESSION['customer_id']}, '$uid', {$_SESSION['shipping']}, $cc_last_four, @total, @oid)");

// Confirm that it worked:
if ($r) {

    // Retrieve the order ID and total:
    $r = mysqli_query($dbc, 'SELECT @total, @oid');
    if (mysqli_num_rows($r) == 1) {
        list($order_total, $order_id) = mysqli_fetch_array($r);

        // Store the information in the session:
        $_SESSION['order_total'] = $order_total;
        $_SESSION['order_id'] = $order_id;

You’d need to replace that code with all of the following:

$r = mysqli_query($dbc, "INSERT INTO orders (customer_id, shipping, credit_card_number, order_date) VALUES
({$_SESSION['customer_id']}, {$_SESSION['shipping']}, $cc_last_four, NOW())");

if (mysqli_num_rows($r) == 1) {

    // Get the order ID:
    $_SESSION['order_id'] = mysqli_insert_id($r);

    // Add the items to the order_contents table:
    $r = mysqli_query($dbc, "INSERT INTO order_contents (order_id, product_type, product_id, quantity, price_per)
SELECT {$_SESSION['order_id']}, c.product_type, c.product_id, c.quantity, IFNULL(sales.price, ncp.price) FROM carts AS c
INNER JOIN non_coffee_products AS ncp ON c.product_id=ncp.id
LEFT OUTER JOIN sales ON (sales.product_id=ncp.id AND sales.product_type='other' AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) )
WHERE c.product_type="other" AND c.user_session_id='$uid'
UNION
SELECT {$_SESSION['order_id']}, c.product_type, c.product_id, c.quantity, IFNULL(sales.price, sc.price) FROM carts AS c
INNER JOIN specific_coffees AS sc ON c.product_id=sc.id
LEFT OUTER JOIN sales ON (sales.product_id=sc.id AND sales.product_type='coffee' AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) )
WHERE c.product_type="coffee" AND c.user_session_id='$uid'");

    if (mysqli_affected_rows($r) > 0) {

        // Calculate and fetch the subtotal:
        $r = mysqli_query($dbc, "SELECT SUM(quantity*price_per) FROM order_contents WHERE order_id={$_SESSION['order_id]}");
        list($subtotal) = mysqli_fetch_array($r, MYSQLI_NUM);

       // Calculate, update, and store the order total:
       $r = mysqli_query($dbc, "UPDATE orders SET total = ($subtotal + {$_SESSION['shipping']}) WHERE id={$_SESSION['order_id]}");
       $_SESSION['order_total'] = $subtotal + $_SESSION['shipping'];

So there’s all the stored procedure logic in PHP. For an explanation of the queries, see the book. To use this in a script, you’ll need to complete all the conditionals as appropriate.

Later, the billing.php script calls the add_transaction() procedure, after processing the payment:

$r = mysqli_query($dbc, "CALL add_transaction($order_id, '{$data['x_type']}', $response_array[9], $response_array[0], '$reason', $response_array[6], '$response')");

That one line simply gets replaced with:

$r = mysqli_query($dbc, "INSERT INTO transactions VALUES (NULL, {$_SESSION['order_id'], {$data['x_type']}, $response_array[9], $response_array[0], '$reason', $response_array[6], '$response', NOW())");

This takes us to final.php, where the clear_cart() procedure is called:

$r = mysqli_query($dbc, "CALL clear_cart('$uid')");

Replace that line with:

$r = mysqli_query($dbc, "DELETE FROM carts WHERE user_session_id='$uid'");

And that’s it for final.php.

Now three scripts in Chapter 10—checkout.php, billing.php, and email_receipt.php—also invoke the get_order_contents() procedure. I explain how to replace it with standard PHP-MySQL in the second post in this series.

That’s it for converting all of the stored procedures in the book with standard PHP and MySQL. As you can see, it’s generally not that hard, depending upon how complex the stored procedure itself is.

Let me know if you have any questions and thanks for your interest in the book!

In MySQL, PHP, Web Development , ,
permalink