For using database, you need to install a package of PEAR called ‘MDB2’ along with the installation of PEAR and Smarty.

MDB2 provides a common API for all support RDBMS.

Connecting to database

To connect to a database through PEAR::MDB2, you have to create a valid DSN – data source name. This DSN consists in the following parts:

phptype: Database backend used in PHP (i.e. mysql , pgsql etc.)

dbsyntax: Database used with regards to SQL syntax etc.

protocol: Communication protocol to use ( i.e. 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

proto_opts: Maybe used with protocol

option: Additional connection options in URI query string format. options get separated by &.

The DSN can either be provided as an associative array or as a string. The string format of the supplied DSN is in its fullest form:

phptype(dbsyntax)://username:password@protocol+hostspec/database?option=value

To instantiate a database object you have several methods available using MDB2.

factory() : Will instantiate a new MDB2_Driver_Common instance, but will not connect to the database until required. This will delay making the actual connection.

connect() : Will instantiate a new MDB2_Driver_Common instance, and will establish a database connection immediately. This way any connection issues will immediately raise an error.

singleton() : Returns a MDB2_Driver_Common instance.

A new MDB2_Driver_Common object is only created once using factory(), subsequent calls to singleton will return a reference to the existing object. This method is preferred over declaring your database object as a global.

To connect to a database you have to use the function factory(), connect() or singleton(), which require a valid DSN as the first parameter. This parameter can either be a string or an array. The second parameter is the optional $options array that can contain runtime configuration settings for this package.

I have used the connect() function with the DSN as

phptype://username:password@hostspec/database

$mdb2 =& MDB2::connect('mysql://root:@localhost/test');

In my case, the database backend is mysql, database username is root, database password is null, the hostname is localhost and the database name is test.

Querying the database

PEAR MDB2 provides several methods for querying databases. The most direct method is query(). It takes a SQL query string as an argument. There are two possible returns: A new MDB2_Result object for queries that return results (such as SELECT queries), or a MDB2_Error object on failure. It should not be used with statements that manipulate data (such as INSERT queries).

exec() should be used for manipulation queries. There are two possible returns: An integer denoting the number of affected rows for statements that manipulate data (such as INSERT queries), or a MDB2_Error object on failure. It should not be used with statements that return results (such as SELECT queries).

Here, I will only be dealing with the SELECT queries.

$mdb2 = new ConnectMDB2;</p>
// calling the function conMDB2 of ConnectMDB2 class
$connect = $mdb2->conMDB2();</p>
$res = $connect->query("SELECT * FROM basic");

Fetching result

The MDB2_Result_Common object provides four methods for fetching data from rows of a result set: fetchOne(), fetchRow(), fetchCol() and fetchAll().

fetchRow() and fetchOne() read an entire row or a single field from a column respectively. The result pointer gets moved to the next row each time these methods are called. NULL is returned when the end of the result set is reached.

fetchAll() and fetchCol() read all rows in the result set and therefore move the result pointer to the end. While fetchAll() reads the entire row data, fetchCol() only reads a single column.

MDB2_Error is returned if an error is encountered.

I have used the fetchRow() method. The fetchmode MDB2_FETCHMODE_ASSOC is used for fetching data. The default fetchmode is MDB2_FETCHMODE_ORDERED.

while($row[] = $res->fetchRow(MDB2_FETCHMODE_ASSOC))
{
$result = $row;
}

If there is any error then the error message is displayed in a separate template called error.tpl. And if there is no error while fetching data then the fetched result is displayed in the template file index.tpl.

if(PEAR::isError($res))
{
// get error message
$error =& $res->getMessage();</p>
// assign error message
$smarty->assign('errmessage',$error);</p>
// display the message and exit
$smarty->display('error.tpl');
exit();
}</p>
// asign the content
$smarty->assign('result',$result);

// display the content
$smarty->display('index.tpl');

In the template file, foreach loop is used to display the result.

{foreach from=$result item=view}
<tr bgcolor="{cycle values="#ffffff,#eeeeee"}">
<td>{$view.firstname}</td>
<td>{$view.lastname}</td>
</tr>
{/foreach}

Download source code

Note: I have not included Smarty library files in this zip file. You can download the Smarty library files from http://smarty.php.net/download.php or directly from http://chapagain.googlecode.com/files/smarty.zip

Get New Post by Email

Subscribe RSS Feed Follow @chapagain