Creating a Basic Shopping System | PHP Essentials, 2nd Edition

With inventory items happily existing in the database tables created previously in this chapter, you can now display your products online within a shopping environment, and allow people to put these items in a shopping cart. The remainder of this chapter will take you through the process of creating storefront menus and tracking selected items, in preparation for checkout.

Displaying Your Product Catalog

Unlike with the administration menu created in the previous chapter, you don't have to worry about PHP authentication code at the beginning of every page of your store. However, as you'll be tracking, per user, items in a shopping cart, you'll want to start/enable a session using session_start() at the beginning of each script.

Let's jump right into creating the storefront menu. For the sake of brevity, we'll use two types of pre-sorted user views:

View Products by Category
View Products Alphabetically By Title

You can present user views of your catalog, based on any information found in your table. For example, you could show all items sorted alphabetically by author, publisher, price, and so forth.

Open your text editor, create a file called shop_menu.php, and add the session_start() function at the beginning. Next, create a basic HTML menu containing the two menu items. Use the file name shop_viewbycat.php for the first link and shop_viewalpha.php for the second link.

The code for this little menu should look something like this:

 <? session_start(); ?> <HTML> <HEAD> <TITLE>XYZ Company Shopping Menu</TITLE> </HEAD> <BODY> <h1>XYZ Company Shopping Menu</h1> <p>Select an option:</p> <ul> <li><a href="shop_viewbycat.php">View Products by Category</a> <li><a href="shop_viewalpha.php">View All Products Alphabetically</a> </ul> </BODY> </HTML>

Now that you've built the menu, it's time to build the pages behind it, starting with View Products by Category. Create a file called shop_viewbycat.php and add the session_start() function, followed by the basic MySQL connection code you've been using throughout the book.

The goal of this script is to display the name of each category that contains books, followed by links to the books themselves. The PHP code snippet, with SQL query, for this portion of the script would look something like this:

 // formulate and execute the query $getCats = "SELECT DISTINCT CATEGORY FROM MASTER_PRODUCTS ORDER BY CATEGORY" $getCats_res = mysql_query($getCats) or die (mysql_error()); if (@mysql_num_rows($getCats_res) < 1))  {         $display_block = "<P>Sorry, no categories exist!</p>"; } else {         //categories exist, so get them and then get information         while ($cat_row = @mysql_fetch_array($getCats_res)) {         $category = stripslashes($cat_row['CATEGORY']);         //now start the display_block and then get items         $display_block .= "<P><strong>$category</strong></p>         <ul>";         $getItems = "SELECT ID, TITLE, AUTHOR FROM MASTER_PRODUCTS WHERE         CATEGORY = '$category' ORDER BY TITLE";         $getItems_res = @mysql_query($getItems_res)  or die (mysql_error());         while ($item_row = @mysql_fetch_array($getItems_res)) {                 $id - $item_row['ID'];                 $title = stripslashes($item_row['TITLE']);                 $author = stripslashes($item_row['AUTHOR']);                 $display_block .= "<li><a href=\"shop_iteminfo.php?id=$id\">                 <strong>$title</strong></a> <em>(by $author)</em>";         }         $display_block .= "</ul>";         } }

If you follow along with the code, you'll see that it performs two basic looping actions, both of which you've seen before. The result of these actions is a text string called $display_block, which you will stick in the middle of your basic HTML template:

 <HTML> <HEAD> <TITLE>XYZ Company Shopping Menu: View by Category</TITLE> </HEAD> <BODY> <h1>XYZ Company Shopping : Category List</h1> <? echo "$display_block"; ?> </BODY> </HTML>

Put all of your code together and you have something like this:

 <? //start a session session_start(); // create connection; substitute your own information! $conn = mysql_connect("localhost","joeuser","34Nhjp") or die(mysql_error()); // select database; substitute your own database name $db = mysql_select_db("MyDB", $conn) or die(mysql_error()); // formulate and execute the query $getCats = "SELECT DISTINCT CATEGORY FROM MASTER_PRODUCTS ORDER BY CATEGORY"; $getCats_res = mysql_query($getCats) or die (mysql_error()); if (@mysql_num_rows($getCats_res) < 1) {        $display_block = "<P>Sorry, no categories exist!</p>"; } else {        //categories exist, so get them and then get information        while ($cat_row = @mysql_fetch_array($getCats_res)) {                $category = stripslashes($cat_row['CATEGORY']);                //now start the display_block and then get items                $display_block .= "<P><strong>$category</strong></p>                <ul>";                $getItems = "SELECT ID, TITLE, AUTHOR FROM MASTER_PRODUCTS                        WHERE CATEGORY = '$category' ORDER BY TITLE";                $getItems_res = @mysql_query($getItems)                        or die (mysql_error());                while ($item_row = @mysql_fetch_array($getItems_res)) {                        $id = $item_row['ID'];                        $title = stripslashes($item_row['TITLE']);                        $author = stripslashes($item_row['AUTHOR']);                        $display_block .= "<li>                        <a href=\"shop_iteminfo.php?id=$id\">                        <strong>$title</strong></a> <em>(by $author)</em>":                }                $display_block .= "</ul>":        } } ?> <HTML> <HEAD> <TITLE>XYZ Company Shopping Menu: View by Category</TITLE> </HEAD> <BODY> <h1>XYZ Company Shopping : Category List</h1> <? echo "$display_block"; ?> </BODY> </HTML>

Save this file and place it on your Web server. Go to the main shopping menu and click on the View Products by Category link. You should see something like that in Figure 7.10.

click to expand
Figure 7.10: XYZ Company products listed by category

The second link on the main shopping menu goes to the View Products Alphabetically By Title script. This script looks a lot like the shop_viewbycat.php script, minus the outer loop. The "guts" of this script look something like this:

 // formulate and execute the query $getItems = "SELECT ID, TITLE, AUTHOR FROM MASTER_PRODUCTS ORDER BY TITLE"; $getItems_res = @mysql_query($getItems) or die (mysql_error()); if (@mysql_num_rows($getItems_res) < 1) {        $display_block = "<P>Sorry, no items exist!</p>"; } else {        //now start the display_block and then get items        $display_block  .= "<ul>";        while ($item_row = @mysql_fetch_array($getItems_res)) {                $id = $item_row['ID'];                $title = stripslashes($item_row['TITLE']);                $author = stripslashes($item_row['AUTHOR']);                $display_block .= "<li>                        <a href=\"shop_iteminfo.php?id=$id\">                        <strong>$title</strong></a> <em>(by $author)</em>";        } }

There's no need for an outer loop, as all you're doing is retrieving information on all records alphabetically, regardless of category. The entire script looks something like this:

 <? session_start(); // create connection; substitute your own information! $conn = mysql_connect("localhost","joeuser","34Nhjp") or die(mysql_error()); // select database; substitute your own database name $db = mysql_select_db("MyDB", $conn) or die(mysql_error()); // formulate and execute the query $getItems = "SELECT ID, TITLE, AUTHOR FROM MASTER_PRODUCTS ORDER BY TITLE"; $getItems_res = @mysql_query($getItems) or die (mysql_error()); if (@mysql_num_rows($getItems_res) < 1) {        $display_block = "<P>Sorry, no items exist!</p>"; } else {        //now start the display_block and then get items        $display_block  .= "<ul>";        while ($item_row = @mysql_fetch_array($getItems_res)) {                $id = $item_row['ID'];                $title = stripslashes($item_row['TITLE']);                $author = stripslashes($item_row['AUTHOR']);                $display_block .= "<li>                        <a href=\"shop_iteminfo.php?id=$id\">                        <strong>$title</strong></a> <em>(by $author)</em>";                } } ?> <HTML> <HEAD> <TITLE>XYZ Company Shopping Menu: View Items Alphabetically</TITLE> </HEAD> <BODY> <h1>XYZ Company Shopping : Alphabetical List</h1> <? echo "$display_block"; ?> </BODY> </HTML>

Save this file and place it on your Web server. Go to the main shopping menu and click on the View All Products Alphabetically link. You should see something like that in Figure 7.11.

click to expand
Figure 7.11: All XYZ Company products listed alphabetically

The last step in your catalog display code is the product details template, called shop_iteminfo.php. Open a text file with that name, start a session, and check for the all-important value for ID, which is passed in the query string of the link.

 <? session_start(); if (!($_GET[id])) {        header("Location: http://www.yourcompany.com/show_menu.php");        exit; }

Next, add the database connectivity code and issue the following query, which is used to retrieve all fields for the selected record:

 $sql = "SELECT * FROM MASTER_PRODUCTS WHERE ID = '$_GET[id]'";

Loop through the result set for this record, assigning names to the extracted information. You'll use these values to populate a display template, such as the following one:

 <h1>XYZ Company Shopping : Book Details</h1> <h2><? echo "$title"; ?></h2> <P><? echo "$info_blurb"; ?></p> <ul> <li><strong>Author:</strong> <? echo "$author"; ?> <li><strong>Publisher:</strong> <? echo "$publisher"; ?> <li><strong>ISBN:</strong> <? echo "$isbn"; ?> <li><strong>Category:</strong> <? echo "$category"; ?> <li><strong>Type:</strong> <? echo "$type"; ?> <li><strong>Number of Pages:</strong> <? echo "$page_num"; ?> </ul> <h3><font color="red">Only <? echo "\$ $price"; ?>!</font></h3>

As this is a shopping system, you'll want people to be able to purchase this item, so include the Add to Shopping Cart form for this item. Assume that the form action is called shop_addtocart.php, and use this as the form action:

 <form method="post" action="shop_addtocart.php">

You'll also need to track the item, item title, item price, and quantity, so create hidden fields for the values you know (ID, title, price) and a text field for the value completed by the user (quantity):

 <input type="hidden" name="sel_item" value="<? echo "$id"; ?>"> <input type="hidden" name="sel_item_title" value="<? echo "$title"; ?>"> <input type="hidden" name="sel_item_price" value="<? echo "$price"; ?>"> <em>Quantity:</em> <input type="text" name="sel_item_qty" value="1" size=3>

Add the submit button, and close the form:

 <P><input type="submit" name="submit" value="Add to Shopping Cart"></p> </form>

From start to finish, the product details template looks something like this:

 <? session_start(); if (!($_GET[id])) {        header("Location: http://www.yourcompany.com/show_menu.php");        exit; } // create connection; substitute your own information! $conn = mysql_connect("localhost","joeuser","34Nhjp") or die(mysql_error()); // select database; substitute your own database name $db = mysql_select_db("MyDB", $conn) or die(mysql_error()); // formulate and execute the query $sql = "SELECT * FROM MASTER_PRODUCTS WHERE ID = '$_GET[id]'"; $result = @mysql_query($sql) or die(mysql_error()); if (@mysql_num_rows($result) < 1) {        header("Location: http://www.yourcompany.com/show_menu.php");        exit; } else {        while ($rec = @mysql_fetch_array($result)) {                $isbn = $rec['ISBN'];                $title = stripslashes($rec['TITLE']);                $author = stripslashes($rec['AUTHOR']);                $publisher = stripslashes($rec['PUBLISHER']);                $category = stripslashes($rec['CATEGORY']);                $type = stripslashes($rec['TYPE']);                $info_blurb = stripslashes($rec['INFO_BLURB']);                $page_num = $rec['PAGE_NUM'];                $price = $rec['PRICE'];        } } ?> <HTML> <HEAD> <TITLE>XYZ Company Shopping : Book Details</TITLE> </HEAD> <BODY> <h1>XYZ Company Shopping : Book Details</h1> <h2><? echo "$title"; ?></h2> <P><? echo "$info_blurb"; ?></p> <ul> <li><strong>Author:</strong> <? echo "$author"; ?> <li><strong>Publisher:</strong> <? echo "$publisher"; ?> <li><strong>ISBN:</strong> <? echo "$isbn"; ?> <li><strong>Category:</strong> <? echo "$category"; ?> <li><strong>Type:</strong> <? echo "$type"; ?> <li><strong>Number of Pages:</strong> <? echo "$page_num"; ?> </ul> <h3><font color="red">Only <? echo "\$ $price"; ?>!</font></h3> <form method="post" action="shop_addtocart.php"> <input type="hidden" name="sel_item" value="<? echo "$id"; ?>"> <input type="hidden" name="sel_item_title" value="<? echo "$title"; ?>"> <input type="hidden" name="sel_item_price" value="<? echo "$price"; ?>"> <em>Quantity:</em> <input type="text" name="sel_item_qty" value="1" size=3> <P><input type="submit" name="submit" value="Add to Shopping Cart"></p> </form> </BODY> </HTML>

Save this file and place it on your Web server. Go back to your shopping menu and select an individual product to view. You should see something like Figure 7.12.

click to expand
Figure 7.12: Individual product display template

The next section details how you can hold on to the items users add to their shopping carts, ultimately resulting in numerous orders, wealth, and fame. Well, maybe.

Tracking Your Users' Shopping Carts

To keep track of your users' shopping carts, first create a database table called something like USER_TRACK, with the following field attributes:

ID int. A basic record ID.
USER_ID varchar (32). Used to associate the entry with a user. The value is the session ID.
SEL_ITEM int. The ID of the book added to the cart.
SEL_ITEM_TITLE varchar (150). The title of the book added to the cart.
SEL_ITEM_QTY tinyint. The quantity the user wants to purchase.
SEL_ITEM_PRICE float. The single-item price of the item the user has added to the cart.
DATE_ADDED date. The date the item was added to the cart.

You can use the three-step process from Chapter 3 to create the USER_TRACK table for XYZ Company, or you can manually type a CREATE TABLE command through your MySQL interface of choice. The ID field will be a primary key, automatically incremented by MySQL when a new record is inserted. The actual SQL command for the USER_TRACK table would be.

 CREATE TABLE USER_TRACK  (        ID int not null primary key auto_increment,        USER_ID varchar(32),        SEL_ITEM int,        SEL_ITEM_TITLE varchar(150),        SEL_ITEM_QTY tinyint,        SEL_ITEM_PRICE float,        DATE_ADDED date );

If you look at the description of the table using the MySQL DESCRIBE command, it looks like this:

 +----------------+--------------+-------+--------+----------+----------------+ | Field          | Type         | Null  | Key    | Default  | Extra          | +----------------+--------------+-------+--------+----------+----------------+ | ID             | int(11)      |       | PRI    | NULL     | auto_increment | | USER_ID        | varchar(32)  | YES   | NULL   |          |                | | SEL_ITEM       | int(11)      | YES   | NULL   |          |                | | SEL_ITEM_TITLE | varchar(150) | YES   | NULL   |          |                | | SEL_ITEM_QTY   | tinyint(4)   | YES   | NULL   |          |                | | SEL_ITEM_PRICE | float        | YES   | NULL   |          |                | | DATE_ADDED     | date         | YES   | NULL   |          |                | +----------------+--------------+-------+--------+----------+----------------+

With your table all ready and waiting, you can make the script called shop_addtocart.php, which will insert records into this table. Open your text editor, start a session, and check for required fields:

 if ((!($_POST[sel_item])) || (!($_POST[sel_item_title])) || (!($_POST[sel_item_qty])) || (!($_POST[sel_item_price]))) {        header("Location: http://www.yourcompany.com/show_menu.php");        exit; }

Add the standard database connection code, and then issue the SQL statement for inserting the shopping cart item:

 $sql =  "INSERT INTO USER_TRACK VALUES('', '$_SERVER[PHPSESSID]', '$_POST[sel_item]', '$_POST[$sel_item_title]', '$_POST[sel_item_qty]', '$_POST[sel_item_price]', now())"; @mysql_query($sql) or die(mysql_error());

Note

The MySQL now() function is for adding the current date to the given field.

Finally, display a confirmation to the user:

 <HTML> <HEAD> <TITLE>XYZ Company Shopping: Product Added to Cart</TITLE> </HEAD> <BODY> <h1>XYZ Company Shopping : Product Added to Cart</h1> <p><strong>You have added the following item to your shopping cart:</strong></p> <P><strong>Item:</strong> <? echo "$_POST[sel_item_title]"; ?><br> <strong>Quantity:</strong> <? echo "$_POST[sel_item_qty]"; ?><br> <strong>Single Unit Price:</strong> <? echo "$_POST[sel_item_price]"; ?><br> <strong>Total Price:</strong> <?echo ($_POST[sel_item_price] * $_POST[sel_item_qty]); ?></p> <P><a href="shop_menu.php">Continue Shopping</a></p> </BODY> </HTML>

Put it all together, and you have a script something like this:

 <? session_start(); if ((!($_POST[sel_item])) || (!($_POST[sel_item_title])) || (!($_POST[sel_item_qty])) || (!($_POST[sel_item_price]))) {        header("Location: http://www.yourcompany.com/show_menu.php");        exit; } // create connection; substitute your own information! $conn = mysql_connect("localhost","joeuser","34Nhjp") or die(mysql_error()); // select database; substitute your own database name $db = mysql_select_db("MyDB", $conn) or die(mysql_error()); $sql = "INSERT INTO USER_TRACK VALUES('',   '$_SERVER[PHPSESSID]', '$_POST[sel_item]', '$_POST[$sel_item_title]', '$_POST[sel_item_qty]', '$_POST[sel_item_price]', now())"; @mysql_query($sql) or die(mysql_error()); ?> <HTML> <HEAD> <TITLE>XYZ Company Shopping: Product Added to Cart</TITLE> </HEAD> <BODY> <h1>XYZ Company Shopping : Product Added to Cart</h1> <p><strong>You have added the following item to your shopping cart:</strong></p> <P><strong>Item:</strong> <? echo "$_POST[sel_item_title]"; ?><br> <strong>Quantity:</strong> <? echo "$_POST[sel_item_qty]"; ?><br> <strong>Single Unit Price:</strong> <? echo "$_POST[sel_item_price]"; ?><br> <strong>Total Price:</strong> <?echo ($_POST[sel_item_price] * $_POST[sel_item_qty]); ?></p> <P><a href="shop_menu.php">Continue Shopping</a></p> </BOOY> </HTML>

Go through the shopping menu system again, and use the Add to Shopping Cart button on an individual product page. You should see something like what is shown in Figure 7.13.

click to expand
Figure 7.13: Product added to cart- user confirmation

Users can now continue through your shopping site, merrily adding items. But why should you leave it up to your consumers to remember how many items they've put in their shopping carts, when you have those handy SQL mathematical functions? The next section provides a short piece of code that will make your site look cool by always showing the number of items in the cart. Plus, you'll add the link to the checkout form.

Counting the Cart Items

If you plan to show the current user's shopping cart count on every page, be sure to place this sort of query after any code that adds to the cart.

The SQL statement and query functions are pretty straightforward:

 $item_count = "SELECT SUM(SEL_ITEM_QTY) AS cart_total FROM USER_TRACK WHERE USER_ID = '$_SERVER[PHPSESSID]'"; $item_result = @mysql_query($item_count) or die(mysql_error()); $item_count_total = @mysql_result($item_result,0,"cart_total");

Note the use of the sum() function instead of the count() function. You could use count(), but what if a user has ordered two copies of one book? Instead of showing two items in the cart, count() would return 1, as it's two copies of one item.

It may be tricky to find a space on your page in which to show the total number of items in the cart, but when you do, you could just say this:

 <P>Your cart contains <? if ($item_count_total == "1") {        echo "1 item."; } else {        echo "$item_count_total items."; } ?>

Add a link to the checkout form, called shop_checkout.php

 You can <a href="shop_checkout.php">checkout</a> at any time.</p>

The shop_addtocart.php script from the previous section, with the shopping cart count and display code added, looks something like this:

 <? session_start(); if ((!($_POST[sel_item])) || (!($_POST[sel_item_title])) || (!($_POST[sel_item_qty])) || (!($_POST[sel_item_price]))) {          header("Location: http://www.yourcompany.com/show_menu.php");          exit; } // create connection; substitute your own information! $conn = mysql_connect("localhost","joeuser","34Nhjp") or die(mysql_error()); // select database; substitute your own database name $db = mysql_select_db("MyDB", $conn) or die(mysql_error()); $sql = "INSERT INTO USER_TRACK VALUES('', '$_SERVER[PHPSESSID]', '$_POST[sel_item]', '$_POST[$sel_item_title]', '$_POST[sel_item_qty]', '$_POST[sel_item_price]', now())"; @mysql_query($sql) or die(mysql_error()); $item_count = "SELECT SUM(SEL_ITEM_QTY) AS cart_total FROM USER_TRACK WHERE USER_ID = '$_SERVER[PHPSESSID]'"; $item_result = @mysql_query($item_count) or die(mysql_error()); $item_count_total = @mysql_result($item_result,0,"cart_total"); ?> <HTML> <HEAD> <TITLE>XYZ Company Shopping: Product Added to Cart</TITLE> </HEAD> <BODY> <h1>XYZ Company Shopping : Product Added to Cart</h1> <p><strong>You have added the following item to your shopping cart:</strong></p> <P><strong>Item:</strong> <? echo "$_POST[sel_item_title]"; ?><br> <strong>Quantity:</strong> <? echo "$_POST[sel_item_qty]"; ?><br> <strong>Single Unit Price:</strong> <? echo "$_POST[sel_item_price]"; ?><br> <strong>Total Price:</strong> <?echo ($_POST[sel_item_price] * $_POST[sel_item_qty]); ?></p> <P>Your cart contains <? if ($item_count_total == "1") {          echo "1 item."; } else {          echo "$item_count_total items."; } ?> <br>You can <a href="shop_checkout.php">checkout</a> at any time.</p> <P><a href="shop_menu.php">Continue Shopping</a></p> </BODY> </HTML>

Now, when you add a product to your shopping cart, the page looks something like Figure 7.14.

click to expand
Figure 7.14: Product added to cart- show total cart items

If you can successfully add products to your shopping cart, there's only one more step to go before fame and fortune is yours: checking out!

When It's Checkout Time

In the previous section, you added a checkout link called shop_checkout.php. You're on your own when it comes to creating that file, but rest assured that you already know enough about PHP to make something work. The reason this book does not go into detail and provide code for performing the checkout process from your online store is because there are simply too many variables (and no best way) to make a generic script that works for everyone in all situations. However, your checkout script might very well follow a path like this:

Total the items, then add tax (if applicable) and shipping costs. This gives you the total amount to authorize from the user's credit card.
Perform credit card authorization for the total amount. Note that you are not storing the card number anywhere on your own system!
You will receive either a success or failure response from your card processing routine. If the response is a failure, then print a message to the user and end the transaction. If the response is a success, continue to Step 4.
Write the basic order information to a database table created to hold such information. Include the authorization code you will receive upon the successful authorization, and get the ID value of this record using the mysql_insert_id() function.
For each item in the order, insert a record into a database table designed to hold line items. Each record will reference the ID of the master order, gathered in the previous step.
Delete the shopping cart items for this user.
Display the order information-with authorization code in place of the credit card information-on the screen, so that the user can print it and hold it as a receipt. You can also send this information via e-mail to the user.

Each of the steps listed above-with the exception of the actual payment authorization code-contain the same simple bits of PHP code you've been using throughout this book. No matter which processing method you decide on, you already know the basic code needed to make the method work-you simply have to bring it all together on your own.