PHP, MySQL and the PEAR Database

In this conclusion to a three-part series, you will add PEAR into the mix of what you have already learned. This article is excerpted from chapter 9 of Learning PHP and MySQL, written by Michele Davis and Jon Phillips (O’Reilly, 2006; ISBN: 0596101104). Copyright © 2006 O’Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O’Reilly Media.

Hosted ISP

Most ISPs have PEAR DB installed. Ask your ISP to install it if they haven’t already. You can tell if PEAR DB has been installed by trying the PHP code in Example 9-7 to see whether the require_once (‘DB.php’); line causes an error when the script is executed.

Adding Additional Packages

Once that’s complete, you can access the PEAR Package Manger by entering pear at the command prompt. Adding new modules is as easy as executing pear packagename . You won’t need to do anything, since the DB package was installed along with the install by default.

However, if you’re running Windows XP Home, you’ll need to take these steps to install the PEAR DB:

  C:>cd c:php
  C:>pear install DB
  C:>pear list

To find out what versions of PEAR packages are installed, execute pear list. That returns a listing such as the one shown in Figure 9-6. 

Figure 9-6.  A listing of installed PEAR packages and versions

Once you’ve got PEAR installed, you’re ready to try it out.

{mospagebreak title=Rewriting the Books Example with PEAR}

When using the PEAR DB package, you follow the same steps. However, the function syntax is slightly different. We’ll go line by line and explain the differences as they appear in Example 9-7.

Example 9-7.  Displaying the books table with PEAR DB

1 <?php
3 include(‘db_login.php’);
4 require_once(‘DB.php’);
6 $connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
8 if (DB::isError($connection)){
9 die("Could not connect to the database:
<br />".DB::errorMessage($connection)); 10 }
12 $query = "SELECT * FROM `books` NATURAL JOIN `authors`";
13 $result = $connection->query($query);
15 if (DB::isError($result)){
16 die("Could not query the database:
<br />".$query." ".DB::errorMessage($result));
17 }
19 echo(‘<table border="1">’);
20 echo ‘<tr><th>Title</th><th>Author</th><th>Pages </th></tr>’;
22 while ($result_row = $result->fetchRow()) {
23 echo "<tr><td>";
24 echo $result_row[1] . ‘</td><td>’;
25 echo $result_row[4] . ‘</td><td>’;
26 echo $result_row[2] . ‘</td></tr>’;
27 }
29 echo("</table>");
30 $connection->disconnect();
32 ?>

Example 9-7 displays the screen shown in Figure 9-7.

Figure 9-7.  Switching to the PEAR DB functions didn’t change the output

Notice that Figure 9-7 is identical to the output in Figure 9-4 .

Line 3 includes your database login information and remains unchanged:


Line 4 has a new require statement:

  require_once( "DB.php" );

This requires the file DB.php, which provides the PEAR DB functions. The require_once function errors out if the DB.php file is not found. It also will not include the file if it has been incorporated already. And, this would cause an error.

The file DB.php is found in the /pear subdirectory of the PHP distribution. The PEAR install should have added that directory to the include_path in the php.ini file. If this file is not found, verify that PEAR DB is installed and that the paths are set up correctly.

{mospagebreak title=Creating a connect instance}

The DB.php 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("mysql://$db_username:$db_password@$db_host/$db_database");

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:


This format may look familiar to you, as it’s very similar to the connect string for a Windows share. The first part of the string is what really sets the PEAR functions apart from the 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 ,  passwordhost, 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 dblogin.php 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.


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 the hyphen and greater-than syntax (->) is used to call it from the object. It also returns the results as another object instead of a result set.

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

Based on the SQL query, this code calls the query function from the connection object and returns a result object named $result.


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] . ‘<br />’;
      echo ‘Author: ‘.$result_row[4] . ‘<br />’;
      echo ‘Pages: ‘.$result_row[2] . ‘<br /><br />’;

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


In line 30, you’re finished with the database connection, so you close it using the object method disconnect:


{mospagebreak title=PEAR error reporting}

The function DB::isError will check to see whether the result that’s been returned to you is an error or not. 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] . ‘<br />’;

Now that you have a good handle on connecting to the database and the various functions of PEAR, we’re going to talk about forms. Forms provide a way to send substantial data from the user to the server where it can be processed.

Chapter 9 Questions

Question 9-1.  Create a PEAR-style connect string to connect to this database:


database name: survey

username: joe

password: my$ql

Question 9-2.  Using the parameters in Question 9-1, write the non-PEAR PHP code to connect to a database and select the instance.

Question 9-3.  Using the connection from Question 9-2, write the non-PEAR PHP code to fetch and display the results of the query select * from authors;.

Question 9-4.  What are the advantages of using PEAR?

See the Appendix for the answers to these questions.

Google+ Comments

Google+ Comments