|Databases and Web Application Design
This laboratory exercise provides practice using PHP to execute various SQL statements on a MySQL database.
Web-based scripts accessing a database typically follow these basic steps:
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.
Run each of these scripts to see how they work.
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.
Referring to the textbook, be sure you understand how each step of the HTML/PHP script fac-dir-db.php works.
The CGI script running Java proceeds in two steps (with two files).
Review Java class facDirJavaDb.java.
For each stage of processing, identify the parallel PHP and Java statements.
Does the Java class have any additional statements? If so, why do you think they are needed?
Modify the HTML/PHP script, so that names are printed alphabetically by first name in reverse alphabetical order.
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.
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.)
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!
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.
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.
This document is available on the World Wide Web as
created 20 August 2008
last revised 20 August 2008
|For more information, please contact Henry M. Walker at firstname.lastname@example.org.