Introducing the Spy Master Program

Introducing the Spy Master Program

The Spy Master program is a suite of PHP programs that allows access to the spy database created in Chapter 9, "Data Normalization." While the database created in that chapter is flexible and powerful, it is not easy to use unless you know SQL. Even if your users do understand SQL, you don't want them to have direct control of a database, because too many things can go wrong. You need to build some sort of front-end application to the database. In essence, there are three levels to this system. The client computer handles communication with the user. The database server (MySQL) manages the actual data. The PHP program sits between the client and the database acting as an interpreter. PHP provides the bridge between the HTML language of the client and the SQL language of the database. This kind of arrangement is frequently called a three-tier-architecture. As you examine the Spy Master program throughout this chapter you'll learn some of the advantages of this particular approach.

Viewing the Main Screen

Start by looking at the program from the user's point of view as shown in Figure 10.1.

Click To expand
Figure 10.1: The entry point to the Spy Master Database is clean and simple.

The main page has two sections. The first is a series of data requests. Each of these requests maps to a query.

Viewing the Results of a Query

When the user selects a query and presses the Submit button, a screen like the one in Figure 10.2 appears.

Click To expand
Figure 10.2: The results of the query are viewed in an HTML table.

The queries are all pre-built. This means the user cannot make a mistake by typing in inappropriate SQL code, but it also limits the usefulness of the database. Fortunately, there is a system for adding new queries, as you will see.

Viewing Table Data

The other part of the main screen (shown again in Figure 10.3) allows the user to directly manipulate data in the tables. Since this is a more powerful (and thus dangerous) enterprise, access to this part of the system is controlled by a password.

Click To expand
Figure 10.3: From the main screen you can also access the table data with a password.

As an example, if I select the agent table, I'll see a screen like Figure 10.4.

Click To expand
Figure 10.4: The editTable screen displays all the information in a table.

From this screen, the user can see all the data in the chosen table. The page also gives the user links to add, edit, or delete records from the table.

Editing a Record

If the user chooses to edit a record, a screen similar to Figure 10.5 will appear.

Click To expand
Figure 10.5: The user is editing a record in the agent table.

The "Edit Record" page has some important features. First, the user cannot directly change the primary key. If the user could do so, it would have profound destabilizing consequences on the database. Also note the way the operationID field is presented. The field itself is a primary key with an integer value, but it would be very difficult for a user to manipulate the integer values directly. Instead, the program provides a drop-down list of operations. When the user chooses from this list, the appropriate numerical index will be sent to the next page.

Confirming the Record Update

When the user clicks the button, a new screen appears and announces the successful update as in Figure 10.6.

Click To expand
Figure 10.6: The user can see the newly updated record.

Deleting a Record

The user can also choose to delete a record from the "Edit Table" page. This action results in the basic screen shown in Figure 10.7.

Click To expand
Figure 10.7: It's very easy to delete a record.
TRICK?

You can tell from this example why it's so important to have a script for generating sample data. I had to delete and modify records several times when I was testing the system. After each test I easily restored the database to a stable condition by reloading the buildSpy.sql file with the MySQL SOURCE command.

Adding a Record

Adding a record to the table is a multi-step process much like editing a record. The first page (shown in Figure 10.8) allows you to enter data in all the appropriate fields.

Click To expand
Figure 10.8: The add screen includes list boxes for foreign key references.

Like the "Edit Record" screen, the "Add Record" page does not allow the user to enter a primary key directly. This page also automatically generates drop-down SELECT boxes for foreign key fields like operationID.

Processing the Add

When the user chooses to process the add, another page appears confirming the add (or of course describing the failure if it cannot add the record for some reason). This page is shown in Figure 10.9.

Click To expand
Figure 10.9: The user has successfully added an agent.