12. Databases and Shopping Carts

What We Will Cover Announcements Please silence all cell phones and pagers When class is over, please shut down your computer if it is on Elucidations 12.1: Encapsulating the Database 12.1.1: Overview 12.1.2: Connecting to a Database 12.1.3: Handling Errors 12.1.4: Making Queries 12.1.5: Show the Query Result Exercise 12.1 12.1.6: Summary 12.2: Displaying Products 12.2.1: Overview 12.2.2: Displaying the Products 12.2.3: Linking to the Shopping Cart Exercise 12.2 12.2.4: Summary 12.3: Coding a Shopping Cart 12.3.1: Overview 12.3.2: Persisting Across Multiple Sessions 12.3.3: Coding the main() Function 12.3.4: Adding an Item 12.3.5: Deleting an Item 12.3.6: Updating an Item 12.3.7: Showing the Cart Exercise 12.3 12.3.8: Summary Wrap Up Due Next: Final Project Report and Presentation (6/1/09) Elucidations Homework Questions? A11-Authenticating Users (5/11/09) Quiz 11 and Discussion Chapter 12 (5/11/09) Quiz Questions? Blackboard Questions from last class? ^ top 12.1: Encapsulating the Database Learner Outcomes At the end of the lesson the student will be able to: Apply object-oriented programming to simplify database access Describe the database error-handling functions Use debugging techniques to find database errors ^ top 12.1.1: Overview We can improve our use of databases by developing a class The connection sequence and queries can be simplified In addition, we can add error handling with adjustable settings While developing, you want to see detailed error information In production, you only want the user to see "friendly" error messages Also, we can add debugging functions that can be turned on or off Plus, we need to use dbconvars.php for portability We want all of this encapsulated into a class for easier programming Existing Database Classes Many similar database classes exist Often called "database abstraction layers" or "database wrappers" Many of them boast database-portability features Applications written for one DBMS can work with another DBMS With only "minimal" changes You may find them complicated to use at first Do NOT use these database wrappers for this course! Commonly Used Database Wrappers Library Provider ADOdb ADOdb Database Abstraction Library for PHP (and Python) DB PEAR - PHP Extension and Application Repository MDB2 PEAR - PHP Extension and Application Repository ^ top 12.1.2: Connecting to a Database We start our design by creating a class named DB class DB { // add code here } The first thing we need to consider is how to connect to a database We want our class to handle the connections so we do not have to worry about them To handle multiple queries, we keep a variable for our database connection Thus we save the database resource using a class variable: private $cnx = 0; // Result of mysql_connect() The next thing we need to develop is the connection-handling function Connection-Handling Function Note the calls to the _handleError() function We discuss this function later in the section on handling errors Making the Connection To make the connection process easy, we call connect() in the constructor function __construct($errorLevel = false) { if ($errorLevel) $this->errLevel = $errorLevel; $this->connect(); } Thus we connect whenever we create a DB object $db = new DB(); We discuss the $errorLevel setting later in the section on handling errors ^ top 12.1.3: Making Queries We can connect to our database and handle errors Now it is time to make queries For our class, we will return the results of a MySQL query We can then process the query in the usual ways Note that we save the last query result in case we want to use it again private $result = 0; // Result of most recent mysql_query() Also, we want our query() function to handle errors automatically Query Function Coding a Query When coding a query, I recommend breaking the process into two steps Assign the SQL statement to a variable Use the variable in the function call This allows you to use to view the SQL in a debug statement For example: $sql = "SELECT * FROM products"; echo "<p>sql=$sql</p>\n"; $result = $db->query($sql); After you are sure the SQL is correct, remove the echo statement ^ top 12.1.4: Show the Query Results As an aid to debugging, it is often convenient to view query results We discussed how to do this in lesson 8.4: Arrays, Tables and Meta-data We put this code into a function of our class: showQuery() By default, we use of the saved result The showQuery() Function Using showQuery() After a SELECT query, we can show a table of results For example: $sql = "SELECT * FROM products"; $result = $db->query($sql); $db->showQuery(); ^ top 12.1.5: Handling Errors Errors from a MySQL database are not always reported by PHP Instead, you need to explicitly check for errors Commonly Used Error-Handling Functions for MySQL Function Description mysql_errno() Returns the error number from the last MySQL function, or 0 if no error has occurred. mysql_error() Returns an error message from the last MySQL function, or the empty string if no errors occurred. Automating Error-Handling We want to develop an automatic error-handling function for our DB class For our system, we need two parts: An error-level setting An error-handling function We use a class variable to store the error-level setting: private $errLevel = "halt"; //"ignore", "halt", "warn" There are three possible settings in our system: ignore: do not report any errors nor take any action warn: display an error message and continue halt: display an error message and stop processing the page While developing code, you use either the "halt" or "warn" levels When in production, you change the setting to "ignore" Error-Handling Function function _handleError($msg) { if ($this->errLevel == "ignore") return; echo "</td></tr></table></div> <strong>Database error:</strong> $msg<br>\n <strong>DB Error</strong>: ".mysql_errno() ." (".mysql_error().")<br>\n"; if ($this->errLevel == "halt") { die ("Session halted."); } } Setting the Error Level We can set the error level when we create a DB object: $db = new DB("warn"); Another option is to change the default level in the code: private $errLevel = "warn"; //"ignore", "halt", "warn" ^ top Exercise 12.1 In this exercise we explore using the DB class. Specifications Open the XAMPP Control Panel by double-clicking the icon on the desktop or by using the Apache Friends entry in the start menu. Start the Apache and MySQL modules, if they are not already running. Verify your computer has the following connection information in a file named dbconvars.php in a subdirectory named includes of the phptest directory: <?php $dbhost = "localhost"; $dbuser = "root"; $dbpwd = ""; $dbname = "artzy"; ?> If needed, change the values assigned to $dbuser and $dbpwd to match your MySQL installation. In the classroom, $dbuser = root; and $dbpwd = password;. For more information see lesson 6.3.1: Connecting to a MySQL Database. Add the following files to the includes subdirectory of phptest: db.php Copy the file and paste it into a text editor such as TextPad. After saving, verify the page saved without turning angle brackets and other special characters into HTML entities such as &lt; and &gt;. Copy the following PHP script into a text editor, save the file in your htdocs/phptest directory as dbtest.php, and then view the file as a Web page in your browser using the address: http://localhost/phptest/dbtest.php <html> <head><title>PHP Script</title></head> <body> <?php // Enter your PHP code here ?> </body> </html> The page should be blank to start with. First let us add the required includes file to the dbtest.php file: require("includes/db.php"); The db.php file contains our DB class. Next, let us construct an object of the DB class after the included file: $db = new DB(); When we construct the object, we automatically connect to the database. For more information see section 12.1.2: Connecting to a Database. Now we want to make a query on the database and save the result set. Add the following code after constructing the DB object: $sql = "SELECT * FROM products"; echo "<p>sql=$sql</p>\n"; // for debugging $result = $db->query($sql); Reload (refresh) your browser and verify you see the SQL statement displayed. For more information see section 12.1.3: Making Queries. As an aid to debugging, the DB class has the showQuery() function that prints the result set in a table. Add the following code after the query: $db->showQuery(); // for debugging Reload (refresh) your browser and verify you see the table of data. For more information see section 12.1.4: Show the Query Results. Now let us explore the error handling functions of the DB class. First make an invalid query on the database using the following code: // Create and process an error $sql = "SELECT * FROM somebogustable"; $result = $db->query($sql); Reload (refresh) your browser and verify you see and error message like: Database error: Invalid SQL: SELECT * FROM somebogustable DB Error: 1146 (Table 'edparr2_courses.somebogustable' doesn't exist) Session halted. For more information see section 12.1.5: Handling Errors. Database error information is helpful when debugging, but should not be displayed while your application is "live" on the Internet. To turn the error messages off for "production", change the line that constructs the DB object to: $db = new DB("ignore"); Reload (refresh) your browser and verify the error message disappears. There is no need to turn in the dbtest.php file. Listing of dbtest.php As time permits, be prepared to answer the Check Yourself questions in the section: 12.1.6: Summary. ^ top 12.1.6: Summary We can simplify our database usage by developing a class With the class, we can simplify our connection process to: $db = new DB(); Our queries become: $sql = "SELECT * FROM products"; $result = $db->query($sql); Errors are handled automatically using one of three settings: ignore: do not report any errors nor take any action warn: display an error message and continue halt: display an error message and stop processing the page We can control the error handling when we create a DB object $db = new DB("warn"); As a convenience, we can view the results of the last SELECT query $sql = "SELECT * FROM products"; $result = $db->query($sql); $db->showQuery(); Check Yourself What is the purpose of a database wrapper class? (12.1.1) Using the DB class, what code do you write to connect to your database ? (12.1.2) From where does the DB class get the database hostname, username, password and database name? (12.1.2) Using the DB class, what code do you write to query your database? (12.1.3) What is the purpose of the showQuery() function in the DB class? (12.1.4) What is the purpose of the error levels in the DB class? (12.1.5) Which PHP functions report MySQL error information? (12.1.5) ^ top 12.2: Displaying Products Learner Outcomes At the end of the lesson the student will be able to: Display a list of products from a database ^ top 12.2.1: Overview In the next few sections we discuss how to build a shopping cart We will use PHP and MySQL to develop a persistent shopping cart Our solution will have two pages: cart.php: manages the shopping cart products.php: displays products to add to the cart Products for Sale Displaying products is the first step in creating a shopping cart Customers need a way to find, select and choose products to purchase A simple list is an appealing and easy approach Viewing the Products List You can view the example products list at the Artzy web site Link to page: products.php Note that you need to install the Artzy database to run the scripts Link to sql file: artzy.txt ^ top 12.2.2: Displaying the Products We organized our page for viewing products using functions Function main() is simple: function main($title = "") { include("includes/header.php"); showContent($title); include("includes/footer.php"); } To display the products, we create a DB object and make a query $db = new DB(); $sql = " SELECT ID, ProductName, ProductDescription, Path2Image, Price FROM products "; $result = $db->query($sql); The code for displaying products is contained in function showContent() The showContent() function in turn calls the following as needed showHeading(): displays the table heading showItem(): displays a table row showCartLink(): displays a link to the shopping cart Each of these called functions output HTML code You can view all the code by following the link: products.php The showContent() function is shown below Note the use of PHP's list() function: list($id, $prodName, $description, $imagePath, $price) = $row; The purpose of the list() function is to assign values in an array to individual variables The individual variables are more readable than array indexes We then pass the variables to the showItem() function: showItem($id, $prodName, $description, $imagePath, $price); The showItem() function then uses the parameters in the HTML code for displaying a product The showContent() Function function showContent($title) { $db = new DB(); $sql = " SELECT ID, ProductName, ProductDescription, Path2Image, Price FROM products "; $result = $db->query($sql); echo "<h1>$title</h1>\n"; echo "<table width=\"100%\">\n"; showCartLink(); showHeading(); while ($row = mysql_fetch_row($result)) { list($id, $prodName, $description, $imagePath, $price) = $row; $price = "$".number_format($price, 2); showItem($id, $prodName, $description, $imagePath, $price); } showCartLink(); echo "</table>\n"; } ^ top 12.2.3: Linking to the Shopping Cart To select an item, a customer clicks the Add Item link The link contains code like the following: cart.php?add=1&qty=1 The link passes two parameters to the cart.php page: add=1: add the product numbered "1" to the cart qty=1: add a quantity of 1 to the cart The cart.php script reads these variables and processes them Example Product Item  Image   Product  Price   Description  Buy Canvas $22.50 Good canvas for quality paint Add Item ^ top Exercise 12.2 In this exercise we display a simple product catalog. Specifications Verify your web server is started and all the support files are installed as specified in the first 4 steps of exercise 12.1. Also, verify that the Artzy database is installed as described in lesson 2.1.5: Importing and Exporting Data. In addition, verify the following files are installed in a subdirectory named includes of the htdocs/phptest directory: db.php dbconvars.php footer.php header.php If needed, copy the files and paste them into a text editor such as TextPad. After saving, verify you saved the page without turning angle brackets and other special characters into HTML entities such as &lt; and &gt;. Also if needed, change the dbconvars.php values assigned to $dbuser and $dbpwd to match your MySQL installation. In the classroom, $dbuser = root; and $dbpwd = password;. For more information see lesson 6.3.1: Connecting to a MySQL Database. To make the products.php page more attractive, add the following files to a subdirectory named images of the htdocs/phptest directory: brush.gif brush2.gif brushlarge.gif canvas2.gif oil-tube.gif tube2.gif Add the following file to the phptest directory: products.php: a simple product catalog Copy the file and paste it into a text editor such as TextPad. After saving, verify the editor saved the page without turning angle brackets and other special characters into HTML entities like &lt; and &gt;. View the products.php file as a web page in your browser using the address: http://localhost/phptest/products.php. Note that now of the "Add Item" links work yet. We will add this functionality in the next part of the lesson. There is no need to turn in any of these files. As time permits, be prepared to answer the Check Yourself questions in the section: 12.2.4: Summary. ^ top 12.2.4: Summary The first step for a customer is to select products for display We provide the display on the products.php page The page lists each product with a link labeled Add Item When the customer selects the link, they are taken to the cart.php page The cart.php page then adds the product to the cart Check Yourself As time permits, be prepared to answer these questions. You can find more information by following the links after the question. What is the purpose of a product listing? (12.2.1) What is the purpose of PHP's list() function? (12.2.2) How does the "Add Item" link send the product ID and quantity information to the cart.php page? (12.2.3) ^ top 12.3: Coding a Shopping Cart Learner Outcomes At the end of the lesson the student will be able to: Describe the purpose of a shopping cart Add a shopping cart to their project, if desired Make use of simple JavaScript ^ top 12.3.1: Overview A shopping cart must allow users to perform four functions: Add an item to their cart Delete an item from their cart Change the quantity of items for each product in their cart See the products that exist in their cart In addition, the cart should show the users a cumulative total cost of all products in their cart. You can view the shopping cart at the Artzy web site Link to page: cart.php What About Credit Card Payments? To process credit cards, you must make arrangements with a credit card service provider There are many service providers and you need to establish an account with one of them They each have their own interface for processing credit card transactions Thus you will need to research the requirements for whichever service you choose Often these interfaces are a URL to which you send all the required information For example, here is the information required by PayPal: Adding PayPal Checkout to Your 3rd-party Shopping Cart Page Organization We organized our page for the shopping cart using functions, some of which are: main(): controls the operation of the page getCartId(): gets an encrypted ID for the cart getQuantity(): gets the quantity of an item addItem(): adds an item to the cart deleteItem(): deletes an item from the cart updateItem(): updates the quantity of an item in the cart showContent(): displays the cart and calls the following as needed showHeading(): displays the table heading showItem(): displays a table row showFooter(): displays the end of the table ^ top 12.3.2: Persisting Across Multiple Sessions Normally we identify and track users using sessions However, what if the user closes their Web browser and returns to the same web site the next day? We could use a login system to identify users However, we want to keep our shopping as simple as possible We can keep things simple for the user by using cookies Tracking Cart ID's We use session id's for the shopping cart identifier Session id's are a good choice because they are secure values No user can guess the cart id of another user Normally, a session id expires when the user closes a browser To get around this limitation we save the session id in our own cookie If a session id is set in our cookie, we use it instead If a session id has not been set, we set the cookie with the current session id We put this functionality into a function named getCartId() The getCartId() Function define('CART_TIME', 2592000); // 30 days ... function getCartId() { if (isset($_COOKIE["cartId"])) { return $_COOKIE["cartId"]; } else { // no cookie is set if (!session_id()) { session_start(); } setcookie("cartId", session_id(), time() + CART_TIME); return session_id(); } } How Safe is it to Store the Session ID in a Persistent Cookie? Session IDs are usually stored in cookies However, the session expires after a short amount of time, usually about 20 minutes Is it safe to store a session ID in a persistent cookie for 30 days? The answer depend on the information available in the session If the user can access sensitive data based on a session ID, then you should not persist the session ID Shopping cart data is usually NOT considered sensitive data Thus, keeping a shopping cart ID is usually not a significant security risk However, you should minimize the help you provide a potential attacker Thus instead of labeling your cookie "cartId", you should use a non-obvious key value In addition, it is critical that a user authenticate before having access to any sensitive data ^ top 12.3.3: Coding the main() Function The main() function decides if an action is needed and calls the appropriate function The decision is made based on the presence of the request variables: add: add a product del: delete a product update: update a product quantity The product to operate upon is passed as the value of the variable For example: add=1: adds the product numbered "1" to the cart Note that we create a DB object and pass it to each function as needed Also note that we have JavaScript that is displayed by header.php You need Javascript to automatically respond to changes in forms Function main() always calls function showContent() The main() Function function main($title = "") { $db = new DB(); if (isset($_REQUEST['add'])) { $pid = $_REQUEST['add']; addItem($db, $pid); } else if (isset($_REQUEST['del'])) { $pid = $_REQUEST['del']; deleteItem($db, $pid); } else if (isset($_REQUEST['update'])) { $pid = $_REQUEST['update']; updateItem($db, $pid); } $other = getJavaScript(); include("includes/header.php"); showContent($title, $db); include("includes/footer.php"); } Including JavaScript function getJavaScript() { return<<<SCRIPT <script type="text/javascript"> function updateQty(item) { itemId = item.name; newQty = item.options[item.selectedIndex].text; location.href = 'cart.php?update='+itemId+'&qty='+newQty; } </script> SCRIPT; } About the JavaScript The JavaScript function is called by the onChange event handler of the selection list: onChange="updateQty(this)" Whenever the selection list value changes, the onChange event handler fires The selection list selected identifies itself by using the this keyword The updateQty() function gets the product ID from the name of the selection list In addition, the function retrieves the selected quantity With these two values, the JavaScript calls the update request with the product and new quantity: 'cart.php?update='+itemId+'&qty='+newQty Using the location.href command automatically loads a page into the browser For more information, I suggest you take the course: CIS-132: Introduction to Internet Programming ^ top 12.3.4: Adding an Item When a user clicks on the Add Item link in the product.php page, the addItem() function is called Function addItem() needs: A cart id: obtained from the getCartId() function A product id: passed to the function as parameter $pid from main() The quantity of items: obtained from the getQuantity() function With this information, it creates a SQL query and checks if the item already exists in the users cart If the item already exists, it increments the quantity and calls the updateItem() function Otherwise, it looks up the price and inserts the item in the cart The getQuantity() Function // Get and sanitize the quantity function getQuantity() { $qty = 1; if (isset($_REQUEST['qty'])) $qty = $_REQUEST['qty']; $qty = intval($qty); if ($qty < 0) $qty = 0; if ($qty > MAX_QTY) $qty = MAX_QTY; return $qty; } The addItem() Function function addItem($db, $pid) { $cartID = getCartId(); $qty = getQuantity(); // Check if item already exists in the users cart $sql = " SELECT * FROM shoppingcarts WHERE CartID='$cartID' AND ProductID=$pid "; $result = $db->query($sql); $numRows = mysql_num_rows($result); if($numRows != 0) { // Item already exists $qty = mysql_result($result, 0, 'Quantity'); $_REQUEST['qty'] = $qty + 1; // increment qty updateItem($db, $pid); } else { // Item does not exist // Get the current price $sql = " SELECT Price FROM products WHERE ID=$pid "; $result = $db->query($sql); if (mysql_num_rows($result) == 0) return; $price = mysql_result($result, 0, 0); // Insert the item $sql = " INSERT INTO shoppingcarts VALUES('$cartID', $pid, NOW(), $price, $qty) "; $db->query($sql); } } ^ top 12.3.5: Deleting an Item When a user clicks on the Remove link in their cart, deleteItem() is called The link contains an anchor reference using URL rewriting like the following: cart.php?del=1 This calls the cart.php page and identifies the product to delete Function deleteItem() needs: A cart id: obtained from the getCartId() function A product id: passed to the function as parameter $pid from main() With this information, the function creates a SQL command and deletes the item Example Shopping Cart Item  Product  Quantity   Price   Action  Canvas 1 2 3 4 5 6 7 8 9 10 $22.50 Remove? The deleteItem() Function function deleteItem($db, $pid) { $cartID = getCartId(); $sql = " DELETE FROM shoppingcarts WHERE CartID='$cartID' AND ProductID=$pid "; $db->query($sql); } ^ top 12.3.6: Updating an Item When a user changes the quantity of an item in their cart, updateItem() is called The function call is generated using JavaScript The select list contains the following JavaScript: onChange="UpdateQty(this)" This script in turn calls the JavaScript included in the page header Function updateItem() needs: A cart id: obtained from the getCartId() function A product id: passed to the function as parameter $pid from main() The quantity of items: obtained from the getQuantity() function If the quantity is less than or equal to zero, it calls the deleteItem() function Otherwise, it generates the SQL to update the quantity for the item Example Shopping Cart Item  Product  Quantity   Price   Action  Canvas 1 2 3 4 5 6 7 8 9 10 $22.50 Remove? The updateItem() Function function updateItem($db, $pid) { $cartID = getCartId(); $qty = getQuantity(); if ($qty <= 0) { deleteItem($db, $pid); } else { $sql = "UPDATE shoppingcarts SET Quantity=$qty WHERE CartID='$cartID' AND ProductID=$pid "; $db->query($sql); } } ^ top 12.3.7: Showing the Cart To show the cart we need the cart id This is obtained from the getCartId() function With this we generate the SQL to select all the items in their cart After this, we call the following functions as needed: showHeading(): displays the table heading showItem(): displays a table row showFooter(): displays the end of the table Each of these functions outputs HTML code The showItem() function uses a FormLib object to generate the select list The showContent() Function function showContent($title, $db) { $cartID = getCartId(); $sql = " SELECT ID, ProductName, PriceEach, Quantity FROM shoppingcarts, products WHERE ID=ProductID AND CartID='$cartID' "; $result = $db->query($sql); echo "<h1>$title</h1>\n"; echo "<table>\n"; showHeading(); $total = 0; while ($row = mysql_fetch_row($result)) { list($id, $prodName, $price, $qty) = $row; $total += $price * $qty; $price = "$".number_format($price, 2); showItem($id, $prodName, $price, $qty); } $total = "$".number_format($total, 2); showFooter($total); echo "</table>\n"; } Excerpt from the showItem() Function $f = new FormLib(); $data = array(); for ($i = 1; $i <= MAX_QTY; $i++) { $data[$i] = $i; } $other = 'onChange="UpdateQty(this)"'; $opt = $f->makeSelect($id, $data, $qty, $other); ... ^ top Exercise 12.3 In this exercise we explore the use of the shopping cart page. Specifications Verify your web server is started and all the support files are installed as specified in the first 4 steps of exercise 12.1. In addition, verify you have the Artzy database and all the files installed from exercise 12.2 In addition, verify the following files are installed in a subdirectory named includes of the htdocs/phptest directory: formlib.php formverifier.php If needed, copy the files and paste them into a text editor such as TextPad. After saving, verify you saved the page without turning angle brackets and other special characters into HTML entities such as &lt; and &gt;. Add the following file to the phptest directory: cart.php: a simple product catalog Copy the file and paste it into a text editor such as TextPad. After saving, verify the editor saved the page without turning angle brackets and other special characters into HTML entities like &lt; and &gt;. View the cart.php file as a web page in your browser using the address: http://localhost/phptest/cart.php. Note how the features of the cart.php page work and how the products.php and cart.php pages interact. There is no need to turn in any of these files. ^ top 12.3.8: Summary Our shopping cart design provides the user with the functions: Add an item to their cart Delete an item from their cart Change the quantity of items for each product in their cart See the products that exist in their cart Shows the total cost of all products in their cart The code to provide each of these is contained in a PHP function Function main() decides which an action is needed and calls the appropriate function We maintain persistence for long durations by saving session id's in cookies Even if the user closes their browser, they can return and view their shopping cart We also use JavaScript to improve the user interface ^ top Wrap Up Due Next: Final Project Report and Presentation (6/1/09) When class is over, please shut down your computer if it is on Work on your project! ^ top Home | WebCT | Announcements | Course info | Expectations | Schedule Project | Help | FAQ's | HowTo's | Links Last Updated: May 11 2009 @19:08:19