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:
$r = mysqli_query($dbc, "CALL select_categories('$sp_type')");
with direct queries:
if ($type == 'coffee') {
$r = mysqli_query($dbc, 'SELECT * FROM general_coffees ORDER by category');
} elseif ($type == 'goodies') {
$r = mysqli_query($dbc, 'SELECT * FROM non_coffee_categories ORDER by category');
}
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:
$r = mysqli_query($dbc, "CALL select_products('$sp_type', $sp_cat)");
with direct queries:
if ($type == 'coffee') {
$r = mysqli_query($dbc, 'SELECT gc.description, gc.image, CONCAT("C", sc.id) AS sku,
CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole, sc.price) AS name,
sc.stock, sc.price, sales.price AS sale_price
FROM specific_coffees AS sc INNER JOIN sizes AS s ON s.id=sc.size_id
INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_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 general_coffee_id=' . $sp_cat . ' AND stock>0
ORDER by name');
} elseif ($type == 'goodies') {
$r = mysqli_query($dbc, 'SELECT ncc.description AS g_description, ncc.image AS g_image,
CONCAT("O", ncp.id) AS sku, ncp.name, ncp.description, ncp.image,
ncp.price, ncp.stock, sales.price AS sale_price
FROM non_coffee_products AS ncp INNER JOIN non_coffee_categories AS ncc
ON ncc.id=ncp.non_coffee_category_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 non_coffee_category_id=' . $sp_cat . ' ORDER by date_created DESC');
}
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:
$r = mysqli_query ($dbc, 'CALL select_sale_items(true)');
with
$r = mysqli_query ($dbc, 'SELECT CONCAT("O", ncp.id) AS sku, sa.price AS sale_price, ncc.category, ncp.image, ncp.name, ncp.price, ncp.stock, ncp.description FROM sales AS sa INNER JOIN non_coffee_products AS ncp ON sa.product_id=ncp.id INNER JOIN non_coffee_categories AS ncc ON ncc.id=ncp.non_coffee_category_id WHERE sa.product_type="other" AND ((NOW() BETWEEN sa.start_date AND sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) )
UNION SELECT CONCAT("C", sc.id), sa.price, gc.category, gc.image, CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole), sc.price, sc.stock, gc.description FROM sales AS sa INNER JOIN specific_coffees AS sc ON sa.product_id=sc.id INNER JOIN sizes AS s ON s.id=sc.size_id INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id WHERE sa.product_type="coffee" AND ((NOW() BETWEEN sa.start_date AND sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) )');
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:
$r = mysqli_query ($dbc, "CALL select_sale_items(false)");
and replace it with:
$r = mysqli_query ($dbc, '(SELECT CONCAT("O", ncp.id) AS sku, sa.price AS sale_price, ncc.category, ncp.image, ncp.name FROM sales AS sa INNER JOIN non_coffee_products AS ncp ON sa.product_id=ncp.id INNER JOIN non_coffee_categories AS ncc ON ncc.id=ncp.non_coffee_category_id WHERE sa.product_type="other" AND ((NOW() BETWEEN sa.start_date AND sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) ) ORDER BY RAND() LIMIT 2) UNION (SELECT CONCAT("C", sc.id), sa.price, gc.category, gc.image, CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole) FROM sales AS sa INNER JOIN specific_coffees AS sc ON sa.product_id=sc.id INNER JOIN sizes AS s ON s.id=sc.size_id INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id WHERE sa.product_type="coffee" AND ((NOW() BETWEEN sa.start_date AND sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) ) ORDER BY RAND() LIMIT 2)');
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.
