PHP 5/MySQL Programming- P84 docx
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (125.53 KB, 5 trang )
• If I want to improve a module, I do it once in the library rather than in
several places.
• It is extremely simple to modify the code library so it works with another
database.
• The details of each particular module are hidden in a separate library so
I can focus on the bigger picture when writing each PHP page.
• The routines can be reused to work with any table in the database.
• The routines can automatically adjust to changes in the data structure.
• The library can be readily reused for another project.
Figure 12.11 shows a more detailed state diagram.
When you begin looking at actual code, you’ll see most of the PHP programs are
extremely simple. They usually just collect data for a library function, send pro-
gram control off to that function, and then print any output produced by the
function.
Writing the Non-Library Code
I begin here by describing all the parts of this project except the library. The
library module is driven by the needs of the other PHP programs, so it makes
sense to look at the other programs first.
393
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.11
This state diagram
illustrates the
relationship
between PHP
programs and
functions in the
spyLib code
library.
Preparing the Database
The database for this segment is almost the same as the one used in chapter 11,
“Data Normalization.” I added one table to store queries. All other tables are the
same as those in chapter 11. The SQL script that creates this new version of the
spy
database is available on the CD as buildSpy.sql.
Note I have modified the database slightly from chapter 11, because the new ver-
sion includes several queries as part of the data! In order to make the program
reasonably secure, I don’t want typical users to be able to make queries. I also
don’t want users to be limited to the few queries I thought of when building this
system. One solution is to store a set of queries in the database and let appropri-
ate users modify the queries. I called my new table the
storedQuery table. It can
be manipulated in the system just like the other tables, so a user with password
access can add, edit, and delete queries. Here is the additional code used to build
the
storedQuery table:
######################################
# build storedQuery table
######################################
CREATE TABLE storedQuery (
storedQueryID int(11) NOT NULL AUTO_INCREMENT,
description varchar(30),
text varchar(255),
PRIMARY KEY (storedQueryID)
);
INSERT INTO storedQuery VALUES (
null,
‘agent info’,
‘SELECT * FROM agent’
);
The storedQuery table has three fields. The description field holds a short English
description of each query. The
text field holds the query’s actual SQL code.
Examining the spyMaster.php Program
The sypMaster.php program is the entry point into the system. All access to the
system comes from this page. It has two main parts. One handles queries from
ordinary users, and the other allows more sophisticated access by authorized
394
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
users. Each segment encapsulates an HTML form that sends a request to a par-
ticular PHP program. The first segment has a small amount of PHP code that sets
up the query list box.
Proper SQL syntax is extremely important when you store SQL syntax inside an
SQL database as I’m doing here. It’s especially important to keep track of single
and double quotation marks. To include the single quotation marks that some
queries require, precede the mark with a backslash character. For example,
assume I want to store the following query:
SELECT * FROM agent WHERE agent.name = ‘Bond’
I would actually store this text instead:
SELECT * FROM agent WHERE agent.name = \‘Bond\’
This is necessary for storing the single quotation mark characters. Otherwise they
are interpreted incorrectly.
In SQLite, the syntax is two single quotation marks together. The SQLite version of
the query text reads like this:
SELECT * FROM agent WHERE agent.name = ‘’Bond’’
Creating the Query Form
<html>
<head>
<title>Spy Master Main Page</title>
<?
include “spyLib.php”;
?>
</head>
<body>
<form action = “viewQuery.php”
method = “post”>
<table border = 1
width = 200>
<tr>
<td><center><h2>View Data</h2></center></td>
</tr>
<tr>
<td><center>
<select name = “theQuery” size = 10>
TRAP
395
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
<?
//get queries from storedQuery table
$dbConn = connectToSpy();
$query = “SELECT * from storedQuery”;
$result = mysql_query($query, $dbConn);
while($row = mysql_fetch_assoc($result)){
$currentQuery = $row[‘text’];
$theDescription = $row[‘description’];
print <<<HERE
<option value = “$currentQuery”>$theDescription</option>
HERE;
} // end while
?>
</select>
</center>
</tr>
<tr>
<td><center>
<input type = “submit”
value = “execute request” >
</center></td>
</tr>
</table>
</form>
Most of the code is ordinary HTML. The HTML code establishes a form that calls
viewQuery.php when the user presses the Submit button. I added some PHP code
that generates a special input box based on the entries in the
storedQuery table.
Including the spyLib Library
The first thing to notice is the include() statement. This command allows you to
import another file. PHP reads that file and interprets it as HTML. An included
file can contain HTML, cascading style sheets (CSS), or PHP code. Most of the func-
tionality for the
spy data program is stored in the spyLib.php library program.
396
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
All the other PHP programs in the system begin by including spyLib.php. Once
this is done, every function in the library can be accessed as if it were a locally
defined function. This provides tremendous power and flexibility to a program-
ming system.
Connecting to the spy Database
The utility of the spyLib library becomes immediately apparent as I connect to
the
spy database. Rather than worrying about exactly what database I’m con-
necting to, I simply defer to the
connectToSpy() function in spyLib(). In the cur-
rent code I don’t need to worry about the details of connecting to the database.
With a library I can write the connecting code one time and reuse that function
as needed.
Notice the
connectToSpy() function returns a data connection pointer I can use
for other database activities.
There’s another advantage to using a library when connecting to a database. It’s
likely that if you move this code to another system you’ll have a different way to
log in to the data server. If the code for connecting to the server is centralized,
it only needs to be changed in one place when you want to update the code.
This is far more efficient than searching through dozens of programs to find every
reference to the mysql_connect() function. Also, if you want to convert the
MySQL-based code in this book to SQLite or another database system, you only
have to change the connectToSpy() function. That’s pretty cool, huh?
Retrieving the Queries
I decided to encode a series of prepackaged queries into a table. (I explain more
about my reasons for this in the section on the
viewQuery program.) The main
form must present a list of query descriptions and let the user select one of these
queries. I use an SQL
SELECT statement to extract everything from the storedQuery
table. I then use the description and text fields from storedQuery to build a
multiline list box.
Creating the Edit Table Form
The second half of the spyMaster program presents all the tables in the database
and allows the user to choose a table for later editing. Most of the functionality in
the system comes through this section. Surprisingly, there is no PHP code at all in
this particular part of the page. An HTML form sends the user to the
editTable.php
program.
TRICK
397
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
