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.
Start by looking at the program from the user's point of view as shown in Figure 10.1.
The main page has two sections. The first is a series of data requests. Each of these requests maps to a query.
When the user selects a query and presses the Submit button, a screen like the one in Figure 10.2 appears.
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.
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.
As an example, if I select the agent table, I'll see a screen like Figure 10.4.
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.
If the user chooses to edit a record, a screen similar to Figure 10.5 will appear.
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.
When the user clicks the button, a new screen appears and announces the successful update as in Figure 10.6.
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.
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 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.
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.
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.