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.
- 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 for order-item information.
The two tables have the following fields:
orderID (int, primary key, autonumber)
orderID (primary key, int)
ISBN (primary key, varchar(15))
- checkout03.php -- This page processes the customer order in the following
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
Write or update the customer's shipping information in the database. Steps:
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.
For security and privacy custID must be encrypted whenever
it is sent to the client (in cookies, query string, 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
Add the code
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
$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'.)
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.
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
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.
New customers only: After creating a new record in bookBustomers you will need to
retrieve the custID that was generated as an autonumber field. Retrieve the custID
using the function mysqli_insert_id($link)
and assign it to a local variable.
With the customer information written, you are now ready to write the order information
to the database.
- Write the customer's order to the order table. Steps:
Retrieve the order information from the cookie and copy it into an array ($bookArray)
as done in the shopping cart.
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);
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.
Retrieve the orderID using mysqli_insert_id().
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
$discount = 0.8;
$sql = "INSERT INTO bookorderitems (orderID, isbn, qty, price) VALUES
($orderID, '$isbn', $qty, (select (price * $discount) from bookdescriptions where ISBN = '$isbn'))";
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.
Display order confirmation information. Steps:
The shipping information (name, address) is stored in local variables. Echoing the
information in straightforward.
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 shopping cart
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.
- Send an email confirmation to the user using the mail() function. The syntax is:
mail($email, $subject ,$body, 'From: firstname.lastname@example.org');
$email is the recipient's email address
$subject is the email subject
$body is the text in the body of the message
'From: email@example.com' 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.
- 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.