Program 2:  PHP/MySQL - WebPro shopping cart

You have just been hired as the web programmer for a new startup company called, WebPro.  WebPro wants to sell books about web programming on the web (makes sense, doesn't it).  They have asked you to design their web site using PHP and MySQL.  They want the following abilities:

• browse inventory by category
• search inventory by author and title
• purchase multiple books; this includes the ability to
• add books to shopping cart
• view books in shopping cart
• delete books in shopping cart
• purchase books with credit card (only XCard is being accepted at this time)

You have decided to build the WebPro website incrementally in the following steps.

Grading Information:

• all code will be expected to be at /home//www/WebPro
• comment all code that you are given using these comment guidelines
• must get checked out on each step before proceeding to the next

Step 1:    Design the look of WebPro

• Click here to see the code.
• Click here to see lecture notes

• Go to your www directory.  Create a directory called WebPro and make it world accessible (i.e., type chmod o+rx WebPro).  Move into the WebPro directory (i.e., type cd WebPro) and create a file called index.html. Put the html code for the design in index.html.
• To create the sidebar look, you're really designing your site within a table containing one row.  There are two columns:  one for the sidebar and one for your main window (where all your content will go).  Do a view source on the graphics consultant's design to see how she did it.
  The consultant's design (Hint: use snarf and barf to copy the design from the View Source window in Netscape to your index.html file).

Because you need to carry your webpage design throughout the whole website you will need to replicate the html for the table in Part A to every page you create.  One way to avoid typing/copying this multiple times, is to write PHP functions to do this for you.  So, in your PHP code, you will call a function and it will print out the necessary html.  One benefit of doing this is that if you decide to change something later on, you only need to change it in one location.

• Copy the following PHP file html_util.php3  to your WebPro directory.   It contains four functions declarations.  You need to fill in the definitions of the functions.  Note that the functions will contain all php print commands which will be printing out parts of the html which you already have in the index.html file.
• To test out your coding, create a form with one submit button in your index.html file.  The action attribute of your form tag should point to a PHP script called list_categories.php3 which you should also copy to your WebPro directory.

• When viewing your website, be sure to view your website using the http url.  For example:

http://165.24.93.62/~ssmallen/WebPro or  http://dictator/~ssmallen/WebPro

(Your PHP files will need to be interpreted by the apache server running on dictator.)
 

• Be sure that these files have either the extension php or php3 otherwise the server will not recognize them as PHP files.
• Make sure every file in your www directory is world readable and executable (your index.html file needs only to be world readable).
• To get the book image click on the book with with the second mouse button and select "Save Image As...". Save the image into your directory.

Step 2:  Display categories

• Click here to see the code
• Click here to see lecture notes

WebPro has designed a database system for keeping track of their inventory.  Each of their books is placed in a category based on its content.  WebPro currently supplies books in 7 categories:

• CGI
• Java
• Linux
• Perl
• PHP
• Python
• Shell

• To access WebPro's database, type the following at a linux prompt

mysql -u <username> -h dictator WebPro    or

mysql -u <username> -h 165.24.93.62 WebPro



• Type

show tables;

show columns from categories;

see the column names.  You should see only one column, name.

WebPro wants to display the list of categories as soon as the user enters the website.

• Copy the file globals.php3  to your WebPro directory.  This file contains some global variables that you'll need for the rest of the assignment.  A global variable contains a value that remains constant (its value never changes) throughout your program.  Be sure to change the value of $USER to your login.
• Add the following code to your html_util.php3 code:

function print_error( $msg ) {
  print "<H3><FONT COLOR=#FF0000>$msg</FONT></H3>\n";
}

The mysql_util.php3 file (explained below) depends on it.  It formats error messages in html.  Be sure to comment!

• Copy the file  mysql_util.php3  to your WebPro directory.  Be sure to add comments for connect_db and close_db where indicated based on what you learned during lecture.
• Add code to your copy of list_categories.php3 to display the categories.  See  list_categories.php3  for clues as to where to place your code.  Functions you'll need to use (explained during lecture):
• connect_db
• close_db
• mysql_query
• mysql_fetch_object

Step 3:  Displaying books

• Click here to see the code
• Click here to see lecture notes

WebPro wants to be able to display all the books related to category.  So, instead of just displaying the name of the category (what we did in step2), we want to display the name of the category as a link.  When the user clicks on the link, it will print all the books in that category.

• When the user clicks on the category link, it should point to a script called list_books.php3.  In list_books.php3, we want to know which category was clicked.  Therefore we want to pass the category name in a variable called category to list_books.php3.  Use the information you learned during lecture to change your print statement in list_categories.php3 to display as a link (look here  for a clue).

The WebPro database stores the following data with each book in the table books  they provide:

• title - title of book
• category - the categories book fits in best (one of the 7 mentioned in step 2)
• author - author of book
• year - year of publication
• id - a unique id to identify the book
• price - the price of the book

• The above book information is stored in a table called books in the WebPro database.  Log into the database and type

select * from books;

to see all the entries in the database.

• Copy list_books.php3 to your WebPro directory.  Fill in the code where comments are placed to display all the books in a requested category.  Use the code in your list_categories.php3 as a guide.

Step 4:   A search engine (a small one)

• Click here to see the code
• Click here to see lecture notes

Part A:

Create a small form in the sidebar of your webpage (i.e., place code within print_sidebar function in your html_util.php3 file...see html_util.php3 ).  Your form will point to a script called search.php3 and will need to include the following:

• one textbox - will contain the keyword; name the text box searchfor
• one radio button for author - name the radio button searchby with VALUE=author
• one radio button for title - also name this radio button searchby with VALUE=title
• one submit button

Copy  search.php3  to your WebPro directory.  Using the information you learned in lecture, fill in the code at the places indicated in the comments.  Use your list_books.php3 script as a guide on how to print the results of your search.  Test it out!

Hint:  There are two authors with Mark as their first name.  Together they published 4 books.

Extra Credit:

list_books.php3 and search.php3 use the same code to display the list of books.  Rather than replicating the code twice in both locations, we will use a function.  Create a function in your mysql_util.php3 file called print_books which takes one argument, the results returned from the mysql query.  Add the following function declaration in your mysql_util.php3 file.

function print_books( $books ) {
}

Identify the common code in list_books.php3 and search.php3 and fill in the definition of print_books.  Modify your list_books.php3 and search.php3 file accordingly.

Check out the demo of step4!

Step 5(a):  Buying one book - Creating a form

• Click here to see the code
• Click here to see lecture notes

Part A:

WebPro wants their customers to be able to buy a book by clicking on the book title.

• Modify your list_books.php3 script so that the book title is now a link to a script called buy.php3. (Note: if you did the extra credit in step 4 then you will want to edit mysql_util.php3 and make the change in your print_books function).  Since you will need to know what book was chosen, pass the book's id in a variable called id to the buy.php3 script.  Hint:  this is very similar to what you did in Step 2:  Part A.

Copy buy.php3 and form.php3 to your WebPro directory.  Add code into buy.php3 at places indicated with comments using the scripts you've written in the other steps and the information you learned during lecture.  Be sure to add a couple of lines of comments at the top of form.php3.

Part C:

It is often convenient for customers if you place links to pages they are likely to need to access again.  Therefore, we want to add a link on the sidebar that takes customers back to the list_categories.php3 script.  Modify your print_sidebar function in your html_util.php3 file so that it contains a link to list_categories.php3.  Look at html_util.php3  for a hint.

Check out the demo of step 5a!

EXTRA CREDIT( Step 5(b):  Buying one book  - Checking data in a form)

• No code available for extra credit
• Click here to see lecture notes.

• the user typed something in all of the fields
• the user entered a correct Xcard credit card number

First we want to put in the checks for user input.

• Copy the file  receipt.php3  to your WebPro directory.  We want to add a

if ( !$var1 ) {
     ...
} else if ($var2) {
      ...
} .
  .
  .
} else {
   ...
}

statement to the check that all the variables have been set (i.e., the customer has typed in something).  If one of the fields has not been filled in, we want to print a message to the customer asking them to type in something in that field.  Then we display the form again so that they can type it in.  To redisplay the form, we will use the include statement that you learned in lecture.

• When we redisplay the form, we want to keep the information the user has typed in so that they don't have to type it in again.  Look at form.php3 and use the information that you learned in lecture to write a comment at the top of the file explaining how information is maintained when the form is redisplayed (i.e., when you include form.php3)

Then we want to check that they gave us a vaild credit card number. WebPro only takes credit cards of type XCard (which by the way only have 4 digit credit card numbers).  ValidCredit is a company that provides a database of valid credit card numbers (including XCard). WebPro has gained access to the ValidCredit database and wants you to add a check for valid XCard numbers.

• To access ValidCredit's database, type the following

mysql -u <username> -h dictator ValidCredit    or

mysql -u <username> -h 165.24.93.62  ValidCredit
 

• Type

select * from XCard;

to see a list of all the valid XCard credit card numbers.  Pick one and copy it down somewhere so that you can use it to test your code later on.
 

• Write the code in receipt.php3 to check the credit card number using the comments as a guide.

 

Add an email adress to the form.

• Modify form.php3 so that it displays a text box for someone's email address
• Modify receipt.php3 so that it know checks for an email address and prints an error message if the customer doesn't type it in.

Step 6:  Identifying your customer

• Click here to see the code
• Click here to see lecture notes.

Part A:

We're going to use a random integer for our shopper id.  To generate our random shopper ids, we're going to use the srand and rand functions provided by PHP.

• Copy gen_util.php3 to your  WebPro directory.  It contains one function gen_shopper_id.  Based on the information you learned in lecture, add a comment for gen_shopper_id.

Now we want to generate the shopper id as soon as the customer enters the site.

• list_categories.php3 is the PHP script called when the user enters the WebPro website so we want to place gen_shopper_id in there.  However, the user can click on the sidebar at any time to take them back to list_categories.php3.  Therefore, we only want to generate a new shopper id, $sid, if a shopper id doesn't already exist.  Take a look at list_categories.php3 to get a hint as to where to place the code to do this.

Now we want to propagate the shopper id everywhere.  This means that whenever the customer submits a form or clicks on a link, we need to pass the shopper id to the new script.

• In each of the scripts containing forms, you can propogate the $sid by including the following line within the form


print "<INPUT TYPE=hidden NAME=sid value=$sid>\n";

Be sure to add this to add this to form.php3 and html_util.php3 (print_sidebar).

• In each of the scripts containing links, you can propogate the $sid by appending it after the ? (as was done with $id and category in the steps above).  For example,


<A HREF=script-name?sid=$sid>somewhere</A>

Be sure to add this to this to html_util.php3 (print_sidebar), list_categories.php3, and either mysql_util.php3 (print_books)  or list_books.php3 and search.php3 (depending on whether you implemented the extra credit Part 4).

Recall that variables declared outside of a function will not be available inside that function.  Therefore, we need to add

global $sid;

at the top of functions print_sidebar (html_util.php3) and if you implemented the extra credit in Part 4, print_books (mysql_util.php3).

Check out the demo of step 6!

Step 7:  Filling a shopping cart

• Click here to see the code
• Click here to see lecture notes

Now that you have a unique shopper id (and all your books have ids), we can create a shopping cart for each customer.  We do this by entering data into a database table.  The table at a minimum needs to contain a column for shopper ids and a column for book ids.  When a customer purchases a book, we enter an tuple into the table.  So, essentially, the table will contain multiple shopping carts, one for each unique shopper id in the table.

• You will use your own database that you used in the MySQL exercises, <username>_db for your shopping cart table.  So, for example, ssmallen_db.  Log into your database and execute the following statement to create the table for your shopping cart.

create table cart ( sid INT, id  varchar(10) );



• Add a global variable to your globals.php3 file called $CART_DB.  Set it to the name of your database, for example ssmallen_db.

Now when the user clicks on a book title, it will add the book to their cart (i.e., enter a (sid, id) tuple to the cart table.

• Modify your buy.php3 script so that it does an insert into the cart table (rather than showing the form).   See  buy.php3  to see where to make the changes.

Step 8:  Viewing the shopping cart

• Click here to see the code
• Click here to see lecture notes

We want the customer to be able to view the contents of their shopping cart at any time.

• Add a link to the sidebar that points to a script called view_cart.php3.  Don't forget to pass the $sid!  See html_util.php3 for a hint as to where to place the code.

When the customer wants to view their shopping cart, we want to retrieve the books they have in our cart database.  We will then display all the books they've added to their cart and their total amount.

• Copy gen_util.php3 file to your WebPro directory (it is okay to override the one currently in your directory).  Using the information you learned during lecture, write a comment describing what the function get_books_from_cart does.
• Copy view_cart.php3 to your WebPro directory.  Note that it calls a function print_cart in gen_util.php3.
• Complete the definition of print_cart.php3 in gen_util.phpe.  Figure out how you want to display the books (you must at least print out the book title and price).  The code to get the total value of the books is given; just figure out where you want to print it.

EXTRA CREDIT( Step 9:  Deleting an item from the shopping cart)

• No code available for extra credit
• Click here to see lecture notes.

Customers may decide to not to buy a book.  Therefore we must allow users to delete items in their shopping cart.

• Modify your print_cart function in gen_util.php3 so that for each book, there exists a link that points to delete_item.php3.  Don't forget to pass delete_item.php3 the $sid and $id.

When the customer clicks the link to delete the book, we want to remove the ($sid, $id) tuple from the cart database.

• Create delete_item.php3.  Hint:  delete_item.php3 is almost identical to buy.php3 except for the mysql query (i.e. replace insert command with delete command).  Your delete query should delete the tuple that the $sid and $id match.

Step 10:  Checking out

• Click here to see the code
• Click here to see lecture notes

Once the customer has finished shopping they'll need to checkout (just like you do in a grocery store).

• Add a link to the sidebar that points to a script called checkout.php3.  Don't forget to pass the $sid!  See html_util.php3 for a hint as to where to place the code.

When the customer goes to checkout, we display their order (so they can verify it) and then display the order form.

• Copy  checkout.php3  to your WebPro directory.  Notice how we are able to reuse the print_cart function.  Be sure to comment!

Once the customer has clicked the order form, you should list the books that will be sent to them in the receipt you print out.

• Modify your receipt.php3 script so that you print out a list of books.  You will create a function called print_titles in gen_util.php3.  Hint:  print_titles should look very similar to print_cart execept it should display less.  See the function definition in gen_util.php3.  Note:  you will have to delete some of your code in receipt.php3 (the part where you print out one book).

EXTRA CREDIT( Step 11:  Clear shopping cart)

• No code available for extra credit
• Click here to see lecture notes.

• Add the following function call, clear_shopping_cart, after your print_cart function in receipt.php3.  
  
   clear_shopping_cart( $sid );

 
• Look at the function definition for clear_shopping_cart in  gen_util.php3 file.  Add it to your gen_util.php3 and fill in code where the comments indicate.  Verify your code by logging into the mysql database by hand and viewing the cart  table.