IT360 Lab 7: Web Databases - PHP and MySQL

 

DUE: March 21, 2011,  BEFORE start of lab

 

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 cs-mysqlsrvr.cs.usna.edu. Your user account and the database name are mxxxxxx, where xxxxxx is your alpha. You can connect to cs-mysqlsrvr.cs.usna.edu server by using the 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: cs-mysqlsrvr.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 current query (the query that has cursor on it). Note that you need to terminate each SQL statement with a semi-colon (;), if you have more than one SQL statement in the window / script.

Set-Up

 

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

 

b)      Create the tables for your online store on the new MySQL server: you can either use your script from Lab 5 (possibly modified to match MySQL data types and syntax), 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:

 

 

 

c)      Check that you have the Product table 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.

 

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;”. 

 

d)     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. 

 

e)      Copy your version of the page.inc.php from Lab6 or course website to Lab7.

 

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

 

            “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, we will create and use 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 serverName, yourUserName, yourPassword, yourSchema to correspond to the database server that we use (cs-mysqlsrvr.cs.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 (35%)

 

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:

 

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 (30%)

 

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 (30%)

 

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: SQL

 Given the following table:

Product(BarCode, PName, Price)

Write the SQL query to achieve the following: Display the number of products with price over $10.00.  

 

 

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.

 

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 7: 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 and the SQL question in Part 5.

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 productPage.php and product.inc.php and the answer to the SQL question on Part 5.