Posted on July 23rd, 2005
13490 views
PEAR::DB - Database abstraction layer for multiple database platforms
Table of Content: 1. Introduction to PEAR 2. Introduction to PEAR::DB / Database Abstraction 3. Installing PEAR::DB 4. Using PEAR::DB 1. Introduction to PEAR What is PEAR? (from the PEAR website) PEAR is short for "PHP Extension and Application Repository" and is pronounced just like the fruit. The purpose of PEAR is to provide: - A structured library of open-sourced code for PHP users - A system for code distribution and package maintenance - A standard style for code written in PHP - The PHP Foundation Classes (PFC) - The PHP Extension Community Library (PECL) - A web site, mailing lists and download mirrors to support the PHP/PEAR community PEAR is a community-driven project with the PEAR Group as the governing body. The project has been founded by Stig S. Bakken in 1999 and quite a lot of people have joined the project since then. How do I install PEAR? The installation guide on how to install PEAR 2. Introduction to PEAR::DB PEAR::DB is a Database Abstraction Layer for multiple database platforms. What's a Database Abstraction Layer? Abstraction is a technique which simplifies something complex. It does this by removing non-essential parts of the object, allowing us to concentrate on the important parts. In the case of database abstraction, the complexities of connecting to a database is hidden behind a standard API, thereby allowing the programmer to connect to many different types of databases without relearning the methods and syntax peculiar to each different type. (as said by PHPBuilder) Basically, PEAR::DB allows you to use the same methods of the object for multiple database platforms. This could be very usefull if you have a product which you want to be available for all database platforms. Read on to learn how to install this great package and how to use it. 3. Installing PEAR::DB PEAR::DB can be installed in many different ways. The easiest way is the PEAR package manager. (Root access is required for this) (requires the latest version of the PEAR package manager to be installed.) Automatic Installation: Go to your shell and run: $ pear install DB Replace with the name of the package you wish to install. PEAR package manager now downloads and installs the specified package. Semi-Automatic Installation: Another way to install a package offline is putting the package in your PEAR dir. Then run the following command: $ pear install DB.tgz This installs the package (downloaded as a .tgz) without needing to have an internet connection. Manual Installation: To install the package manually, let's put the package in /includes/ Use the following code to tell PHP where to look for our package: <?php ini_set(\"include_path\", '/var/www/www.example.com/includes/' . PATH_SEPARATOR . ini_get(\"include_path\")); ?> Now we have set our include_path, we can simply require our package like this: <?php require_once 'DB.php'; ?> After installing PEAR::DB, read on for the instructions on how to use it. 4. Using PEAR::DB This part of the tutorial will teach you how to effectively use PEAR::DB. <?php // connection information $host = 'localhost'; $database = 'my_database'; $user = 'root'; $pass = 'password'; // connect and select the database $connection = mysql_connect( $host, $user, $pass ); mysql_select_db( $database, $connection ); // query and fetch $result = mysql_query( 'SELECT * FROM news ORDER BY id DESC LIMIT 5', $connection ); while( $row = mysql_fetch_assoc( $result ) ) { // spit it out echo \"title: $row[title]\"; } mysql_close( $connection ); ?> The script above is an example of a simple 'getting information from database and spit it out'-script. Below, this is done with PEAR::DB. <?php // require PEAR::DB require_once 'DB.php'; // set up a connection $dns = 'mysql://root:password@localhost/my_database'; $DB = DB::connect( $dns, false ); // query and fetch $result = $DB->query( \"SELECT * FROM news ORDER BY id DESC LIMIT 5\" ); while( $row = $result->fetchrow(DB_FETCHMODE_ASSOC) ) { echo \"title: $row[title]\"; } $DB->disconnect(); ?> To me, this seems a lot prettier. Below, the code explained. // require PEAR::DB require_once 'DB.php'; In order to initiate the PEAR::DB object, we need to require the package. Since the 'include_path' is set in php.ini, you don't have to worry about the file not being in the current dir. // set up a connection $dns = 'mysql://root:password@localhost/myDatabase'; $DB = DB::connect( $dns, false ); We're setting up a connection. For PEAR::DB we must use a dns instead of a normal connect. If you want to use pgSQL, change mysql:// to pgsql:// The method 'connect' inherits a child class, specific to database platform. // query and fetch $result = $DB->query( \"SELECT * FROM news ORDER BY id DESC LIMIT 5\" ); while( $row = $result->fetchrow(DB_FETCHMODE_ASSOC) ) { echo \"title: $row[title]\"; } PEAR::DB's method query make a new object which holds result data. In this case $result. The method fetchrow allows you to fetch a row. In this example we're using DB_FETCHMODE_ASSOC, which orders result by field name. If we are to do just fetchmode() without arguments, it orders the array on what it's been set to for default. Other constants are DB_FETCHMODE_ORDERED, which indexes the array numerically and DB_FETCHMODE, which does whatever default has been set to. $DB->disconnect(); This disconnects from the database. Next up we will look at some more usefull features of PEAR::DB $result = $DB->query( \"SELECT * FROM news\" ); if( DB::isError( $result ) ) { die( mysql_error() ); } This is PEAR::DB's basic error handler. It checks if there is an error and spits out what it knows. $count = $DB->getOne( \"SELECT * FROM news\" ); echo \"there are $count news items\"; The method getOne gets the number of rows selected by the query and automaticly frees result. This is basically all you need to know about this nifty PEAR package. I hope you enjoyed reading this tutorial and consider using this package in your own scripts! |