12. Databases and Shopping Carts

What We Will Cover

Elucidations

Homework Questions?

Quiz Questions?

Questions from last class?

^ top

12.1: Encapsulating the Database

Objectives

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 can be 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

^ 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 need to keep our database connection
  • Thus we save the database resource using a class variable:
    • var $_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 in the next section

Making the Connection

  • To make the connection process easy, we call connect() in the constructor
    • function DB($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 in the next section

^ top

12.1.3: 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:
    1. An error-level setting
    2. An error-handling function
  • A class variable stores the error-level setting:
    • var $_errLevel = "halt"; //"ignore", "halt", "warn"
  • There are three possible settings in our system:
    1. ignore: do not report any errors nor take any action
    2. warn: display an error message and continue
    3. 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>
          <b>Database error:</b> $msg<br>\n
          <b>DB Error</b>: ".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
    • var $_errLevel = "warn"; //"ignore", "halt", "warn"

^ top

12.1.4: 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
    • var $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
    1. Assign the SQL statement to a variable
    2. 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.5: 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.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:
    1. ignore: do not report any errors nor take any action
    2. warn: display an error message and continue
    3. 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();

^ top

Exercise 12.1

In this exercise we explore using the DB class.

Specifications

  1. Install the following file in the includes directory.
  2. To test the DB class, place the following script in public_html
<?php
require("includes/db.php");
$db = new DB();

$sql = "SELECT * FROM products";
echo "<p>sql=$sql</p>\n";
$result = $db->query($sql);
$db->showQuery();

// Create and process an error
$sql = "SELECT * FROM somebogustable";
$result = $db->query($sql);
?>

^ top

12.2: Displaying Products

Objectives

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 the 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:

    • Link to file: products.php

The showContent() Function

^ 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

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

^ top

Exercise 12.2

In this exercise we display a simple product catalog.

Specifications

  1. If needed, install the Artzy database using phpMyAdmin
  2. Install the following files in the includes directory.
  3. Optionally, you may place the images in an images directory
  4. Install the products.php file in the public_html directory.

    5. Link to file: products.php

  5. Run the products.php page and observe the features.

^ top

12.3: Coding a Shopping Cart

Objectives

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:
    1. Add an item to their cart
    2. Delete an item from their cart
    3. Change the quantity of items for each product in their cart
    4. 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

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();
    }
}

^ 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 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
    • This Javascript calls our cart.php page with updated quantities
    • Our cart.php page still needs to process the update
  • 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

^ 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 $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 $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

12.3.8: Summary

  • Our shopping cart design provides the user with the functions:
    1. Add an item to their cart
    2. Delete an item from their cart
    3. Change the quantity of items for each product in their cart
    4. See the products that exist in their cart
    5. 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

Exercise 12.3

In this exercise we explore the use of the shopping cart page.

Specifications

  1. Install all the files from the previous exercise: Exercise 12.2
  2. Install the following files in the includes directory.
  3. Install and run the cart.php file in the public_html directory.

    4. Link to file: cart.php

  4. Run the cart.php page and observe the features, including its interaction with the products.php page.

^ top

Wrap Up

^ top

Home | WebCT | Announcements | Course info | Expectations | Schedule
Project | Help | FAQ's | HowTo's | Links

Last Updated: April 30 2006 @21:36:11