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.

• PHP script: fac-dir-db.php
• CGI script running Java: fac-dir-java-db.cgi using Java class facDirJavaDb.java

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.

   • PHP Script: Copy the file from ~walker/public_html/courses/325.fa08/labs/fac-dir-db.php.
   • CGI script running Java: Copy fac-dir-java-db.cgi and facDirJavaDb.java from directory ~walker/325/database.

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

   • fac-dir-java-db.cgi tells the Web server to run a Java program, based on a specification called the Common Gateway Interface or CGI. More precisely, the first line #!/bin/bash tells the server to run a terminal window (actually, the bash shell). The export statement sets the search path for the Java run time environment, and the last line invokes the Java runtime environment to execute the Java class facDirJavaDb.
   • The Java class facDirJavaDb.java establishes the database connection and controls processing.

   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:

• PHP script: fac-dir-db-reset.php
• CGI script running Java: fac-dir-java-db-reset.cgi using Java class facDirJavaDbReset.java

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

6. 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

7. 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!

8. 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:

   • If no names are entered, the script lists all faculty.
   • If some names or phone information are listed, but these data do not match any entries in the direction, the script should indicate "no faculty match".
   • If some names or phone information are listed and match some directory entries, then all matching entries should be printed. For example, during 1998-1999, both Nathaniel Borenstein and Pamela Ferguson shared an office. Thus, entering telephone 3017 (but no name information) should return entries for both of these faculty members.

   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.

9. 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

• Commentary for steps 4 and 6.
• URLs and HTML/PHP script listings for steps 5, 7, 8, and 9.
• Comments on what test cases you have tried and the results obtained.

This document is available on the World Wide Web as

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

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