-
原文：
Getting PHP to Talk to
MySQl
Now that
you
’
re comfortable using the
MySQL client tools to manipulate
data
in the database, you can begin using PHP to
display and modify data
from the
database. PHP has standard functions for working
with the
,
we
’
re
going
to
discuss
PHP
’
s
built-in
database
functions.
We
’
ll also show
you how to use the The PHP Extension and
Application
Repository (PEAR) database
functions that provide the ability to
use the same functions to access
any
supported database. This type of flexibility comes
from a process
called
abstraction
. In
programming
interfaces,
abstraction
simplifies
a
complex interaction. It
works by
removing any nonessential
parts of the interaction, allowing you to
concentrate on the important parts.
PEAR
’
s DB classes are one
such
database interface abstraction.
The information you need to log into a
database
is
reduced
to
the
bare
minimum.
This
standard
format
allows
you
to
interact
with
MySQL,
as
well
as
other
databases
using
the
same
functions.
Similarly,
other
MySQL-specific
functions
are
replaced
with
generic
ones
that
know
how
to
talk
to
many
databases.
For
example,
the
MySQL-specific
connect
function is:
mysql_connect($$db_host,
$$db_username, $$db_password);
versus
PEAR
’
s DB connect function:
$$connection =
DB::connect(
);
The same basic information is present
in both commands, but the PEAR
function
also specifies the type of databases to which to
connect. You
can connect to MySQL or
other supported databases.
We
’
ll discuss both
connection methods in detail.
In this chapter,
you
’
ll
learn how
to
connect to a
MySQL server
fromPHP,
how to use PHP to access and
retrieve stored data, and how to correctly
display information to the user.
The Process
The
basic steps of performing a query, whether using
the mysql
command-line tool or PHP, are
the same:
?
Connect to the
database.
?
Select the
database to use.
?
Build a
SELECT statement.
?
Perform
the query.
?
Display the
results.
We
’
ll
walk through each of these steps for both plain
PHP and PEAR
functions.
Resources
When
connecting to a MySQL
database,
you will
use two new
resources. The
first
is
the
link
identifier
that
holds
all
of
the
information
necessary
to connect to the
database for an active connection. The other
resource
is
the
results
resource.
It
contains
all
information
required
to
retrieve
results
from
an
active
database
query
’
s
result
set.
You
’
ll
be
creating
and
assigning both resources in this chapter.
Querying the Database with PHP
Functions
In
this
section,
we
introduce
how
to
connect
to
a
MySQL
database
with
PHP.
It
’
s
quite
simple,
and
we
’
ll
begin
shortly
with
examples,
but
we
should
talk briefly about what actually
happens. When you try connecting to a
MySQL
database,
the
MySQL
server
authenticates
you
based
on
your
username
and password. PHP
handles connecting
to the database for
you, and it allows you to start performing queries
and gathering data immediately.
As in Chapter 8,
we
’
ll need the same pieces
of information to connect
to the
database:
?
The IP address
of the database server
?
The
name of the database
?
The
username
?
The password
Before
moving
on,
make
sure
you
can
log
into
your
database
using
the
MySQL
command-line client.
Figure
9-1 shows
how the steps of
the database interaction
relate
to the
two
types
of
resources.
Building
the
SELECT
statement
happens
before
the
third
function
call,
but
it
is
not
shown.
It
’
s
done
with
plain
PHP
code,
not a
MySQL-specific PHP function.
Figure 9-1. The interaction
between functions and resources when using
the database
Including Database Login
Details
You
’
re going to
create a file to hold the information for logging
into
MySQL.
Storing
this
information
in
a
file
you
include
is
recommended.
If
you change the database password, there
is only one place that you need
to
change it, regardless of how many
PHP
files you have that access the database.
You
don
’
t
have
to
worry
about
anyone
directly
viewing
the
file
and
getting
your database login
details. The file, if requested by itself, is
processed as a PHP file and returns a
blank page.
Troubleshooting connection
errors
One error you may get
is:
Fatal error: Call to undefined
function mysql_connect( ) in C:Program
FilesApache
Software
FoundationApache2.2htdocsdb_ on line 4
This error occurs because PHP 5.x for
Windows was downloaded, and MySQL
support was not included by default. To
fix this error, copy the
php_
file
from
the
ext/
directory
of
the
PHP
ZIP
file
to
C:php
,
and then
C:
.
Make sure there are two lines that are
not commented out by a semicolon
(;) at
the beginning of the line like these:
extension_dir =
extension=php_
This will
change the extension to include the directory to
C:/php
and
include
the
MySQL
extension,
respectively.
You
can
use
the
Search
function
of
your
text
editor
to
check
whether
the
lines
are
already
there
and
just
need to be uncommented, or whether they
need to be added completely.
You
’
ll need to
restart Apache, and then MySQL support will be
enabled.
Selecting the
Database
Now that
you
’
re
connected,
the
next
step is to select
which database to
use with
the mysql_select_db command. It takes two
parameters: the
database name and,
optionally, the database connection. If you
don
’
t
specify the
database connection, the default is the connection
from the
last mysql_connect:
// Select the database
$$db_s
elect=mysql_select_db($$db_database);
if
(!$$db_select)
{
die
(
}
Again,
it
’
s
good
practice
to
check
for
an
error
and
display
it
every
time
you access the database.
Now
that
you
’
ve
got
a
good
database
connection,
you
’
re
ready
to
execute
your SQL query.
Building the SQL SELECT
Query
Building a SQL query
is as easy as setting a variable to the string
that
is your SQL query. Of course,
you
’
ll need to use a valid
SQL query, or
MySQL
returns
with
an
error
when
you
execute
the
query.
The
variable
name
$$query is used since the name reflects
its purpose, but you can choose
anything you
’
d
like for a variable name. The SQL query in this
example
is SELECT * FROM books.
You can build
up your query in parts using the string
concatenate (.)
operator:
Executing the
Query
To
have
the
database execute
the
query,
use
the
mysql_query
function.
It
takes two
parameters
—
the query and,
optionally, the database
link
—
and
returns
the result. Save a link to the results in a
variable called, you
guessed it,
$$result! This is also a good place to check the
return code
from
mysql_query
to
make
sure
that
there
were
no
errors
in
the
query
string
or the database connection by verifying
that $$result is not FALSE:
When
the
database
executes
the
query,
all
of
the
results
forma
result
set.
These
results
correspond
to
the
rows
that
you
saw
upon
doing
a
query
using
the
mysql
command-line
client.
To
display
them,
you
process
each
row,
one
at a time.
Fetching and Displaying
Use mysql_fetch_row to get the rows
from the result set. Its syntax is:
array mysql_fetch_row ( resource
$$result);
It takes the result you
stored in $$result fromthe query as a parameter.
It
returns
one
row
at
a
time
from
the
query
until
there
are
no
more
rows,
and then it returns
FALSE. Therefore, you do a loop on the result of
mysql_fetch_row and define some code to
display each row:
The
columns
of
the
result
row
are
stored
in
the
array
and
can
be
accessed
one
at
a
time.
The
variable
$$result_row[2]
accesses
the
second
attribute
(as
defined
in
the
query
’
s
column
order
or
the
column
order
of
the
table
if SELECT * is used) in the result row.
Fetch types
This
is not the only way to fetch the results. Using
mysql_fetch_array,
PHP can place the
results into an array in one step. It takes a
result
as its first parameter, and the
way to bind the results as an optional
second parameter. If MYSQL_ASSOC is
specified, the results are indexed
in
an array based on their column names in the query.
If MYSQL_NUM is
specified, then the
number starting at zero accesses the results. The
default value, MYSQL_BOTH, returns a
result array with both types. The
mysql_fetch_
assoc is an
alternative to supplying the MYSQL_ASSOC argument.
Closing the
Connection
As a rule of
thumb, you always want to close a connection to a
database
when
you
’
redone
using
it.
Closing
a
database
with
mysql_close
will
tell
PHP and MySQL that you
no longer will be using the connection, and will
free any resources and memory allocated
to it:
mysql_close($$connection)
Installing
PEAR
uses
a
Package
Manager
that
oversees
which
PEAR
features
you
install.
Whether
you
need
to
install
the
Package
Manager
depends
on
which
version
of
PHP
you
installed.
If
you
’
re
running
PHP
4.3.0
or
newer,
it
’
s
already
installed. If
you
’
rerunning PHP 5.0, PEAR
has been split out into a
separate
package. The DB package that
you
’
re interested in is
optional
but installed by default with
the Package Manager. So if you have the
Package Manager,
you
’
re all set.
Unix
You can
install the Package Manager on a Unix systemby
executing the
following
from
the shell (command-line) prompt:
lynx
-source / | php
This
takes
the
output
of
the
site
(which
is
actually
the
source
PHP code) to install
PEAR and passes it along to the php command for
execution.
Windows
The PHP 5
installation includes the PEAR installation script
as
C:
.
In
case
you
didn
’
t
install
all
the
files
in
Chapter
2,
go
ahead
and
extract
all
the
PHP
files
to
C:/php
from
the
command
prompt,
and execute the
.bat
file.
Creating a connect instance
The
file defines
a class of type DB. Refer to Chapter 5 for more
information on working with classes and
objects. We
’
ll principally
be
calling
the
methods
in
the
class.
The
DB
class
has
a
connect
method,
which
we
’
ll
use
instead
of
our
old
connect
function,
mysql_connect.
The
double
colons
(::)
indicate that
we
’
re
calling
that
function
from
the
class
in
line 4:
$$connection =
DB::connect(
);
When
you
call
the
connect
function,
it
creates
a
new
database
connection
that
is
stored
in
the
variable
$$connection.
The
connect
function
attempts
to connect to the
database based on the connect string you passed to
it.
Connect string
The
connect
string
uses
this
new
format
to
represent
the
login
information
that you already supplied in separate
fields:
dbtype://username:password@host
/database
This
format
may
look
familiar
to
you,
as
it
’
s
very
similar
to
the
connect
string
for
a
Windows
file
share.
The
first
part
of
the
string
is
what
really
sets the PEAR
functions apart fromthe plain PHP. The phptype
field
specifies the type of database to
connect. Supported databases include
ibase, msql, mssql, mysql, oci8, odbc,
pgsql, and sybase. All
that
’
s
required
for your PHP page to work with a different type of
database is
changing the phptype!
The username,
password, host, and database should be familiar
from the
basic
PHP
connect.
Only
the
type
of
connection
is
required.
However,
you
’
ll usually
want to specify all fields.
After
the
values
from
db_
are
included,
the
connect
string
looks
like the following:
If the connect method on
line 6 was successful, a DB object is created.
It contains the methods to access the
database as well as all of the
information about the state of that
database connection.
Querying
One of
the methods it contains is called query. The query
method works
just like
PHP
’
s query function in that
it takes a SQL statement. The
difference
is
that
the
arrow
syntax
(->)
is
used
to
call
it
fromthe
object.
It also returns the
results as another object instead of a result set:
$$query =
$$result =
$$connection->query($$query);
Based on
the SQL query, this code calls the query function
fromthe
connection
object
and returns a result object named $$result.
Fetching
Line 22
uses the result object to call the fetchRow
method. It returns
the rows one at a
time, similar to mysql_fetch_row:
while
($$result_row = $$result->fetchRow( )) {
echo 'Title: '.$$result_row[1] . '
';
echo 'Author: '.$$result_row[4] .
'
';
echo 'Pages:
'.$$result_row[2] . '
';
}
Use another while loop to go through
each row from fetchRow until it
returns
FALSE. The code in the loop
hasn
’
t changed from the non-
PEAR
example.
Closing
You
’
re finished
with the database connection, so close it using
the
object method disconnect:
$$connection->disconnect( );
PEAR error reporting
The
function
DB::isError
will
check
to
see
whether
the
result
that
’
s
been
returned
to
you
is
an
error.
If
it
is
an
error,
you
can
use
DB::errorMessage
to return a
text description of the error that was generated.
You need
to pass DB::errorMessage, the
return value from your function, as an
argument.
Here you rewrite the PEAR code to use
error checking:
if (
DB::isError( $$demoResult = $$db->query( $$sql)))
{
echo
DB::errorMessage($$demoResult);
} else
{
while ($$demoRow =
$$demoResult->fetchRow( ))
{
echo $$demoRow[2] . '
';
}
}
?>
There
’
s also a
new version of the PEAR database interface called
PEAR::MDB2.
The
same
results
display,
but
there
are
more
functions
available
in
this
version
of the PEAR database abstraction layer.
Now that you
have a good handle on connecting to the database
and the
various functions of
PEAR
。
译文：
通
过
PHP
访问
MySQL