Web Database Applications With Php And Mysql (2nd Edition) [Electronic resources] نسخه متنی

7.2 Core Components

PEAR's core
components are general-purpose, reliable packages that work with most
web servers, database servers, browsers, and operating systems. If
you're using PHP 4.3 or later on a Unix system, the
PEAR core components and the PEAR installer for adding other packages
are already installed and ready for use. For Microsoft Windows, the
integration occurred in PHP 4.3.2.

The list of core components can change but at the time of writing it
includes:

PEAR base and error handling classes

These are the foundations of other PEAR packages, and you
don't need a detailed understanding of them unless
you plan to develop your own package. We discuss error handling in
our introduction to PEAR DB in the next section.

PEAR Console command-line parsing

Used for non-web scripts.

PEAR DB

Database server abstraction. Discussed in detail in the next section.

HTTP methods

Used to format HTTP-compliant dates, negotiate language, and compress
data for fast transfer.

PEAR Mail

Used for mail sending, including platform independence, MIME
attachments, and correct email address validation.

PEAR System

Platform-independent commands for making and removing directories and
files, concatenating files, and finding the full path of a program.

7.2.1 What's Installed?

Now, let's check the
core components distributed with your PHP installation.

7.2.1.1 Unix systemsPHP 4.3.0 and later

For the instructions in this
section to work, you must have followed our installation instructions
in Appendix A to Appendix C. You also need an active Internet connection.

To check the list of components installed, you need to login as the
root user; to do this, type su at a shell
prompt and provide the root user password.

If you're working with
Mac OS X, type at a shell prompt:

% cd /usr/local/bin

Then, on all systems, type the following at a shell prompt:

% pear list

You'll see a list in the following format:

Installed packages:
===================
+------------------+---------+--------+
| Package          | Version | State  |
| Archive_Tar      | 0.9     | stable |
| Console_Getopt   | 1.0     | stable |
| DB               | 1.3     | stable |
| HTTP             | 1.2     | stable |
| Mail             | 1.0.1   | stable |
| Net_SMTP         | 1.0     | stable |
| Net_Socket       | 1.0.1   | stable |
| PEAR             | 1.0b3   | stable |
| XML_Parser       | 1.0     | stable |
| XML_RPC          | 1.0.4   | stable |

You may find that the versions you have are different. This
isn't a problem.

7.2.1.2 Microsoft WindowsPHP 4.3.2 and later

For


the instructions in this section to
work, you must have followed our installation instructions in
Appendix A to Appendix C. You also need an active Internet connection.

Start by launching a command window. You can do this by running the
file command.com or running a DOS prompt window
(if it's listed in your Accessories group under
Programs in your Start Menu).

To run command.com, click on the Start Menu,
then the Run option. Now, type command.com and
press Enter.

In your command window, change directory to the pear install
directory. If you've followed our install
instructions in Appendix A to Appendix C, type:

C:\> cd c:\Progra~1\EasyPH~1\php\pear

Then, type the following:

C:\> pear.bat list

A list of installed packages is shown in the following format:

INSTALLED PACKAGES:
===================
PACKAGE           VERSION  STATE
Archive_Tar       0.9      stable
Console_Getopt    1.0      stable
DB                1.3      stable
HTTP              1.2      stable
Mail              1.0.1    stable
Net_SMTP          1.0      stable
Net_Socket        1.0.1    stable
PEAR              1.0b3    stable
XML_Parser        1.0      stable
XML_RPC           1.0.4    stable

You may find that the versions you have are different. This
isn't a problem.

If you want to close the command window, type
exit. However, you'll need
this window later in this chapter, so keeping it open is fine.

7.2.2 Using PEAR DB

In most PHP applications, one of the
server-specific database libraries is used to access the database
server. In Chapter 6, we showed you how to
access the MySQL sever using the MySQL library functions. In this
section, we show you how to develop reasonably server-independent
scripts using PEAR's DB component. We also use the
PEAR DB class throughout our online winestore in Chapter 16 through Chapter 20.

7.2.2.1 Should I use PEAR DB?

If you want server-independent
function calls, PEAR's DB component is ideal because
the code usually doesn't change when you change the
underlying database server. However, there are sometimes needs for
small changes, such as catering for different function return values
or rewriting code because a database server doesn't
support a feature. For example, only some of the underlying servers
support the tableInfo( ) method for returning
metadata about table attributes.

If you don't use PEAR DB, changing database servers
can be time-consuming. If you switch between similar
librariessuch as the MySQL and PostgreSQL librariesthen
updating the code usually doesn't require too much
work: it's largely a case of changing the
mysql_ prefix to a pgsql_
prefix, and perhaps tackling complex querying in a different way.
However, if you change to a less-similar librarysuch as one of
the Oracle libraries or ODBCthen more work is required even
for the simple tasks.

PEAR DB will almost give you function library independence, but it
won't give you complete database server
independence. SQL isn't the same between any two
servers: as we discussed in Chapter 1,
combinations of the features of SQL-89, SQL-92, and SQL-99 are often
implemented, and many servers have proprietary statements for tasks.
For example, MySQL supports entry-level SQL-92, but uses proprietary
clauses such as LIMIT and
AUTO_INCREMENT, and attribute types such as
LONGINT and TIMESTAMP. Even if
you use PEAR DB, it's almost impossible (and
probably not sensible) to avoid using proprietary SQL.

For many developers, it isn't clear whether database
abstraction offers an advantage: many developers
don't bother writing server-independent code because
their SQL is tied to the database server. In fact, if
you're sure you'll be using one
database server for the lifetime of an application, we recommend
using the proprietary library so that you can take advantage of the
specialized functions designed for the database server.

In most of the applications we've developed,
we've used the MySQL library functions outlined in
Chapter 6. However, to illustrate how to use
PEAR DB and to give you code that will work with minimal modification
for other database servers, we've used it to develop
our online winestore in Chapter 16 through
Chapter 20.

7.2.2.2 Getting started

In this section, we assume
you've read Chapter 6 and are
familiar with the basic querying processes and the core MySQL library
functions. Also, you'll need to be familiar with the
basic object-oriented PHP features discussed in Chapter 4.

Example 7-1
shows how to connect, query, and
retrieve results using PEAR DB. The example is an extended version of
Example 6-1 that includes error handling.

Example 7-1. Using PEAR DB to query the winestore database

<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html401/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Wines</title>
</head>
<body><pre>
<?php
require_once "DB.php";
require "db.inc";
$dsn = "mysql://{$username}:{$password}@{$hostName}/{$databaseName}";
// Open a connection to the DBMS
$connection = DB::connect($dsn);
if (DB::isError($connection))
die($connection->getMessage( ));
// (Run the query on the winestore through the connection
$result = $connection->query("SELECT * FROM wine");
if (DB::isError($result))
die ($result->getMessage( ));
// While there are still rows in the result set, fetch the current 
// row into the array $row
while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC))
{
// Print out each element in $row, that is, print the values of 
// the attributes
foreach ($row as $attribute)
print "{$attribute} ";
print "\n";
}
?>
</pre>
</body>
</html>

As discussed previously, there's no need to download
or install any extra components to use the PEAR core components. The
PEAR DB class is used within a script by requiring it:

require_once "DB.php";

If you find that your PHP engine can't find
DB.php,
it's likely that your
include_path directive in your
php.ini configuration file
doesn't include the PEAR directory. Check the
installation instructions for your platform in Appendix A to Appendix C.

Connecting to a database server uses a URL-style string. In the
example, this string consists of the familiar
$username, $password,
$hostName, and $databaseName
from the db.inc require file:

$dsn = "mysql://{$username}:{$password}@{$hostName}/{$databaseName}";

For the defaults in the db.inc file, this gives
the string:

mysql://fred:shhh@localhost/winestore

We store the string in a variable with the acronym
$dsn to signify this is the data source
name
. The prefix mysql://
indicates the MySQL server, and the string
fred:shhh@localhost specifies the username,
password, and host parameters that are used with the
mysql_connect( ) and mysql_pconnect(
) functions. Rather than use the separate
mysql_select_db( ) function to use the database,
it's specified following a forward slash character.

The connection itself is established with the method
DB::connect( )
:

$connection = DB::connect($dsn);

The notation DB:: means that the method
connect( ) is a member of the class DB. Error
handling is discussed in the next section.

The DB::query( )
method works similarly to
mysql_query( ), taking the SQL query as a
parameter and returning a result resource that can be used to
retrieve data from a SELECT query:

$result = $connection->query("SELECT * FROM wine");

The result rows are retrieved using the DB::fetchRow(
)
method:

while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC))

The method behaves similarly to mysql_fetch_array(
). The parameter DB_FETCHMODE_ASSOC
specifies that the return array has associatively-accessible elements
that are named with the database attribute names or attribute
aliases; however, this isn't important in this
example because we use foreach to iteratively
process all elements of the array.

7.2.2.3 Handling errors in PEAR DB

The error status of any database server method can be tested using
DB::isError( )

. Unlike in the MySQL library, this
method can be used regardless of whether a connection has been
established yet or not. If an error occurs, the getMessage(
) method can be used to retrieve a descriptive string as
in the following example:

// Open a connection to the DBMS
$connection = DB::connect($dsn);
if (DB::isError($connection))
die($connection->getMessage( ));

The getMessage( ) method is part of the core
PEAR error class. The method works similarly for testing errors from
queries:

$result = $connection->query("SELECT * FROM wine");
if (DB::isError($result))
die ($result->getMessage( ));

Note that the method can be used with many types of objects: for
connections, we use the $connection object and for
results we use $result.

If a method or parameter is unsupported by the underlying database
server, you'll find that the following error is
reported:

DB_error: database not capable

7.2.2.4 Essential functions for accessing MySQL with PEAR DB

Methods for interacting with database
servers using PEAR DB are the subject of this section.
We've included the essential methods, and omitted
those that are less-frequently used, redundant, or
aren't used with MySQL. More detail on all methods
can be found in the PEAR manual at http://pear.php.net/manual/en/core.db.php.

For most PEAR DB methods, we've noted which native
MySQL functions are used in the library to implement the
functionality that's described. Chapter 6 presents the detail of the underlying MySQL
functions, and you'll find that the limitations and
advantages of those functions affects PEAR DB too. We recommend
reading the MySQL function notes in conjunction with the PEAR DB
descriptions.

mixed DB::affectedRows( )

Returns the number of rows that were affected by the previous
database-modifying query. Returns a DB_ERROR
object on failure. (The return type mixed
indicates that the type of the value returned by the method
isn't always one type.)

For MySQL, the underlying function is mysql_affected_rows(
). However, unlike mysql_affected_rows(
) a workaround ensures affectedRows(
) provides the correct value when all rows are deleted
from a table. DB_Result::numRows( ) should be
used for queries that do not modify the database.

This method, and database modifications in general, are discussed in
Chapter 8.

mixed DB::connect(string
dsn [, bool persistent])

Connect to a DBMS using the parameters specified in the data source
name dsn. If
persistent is true and
the DBMS supports persistent connections, a persistent connection is
used, otherwise the default of false returns a
non-persistent connection. The function returns a database connection
object on success or a DB_error object on failure.

The data source name
dsn is specified in the following format
or one of its simplifications:

dbms://username:password@protocol+host:port/database

where the following is applicable:

dbms

The type of DBMS to connect to. The options that are supported in
release 1.17 are dbase (dBase file support),
fbase (FrontBase), ibase
(InterBase), ifx (Informix),
mssql (Microsoft SQL server),
msql (mSQL), mysql (MySQL 3.x),
mysql4 (MySQL 4.x), oci8
(Oracle OCI8), odbc (ODBC),
pgsql (PostgreSQL), and sybase
(Sybase).

username

The username to connect with.

password

The password associated with the username.

protocol

Communication protocol such as tcp or
unix. Often omitted.

host

The hostname of the DBMS server. Often localhost
for the local machine.

port

The port to connect to on the host.

database

The name of the database to use on the connection.

Several simplifications of the dsn are
possible and are described in the manual. By far the most common
format uses the default protocol and port:

dbms://username:password@host/database

For MySQL, mysql_connect( ) is used if the
second parameter is false or omitted, and
mysql_pconnect( ) is used when
it's true.

mixed DB::createSequence(string
name)

Creates a new sequence name. Returns the
result of the query that creates the sequence, or a
DB_ERROR object on failure. See
DB::nextId( ) for an introduction to sequences.
An example of using sequences in presented in Chapter 8.

mixed DB::dropSequence(string
name)

Deletes a sequence name. Returns the
result of the query that deletes the sequence, or a
DB_ERROR object on failure. See
DB::nextId( ) for an introduction to sequences.
An example of using sequences in presented in Chapter 8.

mixed DB_Result::fetchRow([int
mode [, int row]])

Retrieve a row of results using an optional
mode and an optional
row. By default, the rows are returned
into a numerically-accessed array. The function returns the row on
success and NULL when there is no more data to
fetch. On error, it returns a DB_ERROR object.

The mode can be one of
DB_FETCHMODE_ORDERED (a numerically accessed
array, which is the default when no parameter is supplied),
DB_FETCHMODE_ASSOC (an associatively accessed
array), or DB_FETCHMODE_OBJECT (an object with
attribute names as properties). An optional
row to retrieve can be specified after the
mode.

For MySQL, the current release uses mysql_fetch_array(
) to provide the functionality of
DB_FETCHMODE_ASSOC and
DB_FETCHMODE_OBJECT, and its numeric-only sibling
mysql_fetch_row( ) for numeric access. The
function mysql_data_seek( ) is used to retrieve
specific rows.

bool DB::isError(DB_error
object)

Reports true when the parameter
object is of type
DB_error, and false otherwise.
It is often used with the return values of DB::connect(
) and DB::query( ) as the parameter.
The error is usually output using getMessage( )
as shown in the previous section.

mixed DB::nextId(string
name [, bool create])

Returns the next unique identifier value associated with the string
name or a DB_ERROR
object on failure. The identifier that is returned is usually used as
input into an INSERT statement to create a new row
with a unique primary key value. If the sequence
name does not exist, it is automatically
created if create is set to
true (which is the default). Sequences can be
manually created with DB::createSequence( ) and
deleted with DB::dropSequence( ).

In the PHP MySQL library, the mysql_insert_id( )
function returns the unique value associated with an
INSERT operation after the operation has occurred.
In contrast, the DB::nextId( ) method reports a
table-independent value prior to the INSERT
operation occurring. Database modifications are discussed further in
Chapter 8, and an example of using
DB::nextId( ) is presented there.

int DB_Result::numRows( )

Returns the number of rows associated with a query result object, or
a DB_ERROR object on failure.
DB::affectedRows( ) should be used for queries
that modify the database.

In MySQL, the function mysql_num_rows( )
provides the underlying functionality.

mixed DB::query (string
query [, array parameters])

Executes an SQL query. An optional array
of parameters can be provided to prepare a
query; we discuss query preparation in Appendix F.

For MySQL, the function returns a MySQL result resource for
SELECT queries on success, the constant
DB_OK for other successful queries, and a
DB_ERROR object on failure.

string DB::quote(string
query)

Escapes a query string so that it can be
used a parameter to DB::query( ). It returns a
copy of the input string that has any special characters escaped. For
a MySQL connection, the function uses
mysql_real_escape_string( ) in PHP 4.3 or later,
and mysql_escape_string( ) otherwise.

mixed DB_Result::tableInfo(DB_Result
result [, int mode])

Returns an array of metadata about the attributes of the
result set using an optional
mode. Returns a
DB_ERROR object on failure. The function works for
MySQL, MS-SQL, FrontBase, and PostgreSQL.

With no second parameter, the array that is returned is
two-dimensional. The first dimension is the attribute number, and the
second has the following associative keys:

name

The name of the attribute.

type

The attribute type.

len

The attribute maximum length.

flags

A string containing a list of attribute flags. For example, in MySQL
the flags can include not_null,
primary_key, auto_increment,
and timestamp.

table

The name of the table associated with the attribute.

You can pass a second parameter,
DB_TABLEINFO_ORDER, which makes one additional
element available. This element can be retrieved through the
associative key order. Its second dimension is
filled with the names of the attributes and the values are set to the
attribute numbers. This allows you to determine the attribute number
using the attribute name, so that the metadata can be accessed in two
steps by attribute name. For example, to access the attribute length
metadata for the attribute wine_name:

$array = $result->tableInfo($result, DB_TABLEINFO_ORDER);
// What's the attribute number of wine_name?
$number = $array["order"]["wine_name"];
// Print out the length of the wine_name
print "Attribute length: {$array[$number]["len"]}";

Another second parameter is available, but it is unnecessary if you
use attribute aliases in your queries to avoid duplicate attribute
names as discussed in Chapter 6.

The function is similar in concept to mysql_fetch_field(
) but it returns an array instead of an object. It is
implemented for MySQL using the non-object based siblings of
mysql_fetch_field( ) (which are listed in Chapter 6 as functions we don't
recommend you use).