Syllabus Schedule Project Labs Canvas Server Access phpMyAdmin Assignment 1 Assignment 2 Assignment 3 Assignment 4 Assignment 5 Assignment 6 Assignment 7 Assignment 8 Assignment 9

Assignment 9 - Checkout03 and Order History

This week you will finish the last part of the checkout process. When you are finished you will have a fully functional interactive bookstore. 
  1. The database will need two new tables for storing order information. Each customer order may contain several books (one-to-many relationship) so we need one table to store order-related information and a second table to store book-related information. The two tables have the following fields:

    bookorders bookorderitems
    orderID (int, primary key, autonumber) orderID (primary key, int)
    custID (int(6)) ISBN (primary key, varchar(15))
    orderdate (int(11)) qty (int(4))
      price (double(6,2))

  2. checkout03.php -- This page processes the customer order in the following sequence:
    1. Validates all user entries from Checkout02. Each field must contain some text. If any of the validation tests fail the user should be notified and provided with a back button (similar to assignment 4 Validate.php). Use exit() or die() to stop terminate execution.

    2. Write or update the customer's shipping  information in the database. Steps:

      1. Determine if customer is new or returning by checking to see if they have a customer ID. Add a hidden form field to checkout02 that contains the custID for returning customers. For new customers the field is left empty.

      2. For security and privacy custID must be encrypted whenever it is sent to the the client (in cookies, querystring, hidden form fields, etc.). This prevents customers from easily impersonating other customers by switching their custID to another customer's ID. Fortunately we have a couple of handy functions for easily to encrypting and decrypting strings. Add the code for encryption.php.txt to your project (remove the .txt extension). Add an include statements near the top of your checkout02 and checkout03 pages to import the code into your page. The syntax to encrypt custID:

        $custIDe = encrypt($custID, $secretPassword);

        where $secretPassword is a global variable located in encryption.php.

        Add a hidden form field named custIDe to checkout02 and populate it with the encrypted ID.

        Also in checkout02 add a button and hidden form field with custIDe that posts to OrderHistory.php. The button to order history should display only for returning customers. (Note: the encrypted custID cannot be passed in the query string because the URL encoding will alter it. It must be passed between pages using a form and method='post'.)

      3. Retrieve the encrypted custID in checkout03.php and decrypt it with:

        $custID = decrypt($custIDe, $secretPassword);

        For error checking it is a good idea to echo both $custIDe and custID to make sure that they were successfully retrieved and decrypted.

      4. The SQL statement uses an INSERT statement for new customers and an UPDATE statement for returning customers. Check the length of custID passed from checkout02 to determine which SQL statement to use. Returning customers will have a custID and new customers will not.

      5. Check your code by echoing the sql statements from the previous step. Make sure that the correct sql is being used for new and returning customers. Any problems at this stage it will cause more problems later. Comment out the echo statement once you have confirmed that your code is working correctly.

      6. New customers only: After creating a new record in bookcustomers you will need to retrieve the custID that was generated as an autonumber field. Retrieve the custID using the function mysqli_insert_id() and assign it to a local variable.

      7. With the customer information written, you are now ready to write the order information to the database.

    3. Write the customer's order to the order table. Steps:
      1. Retrieve the order information from the cookie and copy it into an array ($bookArray) as done in the shopping cart.

      2. Empty the shopping cart by deleting the shopping cart cookie. Do this by setting its expiration date to a past date:

        setcookie($cookieName, null, time()-60000);

      3. If there are any books in the $bookArray insert a new record in the database table bookorders. The INSERT statement contains the custID and the order date (orderID is an auto increment field). I suggest using the php time() function for the time/date because it is easy to convert it to a human readable format using the date() function.

      4. Retrieve the orderID using mysqli_insert_id().

      5. For each book ordered insert a new record in bookorderitems.  Use a foreach loop to loop through each item in $bookArray as done in the shopping cart page. Include the price paid for the books since book prices may change. We can retrieve the price from the bookDescription table using a subquery in the SQL statement as follows:

        $discount = 0.8;
        $sql = "INSERT INTO bookorderitems (orderID, isbn, qty, price) VALUES
        ($orderID, '$isbn', $qty, (select (price * $discount) from bookdescriptions where ISBN = '$isbn'))";

      6. Check your code by echoing the two insert sql statements. Comment out the echo statement once you have confirmed that your code is working correctly.

    4. Display order confirmation information. Steps:

      1. The shipping information (name, address) is stored in local variables. Echoing the information in straightforward.

      2. The book information must be retrieved from the database. The code to do this is very similar to the code used to display book information on the the shopping cart page.

      3. Include a "View Order History" button. This button requires form tags, action='OrderHistory.php', method='post', and a hidden form field named custIDe. See the sample Bookstore site for syntax.

    5. Send an email confirmation to the user using the mail() function. The syntax is:

      mail($email, $subject ,$body, 'From: your@email.address');

      where:
      $email is the recipient's email address
      $subject is the email subject
      $body is the text in the body of the message

      'From: your@email.address' is the sender's email address. Use a real email address here so that you receive bounced email messages.

      The $body variable contains all of the text that appears in the body of the email, including shipping address, books ordered, orderID and total cost.

      Tip: The $body variable includes a listing of the book titles. An easy way to get the book titles is to capture them in a local variable while listing them to the browser (previous step). This saves retrieving the titles from the database a second time. Concatenate this variable into $body.

  3. OrderHistory.php -- This page displays all the orders associated with a custID. This page is very similar to SearchBrowse.php which you may wish to use as a template. The main differences are that it uses a different SQL statement and display different fields. The SQL statement uses custID in the WHERE clause and joins fields from three tables: bookorders, bookorderitems and bookdescriptions.

    Note: the count of books at the top of OrderHistory.php uses the mysql_num_rows() function to count the number of rows returned in $result. This count many be low since it does not include books that were ordered in quantities greater than one. An optional enhancement is to use a the sum() function in a separate SQL query to sum the actual number of books purchased by the customer.

Congratulations! You have completed the basic bookstore!


Submission instructions: Submit assignments via the Canvas course management system. Submit the full URL for each exercise in the assignment, listing the URLs in the same order that they are listed in the assignment. To minimize typos in URLs it is strongly recommended that you copy the URLs from the address bar of the browser rather than trying to type them. Incorrect URLs will not be graded and no credit will be given.

When pages are connected via navigation (as in your Bookstore project) it is only necessary to submit the URL of the first page.

Regular Expressions HTML Color Names Color Picker ASCII Character Codes Unicode Character Codes