388
P
H
P
5
/M
y
S
Q
L
P
r
o
g
r
a
m
m
i
n
g
f
o
r
t
h
e
A
b
s
o

l
u
t
e
B
e
g
i
n
n
e
r
FIGURE 12.6
The user can
see the newly
updated record.
FIGURE 12.7
It’s very easy to
delete a record.
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.
TRICK
Adding a Record
Adding a record to the table is a multistep process, much like editing a record.
The first page (shown in Figure 12.8) allows you to enter data in all the appropri-
ate fields.
Like the Edit Record screen, the Add Record page does not allow the user to
directly enter a primary key. 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 confirms the add (or
describes the failure, if it cannot add the record). This confirmation page is
shown in Figure 12.9.
Building the Design of
the SpyMaster System
It can be intimidating to think of all the operations in the SpyMaster system. The
program has a lot of functionality. It could be overwhelming to start coding this
system without some sort of strategic plan.
389
C
h
a
p
t
e
r 1
2B
u
i
l
d
i
n
g
a
T
h
r

e
e
-T
i
e
r
e
d
D
a
t
a
A
p
p
l
i
c
a
t
i
o
n
FIGURE 12.8
The add screen
includes list boxes
for foreign key
references.
390
P

H
P
5
/M
y
S
Q
L
P
r
o
g
r
a
m
m
i
n
g
f
o
r
t
h
e
A
b
s
o
l

u
t
e
B
e
g
i
n
n
e
r
Creating a State Diagram
Complex programming problems have many approaches. For this particular
problem I decided to concentrate on the flow of data through a series of mod-
ules. Figure 12.10 shows my overall strategy.
The illustration in Figure 12.10 is sometimes called a
state diagram.
This kind of
illustration identifies what particular problems need to be solved and indicates
modules that might be able to solve these problems.
FIGURE 12.9
The user has
successfully
added an agent.
FIGURE 12.10
A state diagram of
the SpyMaster
system.
I began the process by thinking about everything that a data-management system
should be able to do. Each major idea is broken into a module. A

module
often
represents a single screen. A PHP program often (although not always) supports
each model.
The View Query Module
Obviously, users should be able to get queries from the database. This is one of
the most common tasks of the system. I decided that the View Query module
should be able to view any query sent to it and display an appropriate result.
The Edit Table Module
The other primary task in a data system is
data definition,
which includes adding
new records, deleting records, and updating information. This kind of activity
can be destructive, so it should be controlled using some kind of access system.
All data definition is based on the database’s underlying table structure, so it is
important to allow the three main kinds of data definition (editing, deletion, and
updating) on each table.
The Edit Table module provides the interface to these behaviors. It shows all the
current records in a table and lets the user edit or delete any particular record.
It also has a button that allows the user to add a new record to this table. It’s
important to see that Edit Table doesn’t actually cause anything to change in the
database. Instead, it serves as a gateway to several other editing modules.
The Edit Record and Update Record Modules
If you look back at the state diagram, you see the Edit Table module leading to
three other modules. The Edit Record module shows one record and allows the
user to edit the data in the record. However, the database isn’t actually updated
until the user submits changes, so editing a record is a two-step process. After the
user determines changes in the Edit Record module, program control moves on
to the Update Record module, which actually processes the request and makes
the change to the database.

The Add Record and Process Add Modules
Adding a record is similar to editing, as it requires two passes. The first module
(Add Record) generates a form that allows the user to input the new record details.
Once the user has determined the record data, the Process Add module creates
and implements the SQL necessary to incorporate the new record in the table.
391
C
h
a
p
t
e
r 1
2B
u
i
l
d
i
n
g
a
T
h
r
e
e
-T
i
e

r
e
d
D
a
t
a
A
p
p
l
i
c
a
t
i
o
n
The Delete Record Module
Deleting a record is a simple process. There’s no need for any other user input, so
it requires only one module to process a deletion request.
Designing the System
The state diagram is very helpful, because it allows you to see an overview of the
entire process. More planning is still necessary, however, because the basic state
diagram leaves a lot of questions unanswered. For example:
• Will the Edit Table module have to be repeated for each table?
• If so, will I also need copies of all other editing modules?
• Can I automate the process?
• What if the underlying data structure is changed?
• What if I want to apply a similar structure to another database?

• How can I allow queries to be added to the system?
It is tempting to write a system specifically to manage the
spy database. The advan-
tage of such a system is that it will know exactly how to handle issues relevant to
the
spy system. For example, operationID is a foreign key reference in the agent
table, so it should be selected by a drop-down list whenever possible. If you build
a specific module to handle editing the
agent table, you can make this happen.
However, this process quickly becomes unwieldy if you have several tables. It is
better to have a smart procedure that can build an edit screen for any table in the
database. It would be even better if your program could automatically detect for-
eign key fields and produce the appropriate user-interface element (an HTML
SELECT clause) when needed. In fact, you could build an entire library of generic
routines that could work with any database. That’s exactly the approach I chose.
Building a Library of Functions
Although the SpyMaster system is the largest example in this book, most of it is
surprisingly simple. The system’s centerpiece is a file called
spyLib.php. This file
is not meant to run in the user’s browser at all. Instead, it contains a library of
functions that simplify coding of any database. I stored as much of the PHP code
as I could in this library. All the other PHP programs in the system make use of
the various functions in the library. This approach has a number of advantages:
• The overall code size is smaller since code does not need to be
repeated.
392
P
H
P
5

/M
y
S
Q
L
P
r
o
g
r
a
m
m
i
n
g
f
o
r
t
h
e
A
b
s
o
l
u
t
e

B
e
g
i
n
n
e
r