This is my second post in a series in which I’m rewriting the stored procedures used in my “[intlink id=”1578″ type=”page”]Effortless E-Commerce with PHP and MySQL[/intlink]” 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. In this post, I’ll present an alternative version of the stored procedures—and the PHP scripts that call them—for Chapter 9, “Building a Shopping Cart.” Both 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).Chapter 9 is all about managing a shopping cart and, with exactly the same functionality, a wish list. The associated tables—carts and wish_lists—are identical in definition. The shopping cart stores the items that will be purchased soon; the wish list stores items that the customer may purchase eventually (through the use of cookies, the wish list can remember user items for weeks).
There are four stored procedures for each of these two tables. Each procedure corresponds to basic CRUD functionality: Create (i.e., INSERT), Retrieve (SELECT), Update, and Delete. In Chapter 9, all eight procedures are invoked from either cart.php or wishlist.php (Chapter 10, “Checking Out,” also has some scripts that will use them). Both scripts use a long-ish if-elseif conditional that checks for values in the URL to know when to perform INSERT and DELETE procedures. Such conditionals will be true when the user, for example, clicks an “Add to Cart” link on a product listing page or clicks a “Remove from Cart” link on the shopping cart page. UPDATE procedures are run when the shopping cart (or wish list) form is POSTed back to the page, presumably with new quantities provided for the items in the cart (or wish list).
A Product Listing in the Catalog
The Shopping Cart Form
Both PHP scripts, after whatever other stored procedures may or may not be called, execute the SELECT procedure. Let’s look at that first. (To make this post shorter, I’m only going to talk about changes required to the cart.php script from here on out; changes required for wishlist.php would be almost exactly the same.)
Near the very end of cart.php, the SELECT stored procedure is executed using this code:
$r = mysqli_query($dbc, "CALL get_shopping_cart_contents('$uid')");
Earlier in the script, the $uid variable is created, which is a unique reference to the current user. You can think of it like a session ID, although it’s not actually used by PHP sessions. To replace the stored procedure, you would just change the above line to be:
$r = mysqli_query($dbc, "SELECT CONCAT("O", ncp.id) AS sku, c.quantity, ncc.category, ncp.name, ncp.price, ncp.stock, sales.price AS sale_price
FROM carts AS c INNER JOIN non_coffee_products AS ncp ON c.product_id=ncp.id
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 c.product_type="other" AND c.user_session_id='$uid'
UNION SELECT CONCAT("C", sc.id), c.quantity, gc.category, CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole), sc.price, sc.stock, sales.price
FROM carts AS c INNER JOIN specific_coffees AS sc ON c.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 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'");
In short, all you need to do is take the big query from the stored procedure and execute it directly. Two uses of “uid” in the stored procedure, representing the internal stored procedure variable, need to be replaced with the PHP variable $uid, and wrapped in quotes (the variable within the stored procedure does not use a dollar sign, as it’s not a PHP variable, and doesn’t need to be wrapped in quotes as queries executed within stored procedures work like prepared statements).
Moving on, the cart.php script invokes the remove_from_cart() stored procedure once:
$r = mysqli_query($dbc, "CALL remove_from_cart('$uid', '$sp_type', $pid)");
Replace that with:
$r = mysqli_query($dbc, "DELETE FROM carts WHERE user_session_id='$uid' AND product_type='$sp_type' AND product_id=$pid");
Again, this is just executing the query directly, after replacing the stored procedure variables in the query with PHP variables. Note that all of these variables have already been validated prior to this point. The cart.php script also invokes remove_from_wish_list() once, which will also need to be replaced with the appropriate query.
The UPDATE stored procedure is called in cart.php, upon the form submission. In a foreach loop, you’ll find this code:
$r = mysqli_query($dbc, "CALL update_cart('$uid', '$sp_type', $pid, $qty)");
Unlike the other procedures I’ve written about thus far, the update_cart() stored procedure has a bit of logic to it. Namely, it will update the quantity in the cart if the new quantity is greater than 0 or execute the remove_from_cart() procedure if the quantity equals 0. So that logic must be present in the PHP code that replaces the above line:
if ($qty > 0) {
$r = mysqli_query($dbc, "UPDATE carts SET quantity=$qty, date_modified=NOW() WHERE user_session_id='$uid' AND product_type='$sp_type' AND product_id=$pid");
} elseif ($qty == 0) {
$r = mysqli_query($dbc, "DELETE FROM carts WHERE user_session_id='$uid' AND product_type='$sp_type' AND product_id=$pid");
}
Arguably, I’d be inclined to use prepared statements there instead (whenever you have a query being executed within a loop, it’s a good candidate for prepared statements), but I don’t want to over-complicate the changes I’m already suggesting.
This leaves us with the last stored procedure, add_to_cart(). You might think this would be a simple INSERT query, but there’s some logic required by it. This procedure will be called when the customer clicks a link. In that case, a simple INSERT is all that’s necessary. However, if the user clicks the same link again later—thereby adding to the cart something already in the cart, another INSERT should not be executed. The decision I made in the book is to assume that the customer purposefully wanted to add another quantity of the same item to the cart in such instances. So the stored procedure runs an UPDATE query when the item exists in the cart.
The first time the add_to_cart() procedure is executed in cart.php looks like this:
$r = mysqli_query($dbc, "CALL add_to_cart('$uid', '$sp_type', $pid, 1)");
The final 1 indicates that one quantity of the item is being added, which is the default behavior when an “Add to Cart” link is clicked. You would replace that code with:
$r = mysqli_query($dbc, "SELECT id FROM carts where user_session_id='$uid' AND product_type='$sp_type' AND product_id=$pid");
if (mysqli_num_rows($r) == 1) { // Exists in cart, UPDATE!
list($cid) = mysqli_fetch_array($r, MYSQLI_NUM);
$r = mysqli_query($dbc, "UPDATE carts SET quantity=quantity+1, date_modified=NOW() WHERE id=$cid");
} else { // Not in cart, INSERT!
$r = mysqli_query($dbc, "INSERT INTO carts (user_session_id, product_type, product_id, quantity) VALUES ('$uid', '$sp_type', $pid, 1)");
}
The first query is basically asking if the user already has this item in their cart. If so, the cart’s id value is selected and assigned to a MySQL variable, @cid. The conditional then checks if one row was returned. If so, an UPDATE query is run, using the just-created MySQL variable in the WHERE clause (if my use of @cid is confusing, I can add explanations of its purpose and usage). If one row wasn’t returned, an INSERT query is executed.
Now the cart.php script also calls the add_to_cart() stored procedure in another instance: If the customer has something in their wish list and they click the “Move to Cart” link, the item gets added to the cart and removed from the wish list. The use of add_to_cart() is exactly the same, except that whatever quantity of the item is in the user’s wish list needs to be transferred, too. In that case, the $qty variable represents that value. So you’d also replace this line:
$r = mysqli_query($dbc, "CALL add_to_cart('$uid', '$sp_type', $pid, $qty)");
With the code above, except you’d use $qty instead of 1.
And that’s it! These procedures are a bit more complicated than those in Chapter 8, as they have some internal logic (whereas Chapter 8’s procedures are merely SELECT statements). But it’s still not hard to rewrite these procedures using standard PHP-MySQL, as I’ve just shown. In a subsequent post, I’ll rewrite the procedures from Chapter 10 using standard PHP-MySQL. One of those procedures has quite a lot of logic to it, and will be the most complicated to translate.
