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 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.
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:root@localhost/test');
In my case, the database backend is mysql, database username is root, database password is root, 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.
$mdb2 = new ConnectMDB2;
// calling the function conMDB2 of ConnectMDB2 class
$connect = $mdb2->conMDB2();
$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. fetchAll() and fetchCol() read all rows in the result set, 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();
}
// 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}