PHP Hacks - Using PEAR DB

Home

All Articles

Databases

Using PEAR DB

PHP Articles

Main Menu

Using PEAR DB

Monday, 24 July 2006

While working with databases, wouldn’t you love to concentrate on coding rather than the syntax for a particular database API. Database Abstraction is a method of coding that lets you do just that. You use generic methods to access the database, any database. Obviously, you need additional code or libraries to do database abstraction. That is where PEAR comes in. PEAR includes a database abstraction class that you can use in your scripts. There are other abstraction classes out there, but for the purpose of this tutorial we will focus on the one provided with PEAR. What is PEAR?

Essentially, PEAR gives you the developer a bunch of reusable components that follow a particular coding standard and API. This allows you to create applications that are generic enough to work on a variety of platforms with little or no modifications. PEAR provides functionality for databases, authentication, filesystem operations, networking, XML, and tons of other things. To know more about PEAR you can read the article on PEAR in our website.

Let’s concentrate on PEAR’s database abstraction layer.

Let’s just begin by looking at a few of the functions available to you in PEAR’s database abstraction class. This is by no means a complete list of functions available from PEAR. For more information about PEAR, see their website at http://pear.php.net/.

DB::connect

mixed connect (string $dsn [, array $options])

we will use the above mentioned function to connect to the database. While coding it will look something like this:

$db = DB::connect("mysql://user:pass@host/db_name", FALSE);

It will return a connection object or an error object which will be stored in $db. You may be wondering what that weird :: is. Well, DB is the class for data abstraction and connect is the function. The reason for the :: is so that we can use the connect function without creating an instance of the class DB. The :: can also be used to access a function of a base class when you are dealing with inherited classes.

We passed a string to the connect function that looks like a URL. This is a dsn, or data source name. Here we pass the database type and other connection information such as username and password.

The second, optional parameter is whether to use persistent connections or not. Not all databases support persistent connections and the default for this parameter is false.

Here is how it is commonly used:

<?php
require_once 'DB.php';
$user = 'foo';
$pass = 'bar';
$host = 'localhost';
$db_name = 'clients_db';
$dsn = "mysql://$user:$pass@$host/$db_name";
$db = DB::connect($dsn, false);
?>

DB::disconnect

boolean disconnect ()

This is the function to be used when disconnecting from a database. It is a good practice to use the disconnect function. In PHP, the end of a script should automatically do a disconnect, but I wouldn’t rely on it. Use it just to be safe.

To use this function, we will reference the database object that was returned from an initial connect statement:

$db = DB::connect($dsn, false);
$db->disconnect();

DB::isError

boolean isError (DB_Error $value)

While using one of the many database functions, if there is an error; an error object will be returned. You can check for an error with DB::isError. We can check for an error in the connection attempt or while a query is executing. If the object is an error, the function returns true. Otherwise, it returns false. Once we have determined that we are dealing with an error object, then there are methods of that error object we can use to display the error.

It is a good practice to check for return errors when making connections or querying a database. Here is an example for DB::isError, to check for a successful connection:

$db = DB::connect($dsn);
if (DB::isError($db)) {
    die ($db->getMessage());
}

DB::getOne

mixed &getOne (string $query [, array $params])

This function provides you data from the first column of the first row of a table that a query has returned. This is useful for doing count or sum functions. If it fails to return a value it will return an error object.

A typical use would be:

$numrows = $db->getOne('SELECT count(*) FROM mystuff');

The nice thing about DB::getone is that it executes the query, gets the results, and frees the results. There’s no need to worry about cleanup with this function.

DB::query()

mixed &query (string $query [, array $params])

This is the general purpose query function. On failure it will return an error object. On success it will return either a DB_OK or a result set object. A DB_OK is a PEAR constant that just tells you the query executed successfully. You will receive a DB_OK for any query that executes properly and doesn’t return a result set, i.e. an INSERT or UPDATE.

Usage for this function:

$sql = "SELECT * FROM mystuff ORDER BY stuff";
$result = $db->query($sql);

DB_Result::fetchRow

mixed fetchRow ([integer $fetchmode [, integer $rownum]])

OK, we have seen everything we need to do except actually get the data. This function will grab a single row from a result set. It will return either a row of data, NULL if there are no more rows, or an error object. The neat thing about this fetchRow function is that you specify the row number you want it to grab. This comes in handy when you are dealing with a DBMS that doesn’t support LIMIT or a similar function.

There are three fetch modes you can specify with this function:

    * DB_FETCHMODE_ORDERED: this is the default fetch mode and will return an array with numerical keys.
    * DB_FETCHMODE_ASSOC: this mode returns an array with column names as the keys.
    * DB_FETCHMODE_OBJECT: this mode will return an object with column names as the properties.

Typical usage for this function:

$row = $result->fetchrow(DB_FETCHMODE_ASSOC);

A simple script

Hopefully this tutorial has generated your interest for PEAR. I will be introducing other components of PEAR in the near future. I will leave you with a simple script to do results paging (previous and next links) using the PEAR DB class. This method of paging should work across all databases that PEAR supports.

<?
$limit = 5;
require_once 'DB.php';
$user = 'user';
$pass = 'pass';
$host = 'localhost';
$db_name = 'test';
$dsn = "mysql://$user:$pass@$host/$db_name";
$db = DB::connect($dsn);
if (DB::isError($db)) {
    die ($db->getMessage());
}
if(isset($_GET['start'])): $start = $_GET['start']; else: $start = 0; endif;
$sql = "SELECT * FROM mystuff ORDER BY stuff";
$result = $db->query($sql);
if (DB::isError($result)) {
    die ($result->getMessage());
}
foreach (range($start, $start + $limit - 1) as $rownum) {
    if (!$row = $result->fetchrow(DB_FETCHMODE_ASSOC, $rownum)) {
        break;
    }
    echo $row['stuff'] . "<BR>\n";
}
$result->free();
$numrows = $db->getOne('SELECT count(*) FROM mystuff');
$db->disconnect();
if($start > 0) {
    echo "<a href=\"".$_SERVER['PHP_SELF']."?start=".($start - $limit)."\">Back</a><BR>\n";
}
if (($start + $limit) < $numrows) {
    echo "<a href=\"".$_SERVER['PHP_SELF']."?start=".($start + $limit)."\">Next</a><BR>\n";
}
?>

Comments

Add New

RSS

Only registered users can write comments!

< Prev		Next >

[ Back ]

Syndicate