Multiple Database Interaction in PHP 4.1

This chapter is from the book

Advanced PHP for Web Professionals

Learn More Buy

This chapter is from the book

This chapter is from the book 

Advanced PHP for Web Professionals

Learn More Buy

Using Pear::DB

Although PHP's DBX functions are useful on simple sites, there is a more feature-rich solution available for sites that require greater complexity in their database use. This solution is PEAR::DB.

PEAR—PHP Extension and Application Repository

PEAR is a "framework and distribution system for reusable PHP components."¹ PEAR provides "modules" along the lines of the PERL CPAN module set. Think of the modules as libraries of prebuilt components that you can use to make your PHP coding easier. PEAR is currently in beta and is under heavy development, but it already contains many useful modules that you can use in your own projects. PEAR provides open-source modules, distribution and maintenance for those modules, and a coding standard for said modules.

PEAR is distributed with PHP. If you've installed PHP recently, you may have noticed the "pear" directory and the many files that it contains. Since PEAR is still in beta, it hasn't been widely advertised, but it has begun to get a lot of attention from PHP developers, especially those who have been using third-party libraries, such as PHPLIB and ADOdb. Although there are good third-party libraries available, there is not yet a standard as far as coding guidelines, distribution, and maintenance. PEAR looks to be the solution to this problem.

One of the modules that is starting to gain a lot of popularity is PEAR DB "a unified API for accessing SQL-databases." PEAR DB greatly exceeds the functionality of the DBX library and is almost purely object oriented. PEAR DB supports a decent subset of the core PHP-supported databases and is being extended to support even more. PEAR DB is a great tool to code database-independent PHP applications.

Downloading PEAR

You can download recent versions of PEAR from the PEAR Web site at pear.php.net. Type "DB" into the "search for" box in the upper right corner of the site.

Click on the first search result that comes up, "DB," then download the latest stable version from the list of releases.

Downloading PEAR from Concurrent Versions System (CVS)

Although PEAR is included with all recent versions of PHP and you can download later stable versions from the PEAR Web site, the latest and greatest version of PEAR with the latest features is probably still sitting in the CVS repository. Note that you should not use CVS versions on production Web sites! If you have access to CVS (www.cvshome.org), then you should take advantage of it and download the latest PEAR source code from the PHP CVS repository. You can find out all the specifics on the PHP CVS repository at http://cvs.php.net/.

To download the latest PEAR source:

1. Change directory to the location that you want to place the source (or a convenient place from which you can copy the files).

2. Execute the command: cvs -d :pserver:cvsread@cvs .php.net:/repository login

You are prompted for a password. Enter "phpfi".

3. Execute the command: cvs -d :pserver:cvsread@cvs .php.net:/repository export -D "last week" php4/pear

Thomas V. V. Cox, one of the main contributors to the PEAR DB documentation, recommends using the "last week" flag. Sometimes the latest is a little buggy, but most bugs are fixed in a week.²

4. Copy the pear folder over your existing pear folder in your PHP installation.

Installing PEAR

Once you have downloaded the source, you should modify your include_path setting in php.ini to point to the pear directory, or you could use the following function at the top of each script that requires access to PEAR:

ini_set("include_path", "path/to/pear"); 

Once you have the latest PEAR source, you are ready to begin. If you don't have the latest, you can still try out the examples, but your mileage may vary.

Back to the Basics

Now that you are ready to code a PEAR DB application, you have to learn the basics of connecting to and querying a database once again. Like any database-backed PHP application, you first have to connect to the database. PEAR DB uses the DB::connect() function for this purpose (notice the similarity to PERL's CPAN module syntax).

$dbconn = DB::connect(DSN);

DB::connect() takes one argument, a DSN. DSN stands for data source name, and it is a way to specify all the necessary settings needed to connect to the database in one neat package. A DSN is typically comprised of the following:

• type—The type of database server to which you are connecting. See below for examples.

• host—The host name of the database server to which you are connecting, optionally followed by the port.

• database—The name of the database to which you are connecting.

• username—The login of the user with access rights to the database.

• password—The user's password.

The type of databases can be one of the following:

• mysql—MySQL
• pgsql—PostgreSQL
• ibase—InterBase
• msql—mSQL
• mssql—Microsoft SQL Server
• oci8—Oracle 7/8/8i
• odbc—ODBC
• sybase—Sybase
• ifx—Informix
• fbsql—FrontBase

All of this information is formatted in a single string:

$dsn = "type://username:password@host:port/database";

If your database doesn't require an option, such as the password, or if it is running on the standard port, then you can omit those from the DSN. If the connection is successful, then DB::connect() returns a valid connection object. If there was a failure, then it returns a DB Error object.

A PEAR DB-enabled page starts like this:

ini_set("include_path", "path/to/pear");
require_once('DB.php');
$type = "mssql";
$user = "mssqluser";
$pass = "password";
$host = "192.168.0.1";
$database = "php";
$dsn = "$type://$user:$pass@$host/$database";
$dbconn = DB::connect($dsn);

Once you have attempted to make the connection, you should verify that it worked. PEAR DB provides the DB::isError() function to check if there was an error.

DB::isError(OBJECT)

DB::isError() takes one argument, OBJECT, the object that was created by a previous PEAR DB function. In the example above, you would check for an error like this:

if (DB::isError($db)) { 

            die ($dbconn->getMessage());
}

Wait a second, it looks like something sneaked in there! $dbconn->getMessage() is a method that, when an error is produced, allows you to display any message associated with the error object. If the connection attempt doesn't work, the $dbconn turns into a DB Error object. If there is no error, then getMessage() won't work on $db, since it is a valid connection object.

Okay, so you've got a connection set up and a way to trap an error if the connection fails. The next thing you need to know how to do is send queries to your database. This is done using the query() method.

$result = $dbconn->query(QUERY);

query() takes one argument, QUERY, which is an SQL statement. You assign the result of that query to a variable, such as $result, which is a result object.

Once you have a valid result object, you can use one of the "fetch" methods to fetch the data from the result and then format and display it to the user or perform some other processing on it. There are two fetch methods that you can use: fetchRow() and fetchInto(). Both of these functions do essentially the same thing; however, their syntax is slightly different.

$row = $result->fetchRow();

and

$result->fetchInto($row);

produce exactly the same thing, a variable called $row that has the current row data from the result object.

You can specify the format of the data retrieved by the fetch methods by assigning an additional argument to the method. The fetch methods support three formats of data that they can retrieve. These are:

• DB_FETCHMODE_ORDERED—(default) Data is formatted as an ordered array.

• DB_FETCHMODE_ASSOC—Data is formatted as an associative array.

• DB_FETCHMODE_OBJECT—Data is formatted as an object, with the column names as properties.

As an example, suppose you have the following information in a table (Table 3–2) called "crew":

TABLE 3–2 Sample Data

You query the database and get a result:

$sql = "select name, origin from crew";
$result = $dbconn->query($slq);

The three formats would be:

//using the default format of DB_FETCHMODE_ORDERED
$row = $result->fetchRow();
$name = $row[0]; // $name = "Spike";
$origin = $row[1]; //$origin = "MA";

//using an associative array as the format
$row = $result->fetchRow(DB_FETCHMODE_ASSOC);
$name = $row['name']; // $name = "Spike";
$origin = $row['origin']; //$origin = "MA";

//using an object as the format
$result->fetchInto($row, DB_FETCHMODE_OBJECT);
$name = $row->name; // $name = "Spike";
$origin = $row->origin; //$origin = "MA";

These formats provide for a lot of flexibility. Also notice that the different fetch methods would produce the same expected result.

Since it can be a real drag having to type out DB_FETCHMODE_ASSOC or DB_FETCHMODE_OBJECT as an argument to every fetch method in your script (if you choose to use those formats), PEAR DB provides a nifty method called setFetchMode().

$dbconn->setFetchMode(MODE);

setFetchMode is a method for the connection object that sets the default fetch mode for all fetch functions. The argument, MODE, can be any one of the three fetch modes specified earlier. Note that it is pointless to set the fetch mode to DB_FETCHMODE_ORDERED if you haven't already set the fetch mode to one of the other values, since it is already the default. Once you use this method, all of the fetch methods that you call use the mode specified by setFetchMode(), unless you have specified a different fetch mode in the fetch method itself.

You should also check your results for errors using the same method as when checking the connection for errors. If a query() results in an error, then it returns an error object:

$result = $dbconn->query($sql);
if (DB::isError($result)) { die ($result->getMessage());} 

After you have finished with your result set, you should free the result object by using the free() method. This will "free" the memory associated with the result set and reduce the load on your Web server:

$result->free();

Finally, when you have completed your script, you should close the connection to the server. This is done using the disconnect method of the connection object:

$dbconn->disconnect();

You now have the building blocks to create a simple script. This next script illustrates the different fetch methods and errors.

Script 3–5 peardb1.php Script 3–6

  1.  <?
  2.  ini_set("include_path", "/path/to/php/pear");
  3.  require_once('DB.php');
  4.  $type = "mssql";
  5.  $user = "mssqluser";
  6.  $pass = "password";
  7.  $host = "192.168.0.1";
  8.  $database = "php";
  9.  $dsn = "$type://$user:$pass@$host/$database";
10.  $dbconn = DB::connect($dsn);
11.  if (DB::isError($dbconn)) { die ($dbconn->getMessage()); }
12.  /*
13.  create table bebop (
14.          id INT,
15.          name VARCHAR(8),
16.          origin CHAR(2));
17.  */
18.  $values = array (
19.          "insert into bebop values ('0','Spike','MA')",
20.          "insert into bebop values ('1','Jett','AZ')",
21.          "insert into bebop values ('2','Faye','FL')",
22.          "insert into bebop values ('3','Ed','NM')",
23.          "insert into bebop values ('4','Ein','CO')"
24.          );
25.
26.  $result = $dbconn->query("select * from bebop");
27.  if(DB::isError($result)) { die ($result->getMessage());}
28.  if($result->numRows() == 0) {
29.          echo "<P>Populating the DB!<p>";
30.          for($i = 0; $i < sizeof($values); $i++) {
31.              $result2 = $dbconn->query($values[$i]);
32.              if (DB::isError($result2)) { die ($result2->getMessage());}
33.          }
34.          echo "<P>Done the DB! Reload Page<p>";
35.  } else {
36.          $result = $dbconn->query("select name, origin from bebop");
37.          if (DB::isError($result)) { die ($result->getMessage());}
38.          echo "<P>Default Fetch Mode (DB_FETCHMODE_ORDERED):";
39.          while($result->fetchinto($row)){
40.              echo "<br>" . $row[0] . " " . $row[1];
41.          }
42.          $result = $dbconn->query("select name, origin from bebop");
43.          $dbconn->setFetchMode(DB_FETCHMODE_ASSOC);
44.          echo "<HR>DB_FETCHMODE_ASSOC Fetch Mode:";
45.          while($row = $result->fetchRow()){
46.              echo "<br>" . $row['name'] . " " . $row['origin'];
47.          }
48.          $result = $dbconn->query("select name, origin from bebop");
49.          $dbconn->setFetchMode(DB_FETCHMODE_OBJECT);
50.          echo "<HR>DB_FETCHMODE_OBJECT Fetch Mode:";
51.          while($result->fetchinto($row)){
52.          echo "<br>" . $row->name . " " . $row->origin;
53.          }
54.  }
55.  $result->free();
56.  $dbconn->disconnect();
57.  ?>

Script 3–5 peardb1.php Line-by-Line Explanation