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).