PEAR:DB
- To use the PEAR DB package as a database abstraction layer.
PEAR 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:
driver://username:password@host/database
Some of the drivers supported by PEAR DB are
- mysqli
- myssql
- 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>
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:This will only work if:
require_once 'DB.php';
- 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: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.
@$DB = DB::connect('mysqli://root:pwdpwd@localhost/Northwind'); - 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: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 enumerated array and an object, respectively.
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>'; }