Critical Skill 7.2 Separate Database Functions from Application Code

In web-based projects, it is not unusual to work in mixed environments, where the database, web server, or operating system may vary. Sometimes, you find yourself migrating to different technologies after an application has been developed. Separation between application code and database-specific functions makes these issues more manageable. This separation is sometimes referred to as abstraction . ODBC is an example of database abstraction on a system level. It is definitely acceptable to set up MyODBC and connect using PHP s ODBC functions to your MySQL database. However, using MyODBC instead of the native MySQL client libraries can result in severe performance degradation ”as much as 30 percent. Fortunately, there is another option for database abstraction.

Implementing application-level database abstraction takes advantage of native client libraries and provides a unified interface to the database functions. This skill will look at the PEAR::DB abstraction module, which supports ODBC databases using the ODBC API.

Installing PEAR::DB

First, you need to install the base PEAR libraries. PEAR, which stands for PHP Extension and Application Repository, may already be installed, as it is a common option when building PHP. Therefore, you should check to see if you have PEAR before running the script to install the manager.

In Linux, you can run this pithy command:

 lynx -source http://go-pear.org/  php 

It basically executes the contents found on the go-pear.com web site.

In Windows, or if you do not have lynx , open go-pear.org in a web browser and save the contents as a file (for example, in a file named go-pear.php). Then execute the file from the PHP command line:

 php go-pear.php 

It will run through an installation process on the command line. The DB module should be installed.

At any time, you can use the manager to download or update PEAR packages using the pear script, as follows :

 pear install <  package_name  > 

Connecting to a Database

The PEAR::DB modules are accessed using object-oriented techniques. For example, once a query is issued, the result is an object. To retrieve data within the object, you call methods instead of passing a pointer to a function. It is also common to see a reference instead of an assignment operator for objects. This reference is an equal sign followed by an ampersand ( = &).

Because PEAR::DB abstracts connections over a number of different database flavors, the connection string has been generalized. This string is referred to as the database source name (DSN). It follows a HTTP-looking format:

 database_type:://user:pass@host:port/database 

For example, the DSN for the MySQL duckwear database would look like this:

 mysql://root:pa55w0rd@localhost/duckwear 

To connect, you issue the connect() function with your DSN as the parameter:

  object  = DB::connect(  dsn  , [  options  ]) 

The dsn parameter can be either a well- formed DSN string or an array of connection settings. options are a named array of options: 'autofree' Boolean for freeing the results after there are no more rows or 'persistent' Boolean to use a persistent connection.

The result of the connect() function is a database object. Here is an example:

 <?php require_once 'DB.php'; $dsn = 'mysql://root@localhost/duckwear'; $dbo =& DB::connect($dsn); if (DB::isError($dbo))    {    die($dbo->getMessage());    } $dbo->disconnect(); ?> 

When you re using the DB library, the error handling is similar to when you re using the native PHP functions. First, check the result object or link to see if it is in an error state. If it is, process the error, and die or exit .

Executing Queries

In many languages, when communicating with a database, SQL statements are sometimes prepared and then executed. This concept exists in the PEAR::DB. The prepare() and execute() methods are usually called together. This offers the most flexibility, as a prepared statement can be executed any number of times and with different binding parameters.

The prepare() method has the following syntax:

  resource  = $result->prepare(  sql  ) 

sql is a string that contains the SQL statement to prepare. The following two common binding parameters are acceptable:

?	Values will be quoted before used.
!	Values will not be quoted and will be used as entered.

The execute() method has the following syntax:

  object  = $result->execute(  resource  , [  data  ]) 

The resource parameter is an object that is a resource from a prepared statement. The data parameter is an array containing the bound data for substitution.

The following is an example of using the two methods to insert binding data. It leaves out the error checking in order to focus more clearly on the commands.

 <?php    require_once 'DB.php';    $dsn = 'mysql://root@localhost/duckwear';    $dbo =& DB::connect($dsn);    $sql = 'INSERT into duck_cust (cust_first, cust_last)       VALUES (?, ?)';    $sth =& $dbo->prepare($sql);    $result = $dbo->execute($sth, array('Al', 'Lange'));    $result = $dbo->execute($sth, array('Kyle', 'Sexton'));    $result = $dbo->execute($sth, array('Terry', 'Crane')); ?> 

This is a fast technique for inserting or updating multiple rows of information at once. However, if you do not need this flexibility, you can save some coding time and use the query() method. This basically combines the prepare() and execute() into one call. Issuing a simple query is similar to using the native PHP mysql_query() function. In this case, any binding parameter may be passed in as a second optional argument. In the background, it runs a prepare() and an execute() in one step.

The query() method has the following syntax:

  object  = $result->query(  sql  , [  mode  ]) 

sql is a string that contains the SQL statement to issue. mode is one of the available fetching modes: DB_FETCHMODE_ORDERED , DB_FETCHMODE_ASSOC , or DB_FETCHMODE_ OBJECT . If a mode is not supplied, query() will use the default mode: DB_FETCHMODE_ ORDERED .

Here is an example of using the query() method:

 <?php    require_once 'DB.php';    $dsn = "mysql://root@localhost/duckwear";    $dbo =& DB::connect($dsn);    if (DB::isError($dbo))       {       die($dbo->getMessage());       }    $result =& $dbo->query("SELECT * FROM duck_cust");    if (DB::isError($result))       {       die($result->getMessage());       }    $dbo->disconnect(); ?> 

Fetching Results

Two ways to deal with the results from a query (or prepare/execute) are the fetchInto() and fetchRow() methods. The fetchRow() method is similar to the mysql_fetch_ array() function. It takes an optional parameter that sets the result mode, using the following syntax:

  array  = $result->fetchRow([  mode  ]) 

The mode parameter is one of the available fetching modes: DB_FETCHMODE_ORDERED , DB_FETCHMODE_ASSOC , or DB_FETCHMODE_OBJECT . If a mode is not supplied, fetchRow() will use the default mode: DB_FETCHMODE_ORDERED , which is similar to the MYSQL_NUM mode you saw previously in the mysql_fetch_array() function.

The fetchInto() method allows you to fill a target variable with the row of data. It has the following syntax:

  integer  = $result->fetchInto(  target  , [  mode  ], [  row  ]) 

The target parameter is a reference to the target variable. The mode parameter is one of the available fetching modes, with the same options and default as fetchRow() . The row parameter is an integer data type that specifies to jump to a specific row.

The following is an example of using fetching results:

 <?php    require_once 'DB.php';    $dsn = 'mysql://root@localhost/duckwear';    $dbo =& DB::connect($dsn);    if (DB::isError($dbo))       {       die($dbo->getMessage());       }    $sql = 'SELECT cust_last, cust_first, cust_city,       cust_state FROM duck_cust';    echo "\n\nFetching all the rows using fetchRow():\n"; $result =& $dbo->query($sql);    if (DB::isError($result))       {       die($result->getMessage());       }    while ($row = $result->fetchRow(DB_FETCHMODE_ORDERED))       {       echo "\t".$row[0];       echo "\t".$row[1];       echo "\t".$row[2];       echo "\t".$row[3];       echo "\n";       } // Free the result (the 'autofree' defaults to false)    $result->free();    echo "\n\nFetching all the rows using fetchInto():\n";    $result =& $dbo->query($sql);    if (DB::isError($result))       {       die($result->getMessage());       }    while ($result->fetchInto($row, DB_FETCHMODE_ASSOC))       {       echo "\t".$row['cust_last'];       echo "\t".$row['cust_first'];       echo "\t".$row['cust_city'];       echo "\t".$row['cust_state'];       echo "\n";       }    $dbo->disconnect(); ?> 

In this example, both methods display the entire database on the web page. However, fetchInto() allows you to specify a particular row to jump to, instead of taking the next row in line.

There are many shortcuts in the PEAR::DB library to make your life even easier. You should familiarize yourself with the library and consider using it in your applications.

This skill and the previous one only touched on some of the many methods you can use with PHP and MySQL. These examples gave you a taste of what MySQL and PHP can jointly accomplish and introduced some useful libraries. Now you have a basic foundation to further explore the possibilities of the PHP scripting language.