Lasa Talkz...!

Today I’m gonna say you about something popular among the PHP developers. So that’s called PEAR. I hope you guys may be heard about PEAR. PEAR is an acronym for PHP Extension and Application Repository. PEAR is a framework or collection of PHP classes which allow us to use them in our solutions. Official web site of PEAR can be found on http://pear.php.net.

As I told you that PEAR is a collection of classes or components. So what are the components available in PEAR?  It has many more components such as Database, File system, Graphics and Image Processing, Mail, Math, Structures and etc. So today I’m going to show you how to use Database component of PEAR.

PEAR Database:

In most of web developing solutions, PHP developers use databases to store data. So the backend database management system can be MySQL, PostgreSQL, SQLite, etc. There are different commands available in PHP to handle each and every database types. What will happen when you want to shift from MySQL to PgSQL? This will result you to change all the PHP MySQL commands to PHP PgSQL commands. In this kind of changing environment, it is advisable to use a framework such as PEAR.

Using PEAR, you can handle many DBMSs without changing the code. It is something like a common API set. The only part you have to change when you want to shift is the connection string.

OK… Let’s consider about the example. In this example, I will connect to a MySQL database, fetch some data and display them on the web page. First you need to create the database as follows.

CREATE DATABASE test;
USE DATABASE test;
CREATE TABLE users(
    uname VARCHAR(20) NOT NULL,
    pword VARCHAR(20) NOT NULL
);

That’s all. Then the PHP code is as follows. I’ll explain them later.

<?php
// peardb_ex.php
require_once 'DB.php';

// connect to the database
$db = DB::connect('mysql://root:rootpass@localhost/test');

// check for errors
if(DB::isError($db)) {
    echo "Unable to connect to the database: " . $db->getMessage();
}else {

    // execute the query
    $sql = "select * from users";
    $res = $db->query($sql);

    // get number of rows affected
    $num_res = $res->numRows();

    echo "<b>$num_res users</b><hr /><hr />";

    // travers the resultset
    while($r = $res->fetchRow(DB_FETCHMODE_ASSOC)) {

        echo "username: " . $r['uname'] . "<br />";
        echo "password: " . $r['pword'] . "<br />";
        echo "<hr />";
    }

    // free resultset
    $res->free();

    // close the connection
    $db->disconnect();
}

require_once 'DB.php';

This is the file that has definitons for PEAR database component. It's available in the PEAR installation folder, so you need not to copy it to the local directory.

$db = DB::connect('mysql://root:rootpass@localhost/test');

This is how the connection will be established. The connection string can be specify as, Driver://username:password@host/database. If you want to shift from MySQL to PgSQL, the only thing you need to do is change the connection string to, pgsql://root:rootpass@localhost/test. Other commands will remain unchanged.

DB::isError($db);

This is used to check if any error occurred.

$sql = "select * from users";
$res = $db->query($sql);

This is the command used to execute SQL query against the database. (Same like $res = mysql_query($sql))

$num_res = $res->numRows();

Get number of rows returned by the query.

while($r = $res->fetchRow(DB_FETCHMODE_ASSOC)) {

    echo "username: " . $r['uname'] . "<br />";
    echo "password: " . $r['pword'] . "<br />";
    echo "<hr />";
}

This loop is used to travers the resultset. It is same as mysql_fetch_assoc($res).

$res->free();

Free the resultset.

$db->disconnect();

Close the connection.