Rewriting the E-Commerce Stored Procedures with Standard PHP-MySQL

In the second e-commerce example in my “Effortless E-Commerce with PHP and MySQL” book, stored procedures were used for all of the public-side database-related functionality. This includes: the displaying of product categories, specific items, and sale items; cart and wish list management (adding items, updating quantities, removing items); and order submissions. As I write in the book, stored procedures offer tons of benefits, the most critical being:

• Greatly improved security
• Better performance
• Compartmentalization of code (i.e., adherence to an MVC approach)

However, not everyone can use stored procedures, in particular those on some shared hosting environments. One reader recently said (in the forums) that they couldn’t use stored procedures with their host, so I volunteered to rewrite some of the stored procedures as standard PHP and MySQL. In this post, I’ll rewrite the procedures, and corresponding PHP scripts, from Chapter 8, “Creating a Catalog”.The first stored procedure created in Chapter 8 is select_categories(). This procedure takes one argument: a string with a value of coffee or other. The procedure then fetches either all the coffee types or non-coffee categories accordingly. The procedure is called by the shop.php script, which uses the value of $_GET\['type'\] to know what value to pass to the procedure. To use shop.php without the stored procedure, replace this line:

with direct queries:

And that’s all there is to it! No need to even change the view file.

The next stored procedure, select_products(), takes two arguments: a string with a value of coffee or other, and an integer representing the coffee or non-coffee category ID. The procedure is called by browse.php, which expects to receive both the broad type— coffee or goodies—in the URL, along with the specific category ID. To change this to a non-stored procedure version, you again need to replace this line:

with direct queries:

Both queries use the PHP variable $sp_cat, which is the integer. Again, no other changes are required to the code or the view file.

The third stored procedure created in Chapter 8 is select_sale_items(). It will either return every sale item or just a couple, depending upon how it’s called. In the sales.php page, the procedure selects them all. To update that script, replace:

with

The home page, index.php, also calls this same stored procedure, but passes along a value of false, indicating that not all items should be fetched. So find this code in index.php:

and replace it with:

Again, that’s it. To save yourself horrendous typing, you can just copy and paste all of these queries from the stored procedure definitions.

Admittedly these stored procedure, which just run SELECT queries, are the easiest in the book to translate into standard PHP-MySQL. In future posts, I’ll demonstrate how to rewrite the more complicated stored procedures found in Chapters 9 and 10.