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

, 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) {

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) {

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:

}, $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);

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

}, $cc_last_four, NOW())”);

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

= mysqli_insert_id($r);

}, 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) {

}”); list($subtotal) = mysqli_fetch_array($r, MYSQLI_NUM);

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:

That one line simply gets replaced with:

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

Replace that line with:

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!