Quick Start Guide to Pear DB

Summary: A tutorial about how to use Pear DB
Maintainer/PHP Initials: Tomas V.V.Cox (cox)
Software Version: n/a (n/a)
Documentation Last Update: 2002/03/20
Download:
(right click->save as
over the link)
./index.php?pack=pear_tut&showsource=1 
YASPE Estadisticas

Translations of this document:

A new PEAR DB Manual in experimental state is already avaible at the PEAR manual. Check it out!


Table Of contents

1. Introduction (write me!)

2. Getting and Installing Pear

3. Pear DB Usage

4. Avaible methods map

5. Error Handling

6. Thanks


1. Introduction (write me!)

This is a tutorial about how to use the Pear DB extension. Pear DB, is a set of classes that provides:



2. Getting and Installing Pear

For the moment the Pear Project is still under heavy development, so the best way to get it, is from the CVS (the Pear DB distribution that comes with PHP is outdated, also the one that comes with PHP 4.0.6!). Then, the only thing you need to do, is add the Pear base directory to your include_path entry in php.ini. You can also do it, using: ini_set('include_path', '/pear_base_dir').

Here is an example step by step:

Where you want to place Pear files
# cd /usr/local/lib
Log to the repository (use "phpfi" as password):
# cvs -d :pserver:cvsread@cvs.php.net:/repository login
Launch this command to get all the pear files. You can use it also
for update yet downloaded files. Other options may be "today", "last month", etc.
I recommend the "last week" option because normally the possible bugs generated
by new commits are found and fixed in one week :-)
# cvs -d :pserver:cvsread@cvs.php.net:/repository export -D "last week" php4/pear
Edit your php.ini and add the directory
/usr/local/lib/php4/pear to your include_path entry. If you don't have
access to php.ini, you can use ini_set('include_path', 'path_to_pear'); in
your code.

The complete documentation of the PHP CVS can be found here.

Please note that Pear DB requires PHP > 4.0.4, but other packages inside Pear like the XML Parser ot the pear installer script will require PHP >= 4.0.5.

For the moment this guide only covers the last CVS version of Pear DB, but may be is valid for any recent version.


3. Pear DB Usage

3.1 Connecting and Disconnecting from a database


<?php
// The pear base directory must be in your include_path
require_once 'DB.php';
$user 'foo';
$pass 'bar';
$host 'localhost';
$db_name 'clients_db';

// Data Source Name: This is the universal connection string
$dsn "mysql://$user:$pass@$host/$db_name";

// DB::connect will return a Pear DB object on success
// or a Pear DB Error object on error
// You can also set to TRUE the second param
// if you want a persistent connection:
// $db = DB::connect($dsn, true);
$db DB::connect($dsn);

// With DB::isError you can differentiate between an error or
// a valid connection.
if (DB::isError($db)) {
        die (
$db->getMessage());
}
....
// You can disconnect from the database with:
$db->disconnect();
?>

The Data Source Name connection string ($dsn param in the above example), has the following permited formats (directly copied from the parseDSN method from pear/DB.php):


     *  phptype: Database backend used in PHP (mysql, odbc etc.)
     *  dbsyntax: Database used with regards to SQL syntax etc.
     *  protocol: Communication protocol to use (tcp, unix etc.)
     *  hostspec: Host specification (hostname[:port])
     *  database: Database to use on the DBMS server
     *  username: User name for login
     *  password: Password for login
     *
     * The format of the supplied DSN is in its fullest form:
     *
     *  phptype(dbsyntax)://username:password@protocol+hostspec/database
     *
     * Most variations are allowed:
     *
     *  phptype://username:password@protocol+hostspec:110//usr/db_file.db
     *  phptype://username:password@hostspec/database_name
     *  phptype://username:password@hostspec
     *  phptype://username@hostspec
     *  phptype://hostspec/database
     *  phptype://hostspec
     *  phptype(dbsyntax)
     *  phptype

The Databases currently supported (the phptype DSN part) are:


mysql  -> MySQL
pgsql  -> PostgreSQL
ibase  -> InterBase
msql   -> Mini SQL
mssql  -> Microsoft SQL Server
oci8   -> Oracle 7/8/8i
odbc   -> ODBC (Open Database Connectivity)
sybase -> SyBase
ifx    -> Informix
fbsql  -> FrontBase

Please note, that some features may be not supported by all database backends. Please refer to the Pear DB extensions status document located at: <pear base dir>/DB/STATUS to get the detailed list.

3.2 Doing a query to the database


<?php
// Once you have a valid DB object
...
$sql "select * from clients";
// If the query is a "SELECT", $db->query will return
// a DB Result object on success.
// Else it simply will return a DB_OK
// On failure it will return a DB Error object.
$result $db->query($sql);
// Always check that $result is not an error
if (DB::isError($result)) {
        die (
$result->getMessage());
}
....
?>

3.3 Fetch the rows from the query

3.3.1 Fetch row functions


<?php
// Once you have a valid DB Result object
...
// Get each row of data on each iteration until
// there is no more rows
while ($row $result->fetchRow()) {
    
$id $row[0];
}
?>

Instead of fetchRow() you can use fetchInto() who directly assigns by reference the $row value:


<?php
...
while (
$result->fetchInto($row)) {
    
$id $row[0];
}
?>

3.3.2 Select the format of the fetched row

The fetch modes supported are DB_FETCHMODE_ORDERED (default), DB_FETCHMODE_ASSOC and DB_FETCHMODE_OBJECT.

Examples of the data structure returned by the fetch row methods:


<?php
$res 
$db->query('select id, name, email from users');
$row $res->fetchRow($mode);

//With $mode = DB_FETCHMODE_ORDERED
//The default behavior is to return an ordered array.
$row = array (
    
=> <column "id" data>,
    
=> <column "name" data>,
    
=> <column "email" data>
);

$id $row[0];

//With $mode = DB_FETCHMODE_ASSOC
//Returns an associative array with column names as array keys:
$row = array (
    
'id'    => <column "id" data>,
    
'name'  => <column "name" data>,
    
'email' => <column "email" data>
);

$id $row['id'];

//With $mode = DB_FETCHMODE_OBJECT
//Returns a DB_row object with column names as properties:
$row db_row Object 
(
    [
id]    => <column "id" data>,
    [
name]  => <column "name" data>,
    [
email] => <column "email" data>
)

$id $row->id;
?>

3.3.3 Set the format of the fetched row

You can supply the fetch mode to use to the fetchrow() / fetchInto() method or also you can set a default mode for your DB instance.


<?php
...
// 1) Set the mode per call:
while ($row $result->fetchRow(DB_FETCHMODE_ASSOC)) {
    [..]
}
while (
$result->fetchInto($rowDB_FETCHMODE_ASSOC)) {
    [..]
}

// 2) Set the mode for all calls:
$db DB::connect($dsn);
// this will set a default fetchmode for this Pear DB instance
// (for all queries)
$db->setFetchMode(DB_FETCHMODE_ASSOC);
$result $db->query(...);
while (
$row $result->fetchRow()) {
    
$id $row['id'];
}
?>

3.3.4 Fetch rows by number

The Pear DB fetch system also supports an extra param to the fetch statement, so from a result you can fetch rows by number. This is specially helpful if you only want to show sets of an entire result (for example in building paginated HTML lists), fetch rows in an special order, etc.


<?php
...
// the row to start fetching
$from 50;
// how many results per page
$res_per_page 10;
// the last row to fetch for this page
$to $from $res_per_page;
foreach (
range($from$to) as $rownum) {
    if (!
$row $res->fetchrow($fetchmode$rownum)) {
        break;
    }
    
$id $row[0];
    ....
}
?>

3.3.5 Free the result

Once you have finished with the result, you can free it with the free() method:


<?php
...
$result $db->query('SELECT * FROM clients');
while (
$row $result->fetchRow()) {
    ...
}
$result->free();
?>

3.4 Quick data retrieving

Pear DB provides some special ways to retrieve information from a query without the need of using fetchRow() and loop throw results. These are the methods getOne, getRow, getCol, getAssoc and getAll. Here are some examples of use:


<?php
require_once 'DB.php';
$db DB::connect('pgsql://postgres@unix+localhost/clients_db');
// -----------------------------------------------------------
// getOne retrieves the first result of the first column
// from a query
$numrows $db->getOne('select count(id) from clients');
// -----------------------------------------------------------
// getRow will fetch the first row and return it as an array
$sql 'select name, address, phone from clients where id=1';
if (
is_array($row $db->getRow($sql))) {
    list(
$name$address$phone) = $row;
}
// -----------------------------------------------------------
// getCol will return an array with the data of the
// selected column. It accepts the column number to retrieve
// as the second param.
// The next sentence could return for example:
// $all_client_names = array('Stig', 'Jon', 'Colin');
$all_client_names $db->getCol('select name from clients');
// -----------------------------------------------------------
// Other functions are: getAssoc() and getAll().
// For the moment refer to their in-line documentation
// at pear/DB/common.php
// -----------------------------------------------------------
?>

The "get*() family methods" will do all the dirty job for you, this is: launch the query, fetch the data and free the result. Please note that as all Pear DB functions they will return a Pear DB_error object on errors.

3.5 Getting more info from query results (numRows, numCols, affectedRows, tableInfo)

With Pear DB you have many ways to retrieve useful information from query results. These are:

Examples of use:


<?php
...
$db DB::connect($dsn);
$sql 'select * from clients';
$res $db->query($sql);
// Don't forget to check if the returned result from your
// action is a Pear Error object. If you get a error message
// like 'DB_error: database not capable', means that
// your database backend doesn't support this action.
//
// Number of rows
echo $res->numRows();
// Number of cols
echo $res->numCols();
// Table Info
print_r ($res->tableInfo());
// Affected rows
$sql "delete from clients";
// remember that this statement won't return a result object
$db->query($sql);
echo 
'I have deleted ' $db->affectedRows() . 'clients';
?>

3.6 Sequences

Sequences is a way of offering unique IDs for data rows. If you do most of you work with e.g. MySQL, think of sequences as another way of doing AUTO_INCREMENT. It's quite simple, first you request an ID, and then you insert that value in the ID field of the new row you're creating. You can have more than one sequence for all your tables, just be sure that you always use the same sequence for any particular table.


<?php
...
// Get an ID (if the sequence doesn't exist, it will be created)
$id $db->nextID('mySequence');

// Use the ID in your INSERT query
$res $db->query("INSERT INTO myTable (id,text) VALUES ($id,'foo')");
...
?>

3.7 Prepare & Execute/ExcuteMultiple (not yet written)


<?php
// UNTESTED CODE !!!
//
// Example inserting data
$alldata = array(
  array(
1'one''en'),
  array(
2'two''to'),
  array(
3'three''tre'),
  array(
4'four''fire')
);
$sth $dbh->prepare("INSERT INTO numbers VALUES(?,?,?)");
foreach (
$alldata as $row) {
    
$dbh->execute($sth$row);
}
//Here's an example of a file placeholder:
$myfile "/tmp/image.jpg";
$sth $dbh->prepare('INSERT INTO images (?, &)');
$dbh->execute($sth, array("this is me"$myfile));
//After I commit a bugfix that I have on my laptop, you can use
//parameter arrays in the getXxx methods too:
$ver $dbh->getOne("SELECT stableversion FROM packages WHERE name = ?",
                    array(
$package));
?>

3.8 autoCommit, commit and rollback (not yet written)


<?php
//examples here
?>


4. Avaible methods map


<?php
/*
* From the DB_(driver) objects
*/
// get the object with, ie:
$db DB::connect('mysql://user:pass@localhost/my_db');

// Set options
$db->setErrorHandling();
$db->setFetchmode();
// Information
$db->affectedRows();
$db->tableInfo();
// Database manipulation
$db->query();
// Data fetch
$db->nextId();
$db->getOne();
$db->getRow();
$db->getCol();
$db->getAssoc();
$db->getAll();
// Place holders and execute related
$db->quote();
$db->prepare();
$db->execute();
$db->executeMultiple();
// Transactions
$db->autoCommit();
$db->commit();
$db->rollback();
// Disconnection
$db->disconnect();

/*
* From DB_result objects
*/
// get the object with, ie:
$res $db->query('select * from foo');

// Data fetch
$res->fetchRow();
$res->fetchInto();
// Result Info
$res->numCols();
$res->numRows();
$res->tableInfo();
// Free
$res->free();

/*
* From DB_error objects
*/
// get the object with, ie:
$error $db->query('select * from no_table');

$error->getMessage();
$error->getDebugInfo();
$error->toString();
?>


5. Error Handling

5.1 Getting the error message from a Pear DB Error

All errors returned by Pear DB are Pear Errors. Here is a way to retrieve information of it:


<?php
...
$res $db->query('select * from no_table');
if (
DB::isError($res)) {
    
// get the portable error string
    
echo $res->getMessage();
}
?>

5.2 Debuging Pear DB Errors

Pear DB use a portable error messages system to output errors to the user. This has many advantages like provide an easy way to translate it into other languages or take a specific action on a specific error. But this don't give many information to the developer on what was happen. To catch the real error given from the database backend plus the last query you launched, you can use the getDebugInfo() method:


<?php
$sql 
'select * from no_table';
if (
DB::isError($res $db->query($sql))) {
    
// get the native backend error
    // and the last query
    
echo $res->getDebugInfo();
}
?>

Normaly when a PHP function fails, it will print an error message. In Pear these behaviour has been disabled. But perhaps sometimes you'll need to see these messages to trap obscure errors in your code. This can be done with the set_error_handler PHP function, documented in the PHP Manual. Here is a quick example:


<?php
// what messages to report
error_reporting (E_ALL E_NOTICE);
// this function will handle all reported errors
function my_error_handler ($errno$errstr$errfile$errline) {
    echo 
"In $errfile, line: $errline\n<br>$errstr";
}
set_error_handler ('my_error_handler');
$db DB::connect('pgsql://postgres@localhost/no_db');
...
?>

5.3 Taking automatic actions on errors

As you can see, Pear DB do extensive error checking and reporting, feature that force the developer to always check if the result from his action had returned an error or not. Pear DB take care also of this tired task, and provides a very flexible system to automatic do actions when a error occurs.

The avaible actions are:

A simple example:


<?php
require_once 'DB.php';
// Set the default action to take on error
PEAR::setErrorHandling(PEAR_ERROR_DIE);
// From here you don't need to check errors any more
$db DB::connect('pgsql://postgres@localhost/my_database');
$res $db->query('select id from no_table');
// at this point the execution is aborted and the error message is raisen
...
?>

A more advanced example:


<?php
// Define the app environment (this is: what errors you want to output)
define ('DEBUG_ENV'true);
// This function will handle all errors
function handle_pear_error ($error_obj) {
    
// Be verbose while developing the application
    
if (DEBUG_ENV) {
        die (
$error_obj->getMessage()."\n".$error_obj->getDebugInfo());
    
// Dump a silly message if the site is in production
    
} else {
        die (
'Sorry you request can not be processed now. Try again later');
    }
}

require_once 
'DB.php';
// On error, call the "handle_pear_error" function back
// You can also use an object as pear error handler so:
// setErrorHandling(PEAR_ERROR_CALLBACK, array($object,'method_name');
PEAR::setErrorHandling(PEAR_ERROR_CALLBACK'handle_pear_error');
$db DB::connect('pgsql://postgres@localhost/site_db');
$res $db->query('select id from no_table');
// at this point the execution is aborted and the "handle_pear_error"
// function is called with the error object as its first argument
while ($row $res->fetchRow()) {
    ...
}
...
?>

Please refer to the Pear Reference Manual, to get the detailed description of the different possible actions.

Here is an idea on how to build an extended error handling system:


<?php
error_reporting 
(E_ALL E_NOTICE);
// this function will handle all errors reported by PHP
function php_error_handler ($errno$errstr$errfile$errline) {
    die (
"In $errfile, line: $errline\n<br>$errstr");
}
set_error_handler ('php_error_handler');
// this function will catch errors generated by Pear,
// transform it to PHP errors and trigger them to the php_error_handler
function pear_error_handler ($err_obj) {
    
$error_string $err_obj->getMessage() . '<br>' $error_obj->getDebugInfo();
    
trigger_error ($error_stringE_USER_ERROR);
}
require 
'DB.php';
PEAR::setErrorHandling (PEAR_ERROR_CALLBACK'pear_error_handler');
// force an error
$db DB::connect('pgsql://postgres@localhost/no_db');
...
?>


6. Thanks

Special thanks to Stig for creating the wonderful Pear World, the people who are helping me on maintaining this document and of course also to the other Pear Team for their continuous work on it.


Please send corrections, updates or additions to me:
Tomas V.V.Cox

If you want to contribute to this document, please save the source template, make the modifications to it and send it back to me, with a brief summary of changes.

To Write:
- Pear DB introduction
- Other features like: prepare/execute, commit
- How to help or report bugs
Changelog:
#2002-02-14
- Added Japanese translation by "Naoki Shima" 
#2001-10-12
- Added links to the tutorial translations
- Couple of corrections reported by Nicolas Hoizey
#2001-08-23
- Added Table of contents (Thanks Natanael Copa!)