CSC 325 Grinnell College Fall, 2008
 
Databases and Web Application Design
 

Laboratory Exercise on Database Processing using PHP

This laboratory exercise provides practice using PHP to execute various SQL statements on a MySQL database.

Overview

Web-based scripts accessing a database typically follow these basic steps:

  1. Print Web-page headers
  2. Establish a connection to the database package
  3. Identify the desired database
  4. Formulate, execute, and process SQL queries
  5. Close the database connection

Although the details of these steps vary somewhat from one programming language to another, work in various languages typically is conceptually similar.

The previous lab on the MySQL database asked you to work with a csc325 database and a faculty table within that database. Here are two programs written to illustrate script processing and to list the records in the faculty table.

Steps for this Lab

  1. Run each of these scripts to see how they work.

  2. Since each of these programs must connect to MySQL using a password, the listing of each program is not accessible over the Web. However, the programs are available to those logged into the departmental network. Copy these files to your account.

  3. Referring to the textbook, be sure you understand how each step of the HTML/PHP script fac-dir-db.php works.

  4. The CGI script running Java proceeds in two steps (with two files).

    Review Java class facDirJavaDb.java.

    1. For each stage of processing, identify the parallel PHP and Java statements.

    2. Does the Java class have any additional statements? If so, why do you think they are needed?

  5. Modify the HTML/PHP script, so that names are printed alphabetically by first name in reverse alphabetical order.

Reseting the Database

Since the faculty table of the csc325 database may be used for class experimentation, students may insert new data, delete old data, or modify records as part of class exercises. Since the contents of the current database may be somewhat unpredictable, it can be helpful to be able to reset the database to its original records. This can be done with either of two scripts:

Each of these scripts may be found in the same directories as described above for Step 2.

  1. Review the PHP and Java scripts. Write a paragraph identifying similarities and differences in the two pieces of code. (For example, compare the INSERT statements in these two scripts.)

Additional Practice

  1. Write a Web page that asks for the last name of a faculty member. Upon clicking "submit", the Web page should call a HTML/PHP script that retrieves faculty data from the database. If the last name does not match any faculty members, the script should indicate "no faculty match". If the last name does match, then all faculty members with the given last name should be printed. (For testing, note that both Thomas Moore and Emily Moore are in the faculty database.)

    Hint: With an adjustment of the SELECT statement, few (if any) changes are needed in the rest of the program!

  2. Write a Web page that asks for the first and last names of a faculty member and a phone extension. Clicking "submit" calls a HTML/PHP script that works as follows:

    Hint: Data retrieval from the database can be done with a single SQL query. After setting up the basic SELECT statement, add modifiers to a WHERE clause based on what data are entered.

  3. Write a Web page that asks for faculty data, including first name, last name, title, email, office, and telephone extension. Upon clicking "submit", the HTML/PHP script should check that all fields are filled in. If not, the script should print an error message. Otherwise, the script should insert the data into a new entry within the faculty table.

Work to Turn In


This document is available on the World Wide Web as

     http://www.walker.cs.grinnell.edu/courses/325.fa08/lab-database-php.shtml

created 20 August 2008
last revised 20 August 2008
Valid HTML 4.01! Valid CSS!
For more information, please contact Henry M. Walker at walker@cs.grinnell.edu.