Creating a Shopping Cart Mechanism

Creating a Shopping Cart Mechanism
In this hands-on chapter, you will integrate a shopping cart mechanism and checkout procedure into the basic storefront display that you created in, “Creating an Online Storefront.” You will be shown the methods for creating the relevant database tables as well as the scripts for adding and deleting cart items. Once again, the examples used in this chapter represent only a few of an infinite number of possibilities to complete these tasks and are meant as working examples rather than the definitive guide for building an online store.

ارایه  درگاه پرداخت ePayBank.ir برای فروشگاه ها و اسکریپتهای فروشگاهی اینترنتی

اگر میخواهید محصولات شما بصورت فروشگاهی نمایش داده شود در MyCityAd.ir درج آگهی کنید

اگر میخواهید برنامه نویسی سبد خرید اینترنتی را بصورت فارسی بیاموزید بسته آموزش طراحی سایت اختصاصی آریاکدرز را از mortezasaheb.ir خریداری نمایید.

In this chapter, you will learn

How to create relational tables for the shopping cart and checkout portion of an online store

How to create the scripts to add and remove cart items

Some methods for processing transactions, and how to create your checkout sequence

Planning and Creating the Database Tables
Because the goal of this chapter is to provide the user with a way to select and order items, you can imagine what the tables will be first and foremost, you need a table to hold the shopping cart information. In addition to the cart table, you’ll need a table to store orders, along with one to store the items purchased as part of each order.

The following SQL statements were used to create the three new tables, starting with the store_shoppertrack table. This is the table used to hold items as users add them to their shopping cart.

By the Way

The field lengths used to define these tables were chosen arbitrarily to accommodate several possible inputs. Feel free to modify the lengths to meet your specific needs.

mysql> CREATE TABLE store_shoppertrack (
-> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> session_id VARCHAR (32),
-> sel_item_id INT,
-> sel_item_qty SMALLINT,
-> sel_item_size VARCHAR(25),
-> sel_item_color VARCHAR(25),
-> date_added DATETIME
> );
Query OK, 0 rows affected (0.01 sec)

In this table, the only key is the id field for the record. The session_id cannot be unique; otherwise, users could order only one item from your store, which is not a good business practice.

The value stored in the session_id field identifies the user; it matches the value of the PHP session ID assigned to them. The sel_* fields hold the selections by the user: the selected item, the selected quantity of the item, and the selected color and size of the item. Finally, there’s a date_added field. Many times, users place items in their cart and never go through the checkout process. This practice leaves straggling items in your tracking table, which you may want to clear out periodically. For example, you might want to delete all cart items more than a week oldthis is where the date_added field is helpful.

The next table holds the order information:

mysql> CREATE TABLE store_orders (
-> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> order_date DATETIME,
-> order_name VARCHAR (100),
-> order_address VARCHAR (255),
-> order_city VARCHAR (50),
-> order_state CHAR(2),
-> order_zip VARCHAR(10),
-> order_tel VARCHAR(25),
-> order_email VARCHAR(100),
-> item_total FLOAT(6,2),
-> shipping_total FLOAT(6,2),
-> authorization VARCHAR (50),
-> status ENUM(‘processed’, ‘pending’)
-> );
Query OK, 0 rows affected (0.00 sec)

The only key field in the store_orders table is the id. For the sake of brevity in this lesson, an assumption is made that the billing and shipping addresses of the user are the same and that this store sells only to United States addresses. It’s simple enough for you to add another block of fields for shipping address information, if you want to do so.

Also, this table assumes that you are not storing credit card information, which you shouldn’t do unless you have superencrypted the information and are sure that your firewalled server is secure. Instead, this table is based on the idea of real-time, credit card processing. You’ll learn a few transaction options at the end of this lesson.

The final table holds the line items in each order, store_orders_items:

mysql> CREATE TABLE store_orders_items (
-> id INT NOT NULL PRiMARY KEY AUTO_INCREMENT,
-> order_id INT,
-> sel_item_id INT,
-> sel_item_qty SMALLINT,
-> sel_item_size VARCHAR(25),
-> sel_item_color VARCHAR(25),
-> sel_item_price FLOAT(6,2)
-> );
Query OK, 0 rows affected (0.00 sec)

The sel_* fields should look familiar with the exception of sel_item_price, they are the same fields that appear in the store_shoppertrack table! The primary key is the id field, and the order_id field is used to tie each line item to the appropriate record in store_orders.

The sel_item_price field is included here, as opposed to simply relating to the item record, because you might have occasion to change the pricing in your item record. If you change the price in the item record, and you relate the sold line items to the current catalog price, your line item prices won’t reflect what the user actually paid.

With your tables all squared away, we can move on to adding an item to the user’s shopping cart.

Integrating the Cart with Your Storefront
In this section, you’ll make modifications to the showitem.php script from Chapter 22. The goal is to transform the item information page into an item information page with a form for selecting colors, sizes, and quantities.

In the original script, insert the following before line 2:

session_start();

Because the shopping cart elements are attached to the user through a session ID, the session must be started. The next changes don’t occur until what was line 39 of the original script, so that’s where we start in Listing 23.1.

Listing 23.1. New Lines in showitem.php
39: <p><strong>Price:</strong> \$”.$item_price.”</p>
40: <form method=\”post\” action=\”addtocart.php\”>”;
41:
42: //free result
43: mysqli_free_result($get_item_res);
44:
45: //get colors
46: $get_colors_sql = “SELECT item_color FROM store_item_color WHERE
47: item_id = ‘”.$_GET[“item_id”].”‘ ORDER BY item_color”;
48: $get_colors_res = mysqli_query($mysqli, $get_colors_sql)
49: or die(mysqli_error($mysqli));
50:
51: if (mysqli_num_rows($get_colors_res) > 0) {
52: $display_block .= “<p><strong>Available Colors:</strong><br/>
53: <select name=\”sel_item_color\”>”;
54:
55: while ($colors = mysqli_fetch_array($get_colors_res)) {
56: $item_color = $colors[‘item_color’];
57: $display_block .= “<option value=\””.$item_color.”\”>”.
58: $item_color.”</option>”;
59: }
60: $display_block .= “</select>”;
61: }
62:
63: //free result
64: mysqli_free_result($get_colors_res);
65:
66: //get sizes
67: $get_sizes_sql = “SELECT item_size FROM store_item_size WHERE
68: item_id = “.$_GET[“item_id”].” ORDER BY item_size”;
69: $get_sizes_res = mysqli_query($mysqli, $get_sizes_sql)
70: or die(mysqli_error($mysqli));
71:
72: if (mysqli_num_rows($get_sizes_res) > 0) {
73: $display_block .= “<p><strong>Available Sizes:</strong><br/>
74: <select name=\”sel_item_size\”>”;
75:
76: while ($sizes = mysqli_fetch_array($get_sizes_res)) {
77: $item_size = $sizes[‘item_size’];
78: $display_block .= “<option value=\””.$item_size.”\”>”.
79: $item_size.”</option>”;
80: }
81: }
82:
83: $display_block .= “</select>”;
84:
85: //free result
86: mysqli_free_result($get_sizes_res);
87:
88: $display_block .= ”
89: <p><strong>Select Quantity:</strong>
90: <select name=\”sel_item_qty\”>”;
91:
92: for($i=1; $i<11; $i++) {
93: $display_block .= “<option value=\””.$i.”\”>”.$i.”</option>”;
94: }
95:
96: $display_block .= ”
97: </select>
98: <input type=\”hidden\” name=\”sel_item_id\”
99: value=\””.$_GET[“item_id”].”\”/>
100: <p><input type=\”submit\” name=\”submit\” value=\”Add to Cart\”/></p>
101: </form>
102: </td>
103: </tr>
104: </table>”;
105: }
106: //close connection to MySQL
107: mysqli_close($mysqli);
108: ?>
109: <html>
110: <head>
111: <title>My Store</title>
112: </head>
113: <body>
114: <?php echo $display_block; ?>
115: </body>
116: </html>

The first change is at the new line 40, where the $display_block string is continued to include the beginning <form> element. The action of the form is a script called addtocart.php, which you will create in the next section.

The next change occurs at line 53, where the $display_block string is continued to include the opening tag of a <select> element, named sel_item_color. In lines 5758, the colors are put into <option> elements for the user to choose from, instead of simply printing on the screen. Line 60 closes the <select> element.

The same types of changes are made for item sizes. Lines 7374 reflect the continuation of the $display_block string to include the <select> element, named sel_item_size. Lines 7879 write the colors in <option> elements, and line 83 closes the <select> element.

Lines 8894 are additions to the script. These lines create a <select> element, called sel_item_qty, for the user to pick how many items to purchase. Line 97 closes this <select> element, and line 98 adds a hidden field for the item_id. Line 100 adds the submit button, and line 101 closes the form. We close the connection to MySQL in line 206, and the remaining lines are unchanged from the original script.

When viewing the baseball hat item using the new version of showitem.php, you would see Figure 23-1, reflecting the addition of the form elements.

Figure 23.1. The new baseball hat item page.

The next step is to create the addtocart.php script.

Adding Items to Your Cart
The addtocart.php script simply writes information to the store_shoppertrack table and redirects the user to the view of the shopping cart. We’ll create the addtocart.php script first in Listing 23.2 and then tackle the showcart.php script next.

Listing 23.2. The addtocart.php Script
1: <?php
2: session_start();
3:
4: //connect to database
5: $mysqli = mysqli_connect(“localhost”, “joeuser”, “somepass”, “testDB”);
6:
7: if (isset($_POST[“sel_item_id”])) {
8: //validate item and get title and price
9: $get_iteminfo_sql = “SELECT item_title FROM store_items WHERE
10: id = ‘”.$_POST[“sel_item_id”].”‘”;
11: $get_iteminfo_res = mysqli_query($mysqli, $get_iteminfo_sql)
12: or die(mysqli_error($mysqli));
13:
14: if (mysqli_num_rows($get_iteminfo_res) < 1) {
15: //invalid id, send away
16: header(“Location: seestore.php”);
17: exit;
18: } else {
19: //get info
20: while ($item_info = mysqli_fetch_array($get_iteminfo_res)) {
21: $item_title = stripslashes($item_info[‘item_title’]);
22: }
23:
24: //add info to cart table
25: $addtocart_sql = “INSERT INTO store_shoppertrack
26: (session_id, sel_item_id, sel_item_qty,
27: sel_item_size, sel_item_color, date_added)
28: VALUES (‘”.$_COOKIE[“PHPSESSID”].”‘,
29: ‘”.$_POST[“sel_item_id”].”‘,
30: ‘”.$_POST[“sel_item_qty”].”‘
31: ‘”.$_POST[“sel_item_size”].”‘,
32: ‘”.$_POST[“sel_item_color”].”‘, now())”;
33: $addtocart_res = mysqli_query($mysqli, $addtocart_sql)
34: or die(mysqli_error($mysqli));
35:
36: //redirect to showcart page
37: header(“Location: showcart.php”);
38: exit;
39: }
40:
41: } else {
42: //send them somewhere else
43: header(“Location: seestore.php”);
44: exit;
45: }
46: ?>

Line 2 continues the user session, which is important because you need to capture the user’s session ID to write to the store_shoppertrack table. Line 5 makes the database connection, and line 7 begins the validation of the actions.

In line 7, the script verifies that a value is present in $_POST[“sel_item_id”], meaning that the user came to this script after submitting the proper form. If there is no value, the script jumps down to line 41 and sends the user away in line 43, and that’s it for the script.

However, if there is a value in $_POST[“sel_item_id”], the next action is to verify that it is a valid value. Lines 912 create and issue a SQL query to gather the title of the selected item. Line 14 checks for a result; if there is no result, the user is again redirected away in line 16 because the item selection was not valid.

If the item selection is valid, the script continues on to line 20 and extracts this value from the resultset. The script now has enough information to add the item selection to the store_shoppertrack table, which it does in lines 2534.

After the query has been issued, the user is redirected to showcart.php, which contains all cart items. You’ll create this in the next section.

Viewing the Cart
Now that you can add items to a cart, you’ll want to see them! Listing 23.3 shows the code for showcart.php.

Listing 23.3. The showcart.php Script
1: <?php
2: session_start();
3:
4: //connect to database
5: $mysqli = mysqli_connect(“localhost”, “joeuser”, “somepass”, “testDB”);
6:
7: $display_block = “<h1>Your Shopping Cart</h1>”;
8:
9: //check for cart items based on user session id
10: $get_cart_sql = “SELECT st.id, si.item_title, si.item_price,
11: st.sel_item_qty, st.sel_item_size, st.sel_item_color FROM
12: store_shoppertrack AS st LEFT JOIN store_items AS si ON
13: si.id = st.sel_item_id WHERE session_id =
14: ‘”.$_COOKIE[“PHPSESSID”].”‘”;
15: $get_cart_res = mysqli_query($mysqli, $get_cart_sql)
16: or die(mysqli_error($mysqli));
17:
18: if (mysqli_num_rows($get_cart_res) < 1) {
19: //print message
20: $display_block .= “<p>You have no items in your cart.
21: Please <a href=\”seestore.php\”>continue to shop</a>!</p>”;
22: } else {
23: //get info and build cart display
24: $display_block .= ”
25: <table celpadding=\”3\” cellspacing=\”2\” border=\”1\” width=\”98%\”>
26: <tr>
27: <th>Title</th>
28: <th>Size</th>
29: <th>Color</th>
30: <th>Price</th>
31: <th>Qty</th>
32: <th>Total Price</th>
33: <th>Action</th>
34: </tr>”;
35:
36: while ($cart_info = mysqli_fetch_array($get_cart_res)) {
37: $id = $cart_info[‘id’];
38: $item_title = stripslashes($cart_info[‘item_title’]);
39: $item_price = $cart_info[‘item_price’];
40: $item_qty = $cart_info[‘sel_item_qty’];
41: $item_color = $cart_info[‘sel_item_color’];
42: $item_size = $cart_info[‘sel_item_size’];
43: $total_price = sprintf(“%.02f”, $item_price * $item_qty);
44:
45: $display_block .= ”
46: <tr>
47: <td align=\”center\”>$item_title <br></td>
48: <td align=\”center\”>$item_size <br></td>
49: <td align=\”center\”>$item_color <br></td>
50: <td align=\”center\”>\$ $item_price <br></td>
51: <td align=\”center\”>$item_qty <br></td>
52: <td align=\”center\”>\$ $total_price</td>
53: <td align=\”center\”><a href=\”removefromcart.php?id=”.
54: $id.”\”>remove</a></td>
55: </tr>”;
56: }
57:
58: $display_block .= “</table>”;
59: }
60: ?>
61: <html>
62: <head>
63: <title>My Store</title>
64: </head>
65: <body>
66: <?php echo $display_block; ?>
67: </body>
68: </html>

Line 2 continues the user session, which is important because you need to match the user’s session ID with the records in the store_shoppertrack table. Line 5 makes the database connection, and line 7 begins the $display_block string, with a heading for the page.

Lines 1014 represent a joined query, in which the user’s saved items are retrieved. The id, sel_item_qty,sel_item_size, and sel_item_color fields are extracted from store_shoppertrack, and the item_title and item_price fields are retrieved from the store_items table, based on the matching information from store_shoppertrack. In other words, instead of printing 2 for the selected item, Baseball Hat is shown as the title. Lines 1516 issue the query, and line 18 checks for results.

If there are no results, the user has no items in the store_shoppertrack table. A message is written to the $display_block string, and the script exits and shows the message.

if there are indeed results, the beginning of an HTML table is created in lines 2434, with columns defined for all the information in the cart (and then some). Line 36 begins the while loop to extract each item from the store_shoppertrack, and this loop continues until line 56, printing the information in the proper table cell.

In lines 5354, you see a link created for an item removal script, which you will create in the next section. Line 58 closes the table, and the script finishes up and prints HTML to the screen in lines 6168.

Now, go back to an item page and add the item to your cart. After the items are written to the store_shoppertrack table, you should be redirected to the showcart.php page, and your newly selected items should be displayed. Figure 23.2 shows my cart after adding some items.

Figure 23.2. Items added to cart.

The next step is to create the removefromcart.php script.

Removing Items from Your Cart
The removefromcart.php script is short because all it does is issue a query and redirect the user to another script. Inevitably, a user will want to weed items out of his cart, and this script enables him to do just that. Listing 23.4 shows the complete script.

Listing 23.4. The removefromcart.php Script
1: <?php
2: session_start();
3:
4: //connect to database
5: $mysqli = mysqli_connect(“localhost”, “joeuser”, “somepass”, “testDB”);
6:
7: if (isset($_GET[“id”])) {
8: $delete_item_sql = “DELETE FROM store_shoppertrack WHERE
9: id = ‘”.$_GET[“id”].”‘and session_id =
10: ‘”.$_COOKIE[“PHPSESSID”].”‘”;
11: $delete_item_res = mysqli_query($mysqli, $delete_item_sql)
12: or die(mysqli_error($mysqli));
13:
14: //redirect to showcart page
15: header(“Location: showcart.php”);
16: exit;
17: } else {
18: //send them somewhere else
19: header(“Location: seestore.php”);
21: exit;
22: }
23: ?>

Line 2 continues the user session because you need to match the user’s session ID with the records in the store_shoppertrack table. Line 5 makes the database connection, and line 7 checks for a value in $_GET [“id”]. If a value does not exist in $_GET [“id”], the user is not clicking the link from her cart and, thus, is sent away in line 19.

If a value exists in $_GET [“id”], a SQL query (lines 810) is issued (lines 1112), and the user is redirected to the showcart.php script (line 15), where the item should no longer show up. Try it and see!

Payment Methods and the Checkout Sequence
Several commerce methods exist when it comes time to pay for the purchases in the shopping cart. The “right” method for you depends on your businessmerchant accounts through banking institutions often require you to have a business license, a reseller’s permit, and other pieces of paper proving that you’re a legitimate business. If you’re simply a person who has a few items to sell, you might not want to go through all that paperwork. However, you still have options!

Regardless of the payment method you choose, one thing is certain: If you are passing credit card information over the Web, you must do so over an SSL connection. Obtaining an SSL certificate and installing it on your system is covered in Chapter, “Setting Up a Secure Web Server.” You do not have to use this secure connection during the user’s entire shopping experience, just from the point at which sensitive information is captured, such as the checkout form.

Creating the Checkout Form
At this point in the book, you should be well versed in creating a simple form. At the beginning of this chapter, the store_orders table was created with fields to be used as a guideline for your form:

order_name

order_address

order_city

order_state

order_zip

order_tel

order_email

Additionally, your form will need fields for the credit card number, expiration date, and the name on the credit card. Another nice feature is to repeat the user’s shopping cart contents with an item subtotal so that the customer remembers what he’s paying for and approximately how much the order will cost. Also at this point of the checkout sequence, you offer any shipping options you might have. Shipping and sales tax would be calculated in the next step of the process.

From the point of clicking the submit button on the form, the checkout sequence depends on the payment method you are using. The next section goes through the basic steps and offers suggestions on various methods of payment processing.

Performing the Checkout Actions
If you have obtained a merchant account through your bank, you can utilize real-time payment services such as VeriSign’s PayFlo Pro. PHP no longer contains a built-in set of functions that enable direct access to the PayFlo libraries from VeriSign, but you can still create a simple script to handle the credit card transaction. You can learn more about PayFlo Pro at the VeriSign website: http://www.verisign.com/products-services/payment-processing/online-payment/payflow-pro/index.html. The comments area of the PHP Manual section for the old PayFlo functions contains several options for using PayFlo and PHP:http://www.php.net/pfpro.

VeriSign’s product is one of several transaction-processing gateways that exist for use by merchants. Your bank will usually provide a list of merchants it prefers you to use. If you stray from your bank’s list of preferred vendors, be sure to research your selected vendor thoroughly to avoid any delays with deposits and to ensure you’re getting the best deal.

After you have selected a transaction processor, your checkout script should follow a path such as the following:

Total the items, add tax, and add shipping. This gives you the total amount to authorize from the credit card.

Perform credit card authorization for the total amount.

You will receive either a success or failure response from your card processing routine. If the response is a failure, print a message to the user, and the transaction is over. If the response is a success, continue to step 4.

Write the basic order information to a table such as store_orders, including the authorization code you will receive on successful authorization. Get the id value of this record using mysql_insert_id().

For each item in the shopping cart that is tied to this user, insert a record into store_orders_itemmap. Each record will reference the id (as order_id) gathered in the previous step.

Delete the shopping cart items for this user.

Display the order 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 email to the user.

Each of the steps listed previously with the exception of the actual payment authorization code are the same simple steps you have been using throughout this book, and there’s no reason to make them more difficult than they need to be!