Quick Start Guide to Pear DB
Translations of this document:
Table Of contents
This is a tutorial about how to use the Pear DB
extension. Pear DB, is a set of classes that provides:
- Database abstraction
- Advanced error handling
- etc
For the moment the Pear Proyect
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.
<?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.
<?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());
}
....
?>
|
<?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];
}
?>
|
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 (
0 => <column "id" data>,
1 => <column "name" data>,
2 => <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;
?>
|
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($row, DB_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'];
}
?>
|
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];
....
}
?>
|
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();
?>
|
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.
With Pear DB you have many ways to retrieve useful information from
query results. These are:
- numRows(): Returns the total number of rows returned from a "SELECT" query.
- numCols(): Returns the total number of columns returned from a "SELECT" query.
- affectedRows(): Returns the number of rows affected by a data manipulation
query ("INSERT", "UPDATE" or "DELETE").
- tableInfo(): Returns an associative array with information
about the returned fields from a "SELECT" query.
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';
?>
|
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')");
...
?>
|
<?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));
?>
|
<?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();
?>
|
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();
}
?>
|
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');
...
?>
|
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:
- Return the error object (PEAR_ERROR_RETURN). This is the default action.
- Print error (PEAR_ERROR_PRINT)
- Print the error message and abort execution (PEAR_ERROR_DIE)
- Use the PHP funcion trigger_error() to raise a PHP error (PEAR_ERROR_TRIGGER)
- Pass the error object to a function or object method (PEAR_ERROR_CALLBACK)
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_string, E_USER_ERROR);
}
require 'DB.php';
PEAR::setErrorHandling (PEAR_ERROR_CALLBACK, 'pear_error_handler');
// force an error
$db = DB::connect('pgsql://postgres@localhost/no_db');
...
?>
|
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:
#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!)