Creating a Complex Smarty Application Using Databases
In this section, I will demonstrate how to create an application that will use a bookstore database and its table, books. The books table structure and content can be seen in Figure 3.
Click here for larger image
Figure 3. The Books Table Structure and Content
This application will connect to the bookstore database to get all the records, put them all into an array, one row next to another, and display the whole table using the template file.
But first, to connect to the bookstore database in PHP 5, I use the DatabaseSingletonClass listed below:
<?php
class DatabaseSingletonClass
{
//set this constants to connect to your database - the default settings
//are just for testing tasks
const MYSQL_HOST = "127.0.0.1";
const MYSQL_USER = "root";
const MYSQL_PASS = "";
const MYSQL_DB = "bookstore";
//A static member variable representing the class instance
private static $_instance = null;
//Locked down the constructor, therefore the class cannot be
//instantiated
private function __construct() { }
//Prevent any object or instance of that class to be cloned
public function __clone() {
trigger_error( "Cannot clone instance of Singleton pattern ...",
E_USER_ERROR );
}
//Prevent any object or instance to be deserilized
public function __wakeup() {
trigger_error('Cannot deserialize instance of Singleton pattern ...',
E_USER_ERROR );
}
//Have a single globally accessible static method
public static function getInstance()
{
if( !is_object(self::$_instance) )
{ //or if( is_null(self::$_instance) ) or if( self::$_instance ==
null )
self::$_instance = new MySQLi(self::MYSQL_HOST,
self::MYSQL_USER, self::MYSQL_PASS, self::MYSQL_DB);
if(self::$_instance->connect_error)
{
throw new Exception('MySQL connection failed: ' .
self::$_instance->connect_error);
}
}
return self::$_instance;
}
}
?>
The index.php listing uses the above DatabaseSingletonClass.php script to connect to the bookstore database. Then the Smarty template file loops the $row array and outputs each record of it.
<?php
//include the DatabaseSingletonClass class to connect at the bookstore //database
include_once('DatabaseSingletonClass.php');
//Querying the book table
$result = DatabaseSingletonClass::getInstance()->query("SELECT id, title, author, yearofpublication, publisher, price FROM books");
//The commented lines from below list the records from the books table
//exactly what the Smarty template file index.tpl does in this example
/*
if($result) {
echo("<table border='1'>");
while ($row = mysqli_fetch_row($result)) {
echo("<tr>");
for($i = 0; $i < count($row); $i++) {
echo "<td>".$row[$i]."</td>";
}
echo("</tr>");
}
echo("</table>");
$result->close();
} else { echo("An error occured while processing!"); }
*/
//Include the Smarty.class.php
include_once('D:/Apache2.2/htdocs/php/Smarty/libs/Smarty.class.php');
//Create new variable $smarty from the class Smarty
$tpl = new Smarty;
//list the book table fields
echo'<table cellpadding="4" border="1" width="500" align="center"><tr>';
echo '<td style="border-top:3px #E6E6E6 solid; border-bottom:3px #595959 solid; border-right: 3px #595959 solid;
border-left:3px #E6E6E6 solid;">Id</td><td style="border-top:3px #E6E6E6 solid; border-bottom:3px #595959 solid;
border-right: 3px #595959 solid; border-left:3px #E6E6E6 solid;">Title</td><td style="border-top:3px #E6E6E6 solid;
border-bottom:3px #595959 solid; border-right: 3px #595959 solid;
border-left:3px #E6E6E6 solid;">Author</td><td style="border-top:3px #E6E6E6 solid; border-bottom:3px #595959 solid; border-right: 3px #595959 solid;
border-left:3px #E6E6E6 solid;">Yearofpublication</td><td style="border-top:3px #E6E6E6 solid; border-bottom:3px #595959 solid;
border-right: 3px #595959 solid; border-left:3px #E6E6E6 solid;">Publisher</td><td style="border-top:3px #E6E6E6 solid;
border-bottom:3px #595959 solid; border-right: 3px #595959 solid; border-left:3px #E6E6E6 solid;">Price</td></tr>';
//Iterate over the book table rows
while($row = mysqli_fetch_row($result))
{
//Assign each row to the $row array
$tpl->assign('row', $row);
//Compile and display output of the template file
$tpl->display('index.tpl');
}
echo '<br /><br />';
?>
You may notice from the listing below that to iterate an array I used the {section},{sectionelse} syntax. A {section} is for looping over sequentially indexed arrays of data, unlike {foreach}, which is used to loop over a single associative array. Every {section} tag must be paired with a closing {/section} tag. You can learn more about Smarty loop functions from the site documentation. This is the template file index.tpl:
<tr>
{section name=i loop=$row}
<td>{$row[i]}</td>
{sectionelse}
</tr>
<tr><td><b><br>Array $row has no entries</b></td></tr>
{/section}
After putting everything together, the output of the database application using Smarty template engine is shown in Figure 4.
Click here for larger image
Figure 4. The Output of Our Database Complex Application
Conclusion
In this article you have seen how to install and use the Smarty template engine for PHP 5 and how to interact with a database.
