php tutorials

PEAR:DB

In this lesson of the PHP tutorial, you will learn...

To use the PEAR DB package as a database abstraction layer.

PEAR (see footnote) supplies a number of open source extensions to PHP including its DB package, which provides a database abstraction layer, so that the PHP programmer doesn't have to worry about all the APIs for different databases.

Advantages and Disadvantages of PEAR DB

Whether or not you decide to use PEAR DB or a similar database abstraction layer depends on your needs. If you need to be able to work on many applications and get your work done quickly, then PEAR DB is certainly helpful. If performance is key, then you may find the extra weight of PEAR DB to be prohibitive.

Why use a database abstraction layer?

One big benefit of using a database abstraction layer like PEAR DB is portability. PEAR DB allows you to use a single API for working with many different types of databases. So if you decide to move to another database, you will not have to rewrite all your code.

Another benefit is code simplification. If your application involves multiple databases of different flavors or you work on many applications each of which uses a different type of database, you would normally have to learn the APIs for each of the databases you would be working with. Again, PEAR DB allows you to work with all these databases using the same API.

When not to use a database abstraction layer?

The biggest downside of using a database abstraction layer is that the benefits come at a performance cost. Imagine you were planning to travel around Europe and had the choice of bringing an interpreter who could speak all European languages and learning the languages yourself. It would certainly be easier to bring the interpreter, but this would make each conversation you had somewhat slower. The abstraction layer is the interpreter.

Using PEAR DB

The connection string for connecting to the database with PEAR DB is:

Syntax

driver://username:password@host/database

Some of the drivers supported by PEAR DB are

mysqli
mysql
mssql
oci8
odbc
pgsql
sybase
dbase
sqlite

Code Sample: PEAR-DB/Demos/EmployeeReport.php

<html>
<head>
<title>Employee Report</title>
</head>
<body>
<?php
require_once 'DB.php';
@$DB = DB::connect('mysqli://root:pwdpwd@localhost/Northwind');
if (DB::isError($DB))
{
 echo 'Cannot connect to database: ' . $DB->getMessage();
}
else
{
 $Query = 'SELECT * FROM Employees';
 $Result = $DB->query($Query);
 $NumResults = $Result->numRows();
 echo "<b>$NumResults Employees</b>";
?>
 <table border="1">
 <tr>
  <th>First Name</th>
  <th>Last Name</th>
  <th>Title</th>
  <th>Email</th>
  <th>Extension</th>
 </tr>
<?php
 while ($Row = $Result->fetchRow(DB_FETCHMODE_ASSOC))
 {
  echo '<tr>';
  echo '<td>' . $Row['FirstName'] . '</td>';
  echo '<td>' . $Row['LastName'] . '</td>';
  echo '<td>' . $Row['Title'] . '</td>';
  echo '<td>' . $Row['Email'] . '</td>';
  echo '<td align="right">x' . $Row['Extension'] . '</td>';
  echo '</tr>';
 }
?>
 </table>
<?php
 $Result->free();
 $DB->disconnect();
}
?>
</body>
</html>

Code Explanation

As you can see, the PEAR DB API is very similar to the mysqli object-oriented API. Let's walk through the code.

First, we include the PEAR DB library. Notice that we simply use DB.php for the path:
```
require_once 'DB.php';
```
This will only work if:
- DB.php is in the same directory as EmployeeReport.php. This isn't likely as DB.php itself includes files, which would also have to be in the same directory.
- The include_path directive in php.ini includes a path to the pear folder containing DB.php.
Next, we connect to the database:
```
@$DB = DB::connect('mysqli://root:pwdpwd@localhost/Northwind');
```
This line of code will create a connection object if the connection is successful or an error object if it is not. The :: syntax will be covered when we discuss object-oriented PHP programming, but the crux of it is that the connect() method is a class-level method rather than an object-level method, so it can be called without first instantiating an object. (see footnote)
We then use the class-level isError() method to check if $DB is an error object, which would mean that the connection failed. If it did fail, we output an error.
```
if (DB::isError($DB))
{
 echo 'Cannot connect to database: ' . $DB->getMessage();
}
```

If the connection succeeded, we run our query:

$Query = 'SELECT * FROM Employees';
$Result = $DB->query($Query);
$NumResults = $Result->numRows();

And, after writing out our header row, we loop through the query results outputting a row for each record returned:
```
while ($Row = $Result->fetchRow(DB_FETCHMODE_ASSOC))
{
 echo '<tr>';
 echo '<td>' . $Row['FirstName'] . '</td>';
 echo '<td>' . $Row['LastName'] . '</td>';
 echo '<td>' . $Row['Title'] . '</td>';
 echo '<td>' . $Row['Email'] . '</td>';
 echo '<td align="right">x' . $Row['Extension'] . '</td>';
}
```
The fetchRow() method can take one of several constants to specify how a row is returned. In this example, we use DB_FETCHMODE_ASSOC to get the row as an associative array. Other options are DB_FETCHMODE_ORDERED (the default) and DB_FETCHMODE_OBJECT, which get the row as an indexed array and an object, respectively.

Exercise: Creating a Customer Report

Duration: 20 to 30 minutes.

In this exercise, you will create a sales report using PEAR DB.

Open PEAR-DB/Exercises/SalesReport.php for editing.
Write code to:
- Include the PEAR DB package.
- Connect to the Northwind database.
- If the connection fails, return an error message to the browser.
- If the connection succeeds run a query that gets the order date and the first and last name of the associated employee and the customer company for all orders. Order by OrderDate.
- Output the results of the query in a table.
- Free the result and disconnect from the database.

Code Sample: PEAR-DB/Exercises/SalesReport.php

<html>
<head>
<title>Sales Report</title>
</head>
<body>
<?php
//Include PEAR DB Package

//Connect to the Northwind database.
// If the connection fails, return an error message to the browser.
// If the connectoin succeeds run a query that gets the order date 
//  and the first and last name of the associated employee 
//  and the customer company for all orders.  Order by OrderDate.
?>
 <table border="1">
 <tr>
  <th>#</th>
  <th>Salesperson</th>
  <th>Customer</th>
  <th>Order Date</th>
 </tr>
<?php
 //Create rows for each record returned from the query.
?>
 </table>
<?php
 //Free the result and disconnect from the database.
}
?>
</body>
</html>

830 rows are returned. This is two many to display on a single page. MySQL has a LIMIT clause that specifies which and how many records to return from the query. For example, the query below would return 10 orders starting with the 100th order (note that the first row is row 0).

SELECT OrderDate, OrderID
FROM Orders
LIMIT 99,10;

Add code to your solution that allows the user to tab through the results 10 at a time with Previous and Next buttons.

Where is the solution?

PEAR:DB Conclusion

In thius lesson, you have learned to use PEAR-DB to connect to databases.

Footnotes

The homepage for PEAR is at http://pear.php.net/. For information on installing PEAR, visit http://pear.php.net/manual/en/installation.php.
If this doesn't make sense, don't worry about it. It will be clearer after you've learned about object-oriented programming.

To continue to learn PHP go to the top of this page and click on the next lesson in this PHP Tutorial's Table of Contents.

Table of Contents