DUE: March 10, 2009, BEFORE start of lab

 

This lab should get you familiarized with accessing MySQL databases over the web using PHP, and using session variables.  We will use a database for an online store. In this lab, you will create a web interface so new products can be inserted in the database, and customers can purchase the products.

Set-Up

 

·        For this lab you will be using your MySQL database on schooner.cs.usna.edu. Your user account and the database name are mxxxxxx, where xxxxxx is your alpha. You can connect to schooner.cs.usna.edu server by using your user name and password. If you do not remember your password, contact your instructor.

 

·        Using MySQL interactively:

·        Establish a connection to the MySQL server: From “Start”ŕAll ProgramsŕMySQLŕMySQL Query Browser. Fill out the information requested:

o       Server Host: schooner.cs.usna.edu

o       Username: mxxxxxx

o       Password:  yourpassword

o       Default Schema: mxxxxxx (xxxxxx is you alpha).

Click “OK”. A window similar with the following one should open up:

 

 

·        Write and execute SQL queries: Write the SQL query in the Query Window. Click “Execute” button, or select QueryŕExecute from the menu, to execute the query. Note that you need to terminate each SQL statement with a semi-colon (;), if you have more that one SQL statement in the window / script.

 

PART 1: Preliminaries

 

a)      Connect to the schooner.cs.usna.edu MySQL server by using MySQL Query Browser.

 

b)      Create the tables for your online store: you can either use you design from Lab1, if that allows products and sales information to be stored in the database, or execute the script available on the course website, to create the tables that correspond to the following ER diagram:

 

 

To see the tables created: From File menu, select “New Query Tab”. In the Query Window, write “show tables;” and click the Execute button. You should get a list of the tables in your database. To see the columns of a table, for example to see the columns of the Product table, you can use “describe Product;”. 

 

Important note: The default storage engine for MySQL (MyISAM) does not enforce the FOREIGN KEY constraints (it just ignores the foreign key definitions). You should therefore make sure in your application (PHP) that the foreign keys are enforced (for example, when you insert an item in the SaleItem table, the SaleID should exist in the Sale table first, and the barcode should exist in the Product table).

 

Create a Lab5 directory on your W drive. All the files created for this assignment should be stored in the W:/Lab5 folder.  You will be using an object-oriented approach for this lab.  Copy your version of the page.inc.php file that you would like to use from Lab4 directory.

 

PART 2: Connect to MySQL from PHP (5 points)

 

Now we would like to connect to the database to access some information. 

 

To connect to MySQL from PHP, you need to create a mysqli object.

 

            “variable” = new mysqli (“hostname”, “user_name”, “password”, “database_name”);

 

variable is a variable that stores the newly created object, hostname is the host or server name where MySql database management system resides, the user_name is the name of the user connecting to the db server, password is the password for the user, and database_name is the name of the database to use.

 

Having this line of code in all PHP scripts that need to connect to the database is very insecure, since the password appears in plain text. Also, it is difficult to maintain the code, if the password actually changes. For these reasons, you will have to modify the following myConnectDB class that extends the mysqli class, such that the constructor of your class has at least the user_name and password parameters optional.  Note that all parameters with default values should be listed last in the list of parameters.

 

<?php

class myConnectDB extends mysqli{

           

            public function __construct($hostname, $user, $password, $dbname){

                        parent::mysqli($hostname, $user, $password, $dbname);

            }

}

?>

 

Use your page.inc.php file to create a test page to see if you can connect to the database using the new myConnectDB class.

 

From now on, you should always use the myConnectDB class to connect to your database.

 

PART 3. Insert into the database (25%)

 

For this part, you will write the code to allow an administrator to insert products into the database, using a web interface.

 

3.1) Create an input page called “product_page.php” using your page.inc.php file that includes the following form. The action should be “product_page.php”. Here is how the form could look like, when rendered by a browser. Feel free to change the design of the page, as well as the content, to fit your database:

 

Text Box: Bar Code             

Product name      

Price

 

 

 

 

 

 

 

 

 

3.2) The purpose of the form is to allow an employee to insert products. But administrators may need to perform other actions on the Products table, such as find and delete.  Create another file called “product.php” which defines a class called product.  It should include variables for each product attribute and functions that may be needed when adding, deleting, reading or updating (CRUD) product records.  For this exercise, just create a function for adding a new product to the Product table in the database. 

 

function add_product($product,$db); 

 

Hint: This will use an insert statement

 

3.3) Now add code to product_page.php to create a product object, assign “posted” values from the form to the attributes of that object and call the function add_product for your database.

 

Test you program. Input some data, submit, and check in the MySQL database if the appropriate rows were inserted into Product table.

 

PART 4:  Create a shopping cart (70%)

 

The online shopping cart is a specific online shopping mechanism: while the potential customer browses the online catalog of products, he/she can choose products to add to his/her cart. At check-out, the products from the cart are purchased. In order to implement the shopping cart functionality, you need to:

  1. Provide some way for a customer to browse the products in your store. The products are stored in the MySQL database, so your program needs to get the products from the database.
  2. Implement a shopping cart to track the products a customer wants to buy. You should keep track not only of the products the users want to buy, but also the quantities ordered. You have to use session variables for this task.
  3. Have a checkout script that processes the order. The checkout script needs to ask for payment and delivery information from the customer, record the sale information to the database, display a confirmation message, and empty the cart. The sale information in the database should include the products bought, quantities, delivery address, and payment information. 

 

Below are detailed steps on how you could implement the shopping cart for your online store. Chapter 27 in the textbook (PHP and MySQL web development) is a good reference for this task.

 

Details:

 

Add to cart:

 

4.1) Modify the form on the page called “product_page.php” to also display a list of all products (barcode, name and price), with radio buttons or a checkbox for each product. Add a “Add to cart” button to the form. The action should be “product_page.php”.

 

4.2) The purpose of the form is to allow a customer to select the products it wants to add to the cart.

 

Create a new shoppingCart.ini.php file that will contain the code for a shoppingCart class. Create a function in this class that allows products to be added to the cart:

 

function add_to_cart($barCode, $quantity)

 

Hint: This will use a session variable $_SESSION[‘cart’] to store the product and quantity (remember the class exercise).

 

4.3) Now add code to product_page.php so if the user presses the “Add to Cart” button, you create a shoppingCart object and call the add_to_cart function for each of the products selected by the customer. Print some appropriate feedback message to the user and allow the user to add more products to the cart (re-display the products list).

 

View Cart

 

4.4) Add a “View cart” button to the form on product_page.php.

 

4.5) Create a list_cart_products($db) function in the shoppingCart class that returns a table with all the products in the cart (including the barcode, product name and price), and an input text field for the quantity (best name for this input test is the BarCode for the product). The current quantity in the shopping cart should be displayed, but the user will be able to modify the quantity (that is why you need an input text field). Note that the barcode and quantity are stored in the session variable $_SESSION[‘cart’], but you will need to get from the database the product name and price corresponding to each barcode.

 

4.6) Add code to product_page.php so if the user clicks on the “View cart” button, the shoppingCart_page.php that you will write next is executed (use header(Location:shoppingCart_page.php))

 

Create the shoppingCart_page.php similar with the product_page.php, but this one will deal with the shopping cart only, instead of all products. shoppingCart_page.php should create a new shoppingCart object, create a new connection to the database, invoke the list_cart_products function in the shoppingCart object, and display the list of products returned by the list_cart_products($db) function  into a form. The action for the form should be shoppingCart_page.php. Add an “Update Cart” button and a “Checkout” button to the form. Also add a link back to “product_page.php”, in case the customer wants to continue shopping.

 

Test you program. Add to cart several products, and then see if they are displayed when you click on the “View Cart” button in “product_page.php”

 

Update Cart

 

4.7) Add code to shoppingCart_page.php to update the cart if the customer clicks on “Update Cart” button. For each product from the cart (from the session variable $_SESSSION[‘cart’]), check whether the post variable corresponding to the quantity for that product is greater than 0. If yes, update the quantity in the session variable, if not, delete that product from the session variable (unset ($_SESSION[‘cart’][$barCode]); should work fine, if $barCode is the barcode of the product). Re-display the shoppingCart_page.php

 

Test that new quantities are displayed in the shopping cart.

 

Checkout

 

4.8) Add code to shoppingCart_page.php to display another form (it is OK to also leave the list of products there) to ask the user for delivery address and payment information. Add a “Confirm Purchase” button to the form. The action should still be “shoppingCart_page.php”

 

4.9) Add to shoppingCart class in shoppingCart.inc.php file the following function that actually processes the purchase:

 

function buy_cart_products($deliveryAddress, $payment, $db)   

 

Hint: the function should write the sale data into the database, and if everything was OK, terminate the session (destroy the session variables and the session). If some error occurred, the session should not terminate. Write code to:

-  Save order data into the Sales, SaleItems, etc tables in your database. Remember that some of the keys (like SaleID in the Sales table) might be surrogate keys. The surrogate keys were created using AUTO_INCREMENT keyword, and the value for the surrogate key is generated by the database system. If you need that value for subsequent inserts (for example to insert the items for that sale in the SaleItems table), you should obtain it by using $db->insert_id property on the database object you have ($db in the example). This property represents the AUTO_INCREMENT ID created by the previous insert statement.

- If data was saved in the database, destroy the session. After session is destroyed, the customer should not see any items in the shopping cart, even if it executes the function again.

                        -return true if everything ok and false otherwise

 

4.10) Add code to shoppingCart_page.php file to invoke the buy_cart_products with the posted values if the user clicks the “Confirm Purchase” button. Display appropriate message to confirm the order. If for any reason the data cannot be saved in the database, an error message should be displayed.

 

Part 5: Extra Credit

For extra credit, add to the database a Users table containing a login, encrypted password and role. Create a login form, and allow a valid user to log in. Modify the code you created for this lab such that only the admin users are allowed to insert products in the database. All users should be able to make purchases.

 

 

Turn in (due before start of lab on March 10, 2009):

Electronic:

  1. Upload all files from your Lab5 folder to the Lab 5 assignment on the blackboard. 

Hard-copies:

  1. The completed assignment coversheet. Your comments will help us improve the course.
  2. A print-screen of the web browser after Part 3 is completed, and after each step of Part 4.
  3. A hard copy of each file written for this lab.