IT360 Lab 7: Web Databases - PHP and MySQL

 

DUE: March 12, 2010, BEFORE start of lab

 

This lab should get you familiarized with accessing MySQL databases over the web using PHP.  We will use the database for the online store that you created in previous labs (Lab 3). In this lab, you will create a web interface so an administrator can insert, display and delete products in the database. 

Set-Up

 

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

 

b)      Check that you have the Product table that you created in Lab 3, 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 the Product table, you can download and 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;”. 

 

c)      Create a Lab7 directory on your W drive. All the php and html files created for this assignment should be stored in the W:/Lab7 folder.  You will be using an object-oriented approach for this lab. 

 

d)     Copy your version of the page.inc.php from Lab6 to Lab7.

 

e)      Download myConnectDb.inc.php  from the course website. Open the file and familiarize yourself with its content. Change the serverName, yourUserName, yourPassword, yourSchema to correspond to the database server that we use (schooner.cs.usna.edu) and your account information.

 

f)       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 with the code provided.

 

 

PART 1: Connect to MySQL from PHP

 

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

 

            “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 should instead always use the myConnectDB class defined in the myConnectDb.inc.php file that you created and modified above, and create a  new  myConnectDB object using the default values for the input parameters in the constructor.

 

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();

 

//set the page content

$page->title = “Online Store”;

 

//create a connection

$myDB = new myConnectDB ();

 

//check connection

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

else{

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

}

//display page

$page->display();

?>

 

 

PART 2. Insert into the database

 

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) that 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:

 

Text Box: Bar Code             

Product name      

Price

 

 

 

 

 

 

 

 

 

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

 

a)      Add another method 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 the product, as done in Part 2).

 

PART 4: Delete a product from the database

 

a)      Add a static deleteProduct($db, $barCode) 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 is displayed to the user.

 

 

PART 5:  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.

 

Text Box: Bar Code             

Product name      

Price

 

 

 

 

 

 

 

 

 

 

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 6: Update the price (Extra credit)

 

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

 

Turn in (due before start of lab on March 12, 2010):

 

Electronic:

  1. Upload all files from your Lab7 folder to the Lab 7 assignment on the blackboard.  This should include productPage.php, and product.inc.php.

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 2, 3, 4 is completed.
  3. A hard copy of each file written for this lab.