IT360 Lab 10: Web Databases - PHP and MySQL

 

DUE: March 31, 2014,  BEFORE 23510 (paper copies due at start of lab next day)

 

This lab should get you familiarized with accessing MySQL databases over the web using PHP.  In this lab, you will create a web interface for your Online Store, so an administrator can insert, display and delete products in the database. 

·         For this lab you will be using your MySQL database on cardhu.academy.usna.edu. If you do not remember your user or password, contact your instructor.

 

Set-Up

 

a)      Connect to the MySQL server on cardhu.academy.usna.edu by using MySQL Workbench.

 

b)      Check that you have the Product table (or something equivalent) and that your table has at least the BarCode, ProductName and Price columns (or something equivalent). The primary key for the table should be BarCode (or its equivalent).  For this lab, you will just need the Product table. If you do not have this table, create it using the code provided on the course calendar for this lab.

 

c)      Create a Lab10 directory on your W drive. All the php and html files created for this assignment should be stored in the W:/Lab10 folder. 

 

d)     Copy your version of the page.inc.php from Lab9 or from the course website to Lab10.

 

e)      Look through the songPage.php and song.inc.php available on the course webpage. A lot of the code required from this lab is similar to the code provided.

 

PART 1: Connect to MySQL from PHP (5%)

 

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

 

To connect to MySQL from PHP, you normally need to create a mysqli object, and then check for errors

 

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

 

$db 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, we discussed in class about using a custom myConnectDB class.

 

Do this: Download myConnectDB.inc.php  from the course website. Open the file and familiarize yourself with its content. Change the values for the default parameters to correspond to the database server that we use (cardhu.academy.usna.edu) and your account information.

 

To test your connection, create a file “productPage.php” with the following content. Run it from the address bar of your browser.

 

<?php

require_once('page.inc.php');

require_once(‘myConnectDB.inc.php’);

 

//create a page object

$page = new Page("OnlineStore - Manage Products");

 

//create a connection

@ $db = new myConnectDB ();

 

//check connection

if (mysqli_connect_errno()) {
    $page->content =  ‘<p>Connect failed: ‘. mysqli_connect_error(). ‘</p>’;

    $page->display();

    exit;
}

 

//everything OK

 $page->content = “<p>Connection successful</p>”;

 

//display page

$page->display();

?>

 

 

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

 

a) Create a Product class (in product.inc.php) with attributes corresponding to the columns of the Product table in the database. Add a constructor, get and set methods. Add a method addProduct($db) that inserts the product into the database and returns true if the insert was successful, false otherwise.

 

b) Add code to the productPage.php that you created before to display a form to get the product information from the user. 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:

 

 

c) When the user fills out the form and submits it, the new product should be inserted into the database and a confirmation message is displayed to the user.  To achieve this, add code to your productPage.php to be processed when the user submits the form: create a product object using the “posted” values from the form and call the function addProduct to insert the product into the database. Depending on the return value of the function, display a user-friendly message back to the user.

 

        

 

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

 

PART 3: Display all  products (25%)

 

a)      Add a static method printProducts($db) to your Product class to return a string that will display all products from your Products table as an HTML table.

 

b)     Modify your productPage.php to also display all products (it should also allow insertion of new products, as done in Part 2).

 

 

PART 4: Delete a product from the database (25%)

 

a)      Add a deleteProduct($db) method to your Product class, that deletes the product with the given barcode from the database, and returns true if  the product was indeed deleted, and false otherwise

b)      Modify your method that displays the table with all the products to include a radio button for each product (use the same “name” attribute for the input field in the HTML, and the “value” should be the barcode of the current product)

 

c)      Modify your productPage.php to display the table with all the products inside a <form> and add a “delete” submit button.

 

d)     If the user selects one of the products using the radio button, and then clicks on “Delete”, the product selected should be deleted from the database and a confirmation message should be displayed to the user.

 

           

 

 

PART 5: SQL (10%)

 Given the following table:

Product(BarCode, PName, Price)

Write the SQL query to achieve the following: Display the number of products with price higher than the average price for all products.  

 

PART 6:  Query the database (Extra credit)

 

a) Modify “productPage.php” by adding a “Find” button to the form, to allow the user to search for products.

 

b)      The purpose of the form is to allow a customer to search for products. If a search term is specified, the customer is searching for products that contain the search term in the product name – sounds like ‘%$word%’.  If no search term is specified (nothing is posted in the product name field), the customer is searching for all available products. 

 

Modify your Product class: add a method (or modify your existing method) to display as an HTML table the products that match a given keyword.

 

PART 7: Update the price (Extra credit)

 

Create a button and mechanism to update the price in the product table.

 

Turn in

Electronic (due March 31, 2014, 2359):

  1. Upload all files from your Lab10 folder to the Lab 10 assignment on the blackboard.  This should include productPage.php, and product.inc.php and the SQL question in Part 5.

Hard-copies (due April 1, 2014, before start of lab):

  1. The completed assignment coversheet. Your comments will help us improve the course.
  2. A print-screen of the web browser after Part 4 is completed.
  3. For extra credit, a print-screen of the web browser after Part 6, 7 completed
  4. A hard copy of productPage.php and product.inc.php and the answer to the SQL question on Part 5.