Accessing Databases with DB

PEAR DB provides a consistent set of methods for using a relational database no matter what database you’re using. It supports the following PHP database extensions: FrontBase, Informix, InterBase, Microsoft SQL Server, mSQL, ODBC, MySQL, Sybase, OCI8, and PostgreSQL. A driver that supports new features in version 4 of MySQL is also in the works. This chapter covers DB version 1.4.

Exploring a Simple DB Example

Retrieving a result and displaying it in a table with DB looks like this:

// Load the DB code
require 'DB.php';

// Connect to the database
$dbh = DB::connect('mysql://user:password@host/database');

// Send a SELECT query to the database
$sth = $dbh->query('SELECT flavor, price, calories FROM ice_cream');

// Check if any rows were returned
if ($sth->numRows()) {
     print "<table>"; 
     print "<tr><th>Ice Cream Flavor</th><th>Price per Serving</th><th>Calories per Serving</th></tr>";
     // Retrieve each row 
     while ($row = $sth->fetchRow()) {
           // And print out the elements in the row
           print "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2</td></tr>\n";
     }
     print "</table>";
} else {
     print "No results";
}

DB::connect() is a static class method that returns an object. You interact with the database by calling methods on this object. In this example, the object is assigned to the variable $dbh, which stands for database handle. The argument to DB::connect() is a Data Source Name (DSN). DSNs are explained in the next section.

Next, you send a query to the database server with the $dbh->query() method. This method returns a statement handle that is assigned to $sth. This variable is a DB_Result object with methods that return information about the retrieved rows. The $sth->numRows() method returns the number of retrieved rows. In this example, it’s used to determine whether to print the retrieved data or just a message saying that no data was retrieved. The “Query Information” section explains methods such as numRows() that provide information about a query.

The $sth->fetchRow() method returns an array that contains one row of data retrieved from the database. The statement handle maintains an internal counter of what the “next” row to return is, so the first time you call fetchRow(), you get the first row retrieved from the database; the second time you call fetchRow(), you get the second row, and so on. After all retrieved rows have been returned, fetchRow() returns NULL instead of a result array. This makes it easy to use in a while() loop as the example does.

The fetchRow() method returns an indexed array with retrieved data. The first field requested by the SELECT query is in element 0 of the array, the second field in element 1, and so on. In this example, $row[0] is the value of the flavor column in each row, $row[1] is the price column, and $row[2] is the calories column. You can also retrieve rows as associative arrays or objects. The “Sending Queries and Retrieving Results” section discusses fetchRow() in more detail.