Assignment 9 - Checkout03 and OrderHistory

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(11))
   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. In addition, the format of the email address should be validated using regular expressions. If any of the validation tests fail the user should be notified and provided with a back button (alternatively you can validate the user inputs in checkout02 and pass the data to checkout03. If you do this, pass the data to checkout03 via a session object so that the user cannot modify the data after it has been validated).

   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. For security and privacy, encrypt the custID by multiplying it by a large number (6-8 digit integer) in Checkout02 before assigning it to the hidden form field. In Checkout03 divide it by the same large number to decrypt it. This prevents customers from easily impersonating other customers.

      2. 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.

      3. 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 mysql_insert_id() and assign it to a local variable.

      4. 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 ($bookArray2) 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 $bookArray2 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 mysql_insert_id().
         

      5. For each book ordered insert a new record in bookorderitems.  Use a foreach loop to loop through each item in $bookArray2 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 nested SQL statement as follows:
         $sql = "INSERT INTO bookorderitems (orderID, isbn, qty, price) VALUES " .
         "($orderID, '$key', $value, (select price from bookdescriptions where ISBN = '$key'))";
         

   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" hyperlink. This link passes a disguised custID to OrderHistory.php.

   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!