MDB2

PEAR supplies a number of open source extensions to PHP including the MDB2 package, which provides a database abstraction layer, so that the PHP programmer doesn't have to worry about all the APIs for different databases. The homepage for PEAR is at http://pear.php.net For information on installing PEAR, visit http://pear.php.net/manual/en/installation.php.

Lesson Goals

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

Advantages and Disadvantages of MDB2

MDB2 is a database abstraction layer. Whether or not you decide to use MDB2 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 MDB2 is certainly helpful. If performance is key, then you may find the extra weight of MDB2 to be prohibitive.

Why use a database abstraction layer?

One big benefit of using a database abstraction layer like MDB2 is portability. MDB2 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, MDB2 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 MDB2

The connection string for connecting to the database with MDB2 is:

Syntax

driver://username:[email protected]/database

Some of the drivers supported by MDB2 are

mysql
mysqli
mssql
oci8
odbc
pgsql
sqlsrv
querysim
sqlite

To use mysqli to connect to the Northwind database, you would use the following connection string:

mysqli://username:[email protected]/Northwind

Code Sample:

MDB2/Demos/EmployeeReport.php

<!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<title>Employee Report</title>
</head>
<body>
<?php
require_once 'MDB2.php';
$db = MDB2::connect('mysqli://root:[email protected]/Northwind');
if (PEAR::isError($db))
{
	echo 'Cannot connect to database: ' . $db->getMessage();
}
else
{
	$sql = 'SELECT * FROM Employees';
	$result = $db->query($sql);
	$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(MDB2_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 MDB2 API is very similar to the mysql object-oriented API. Let's walk through the code.

First, we include the MDB2 library. Notice that we simply use MDB2.php for the path:
```
require_once 'MDB2.php';
```
This will only work if:
- MDB2.php is in the same directory as EmployeeReport.php. This isn't likely as MDB2.php itself includes files, which would also have to be in the same directory. OR...
- The include_path directive in php.ini includes a path to the pear folder containing MDB2.php.
Next, we connect to the database:
```
$db = MDB2::connect('mysqli://root:[email protected]/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.
If this doesn't make sense, don't worry about it. It will be clearer after you've learned about object-oriented programming.
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 (PEAR::isError($db))
{
	echo 'Cannot connect to database: ' . $db->getMessage();
}
```

If the connection succeeded, we run our query:

$sql = 'SELECT * FROM Employees';
$result = $db->query($sql);
$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(MDB2_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 MDB2_FETCHMODE_ASSOC to get the row as an associative array. Other options are MDB2_FETCHMODE_ORDERED (the default) and MDB2_FETCHMODE_OBJECT, which get the row as an indexed array and an object, respectively.

Continue to the next lesson on Authentication with PHP and SQL »

MDB2

Lesson Goals

Lesson Activities

Advantages and Disadvantages of MDB2

Why use a database abstraction layer?

When not to use a database abstraction layer?

Using MDB2

Syntax

Code Sample:

Table of Contents