Platinum Edition Using HTML 4, XML, and Java 1.2
(Publisher: Macmillan Computer Publishing)
Author(s): Eric Ladd
ISBN: 078971759x
Publication Date: 11/01/98

You need to decide what bits (entities) of information you want to store and how this information is related to other entities. The usual technique, when designing a database, is to draw a graphical display of the database. This drawing is called an Entity-Relationship (E-R) diagram. If you take a look at Figure 29.2, you can see that each box corresponds to a table. Each line in the box represents a specific field. When required, each box is connected to another where an entity is related to an entity in another table.

FIGURE 29.2  An E-R diagram shows how the information in various tables is to be related.

The purpose of having relational databases is to limit the amount of repetitive information contained in the database. After someone’s address is entered in one table, for example, you should never have to provide an address in any other table.

Making SQL Queries

Structured Query Language (SQL) is a language that provides a user interface to relational database management systems (RDBMS). Originally developed by IBM in the 1970s, SQL is the de facto standard for storing and retrieving information for relational databases, as well as being an ISO and ANSI standard. The current standard is SQL2, with SQL3, which was released in 1998. The purpose of SQL is to provide a language, easily understandable by humans, that interfaces with relational databases.

When adding, removing, and retrieving information from SQL databases, you perform what is called a query. A query is a command that is sent to the database, telling the database engine what to do to manipulate the contents of a database.

One example of a query is provided below using the select command. The select command enables you to specifically define what information you want retrieved from the database. You can be as broad or as specific as you want when retrieving information from the database.

select * from personal,department,finance where Name=’Fred Flintstone’

This would pull up all information on Fred Flintstone—from all the tables. You could even be more specific, pulling only certain parts from each table:

select Name.finance,Name.department,Tardy.department
       from department,finance
       where Tardy > 5 AND Name.finance = Name.department

Many commands are used to query a database, providing you with quite a bit of power when working with a relational database. Most SQL servers use basic commands such as select, but minor differences can be noticed. It is best to check the documentation provided with your SQL server for a complete list of commands and the proper syntax for each command.

Debugging Database Applications

One of the biggest problems when dealing with databases on the Web is trying to fix any problems that may occur when you are writing your script. First, you can have problems because of a bug in your script. Second, you can have problems because of an improper query.

The best way to see whether the problem lies within your script is to create a copy of the script that includes dummy variables—variables that contain information as if a visitor actually entered something into a form. Using the previous scripts, for example, you can create a set of variables that mimics information that a visitor might have entered:

$contents{‘fname’} = “John”;
$contents{‘lname’} = “Doe”;

After you have the dummy variables set, you can execute your script using the command line

% /usr/bin/perl phone.pl

Perl will report any problems with your script if you have programmed any code improperly.

To figure out what may be wrong with your SQL query, you can usually use the console interface provided with your SQL server. If you construct the query with the console interface and everything works fine, then look at your script and see if an error can be produced there.

	Last, try to keep things simple and build up from what you know works. At first, create a script that contains the query you need to perform, making sure that you use the Content-type text/plain. This will enable you to make sure that your script is providing a proper query. If all goes well, access your database using the same query from the command line. If the query produces the proper results, you can move on and try to access your script through the Web server. If you are having problems here, make sure that you have specified the Content-type before anything is sent out to the server. This is probably one of the biggest problems with any script. An error occurs and your script sends information back to the Web server without specifying the Content-type.