JVM Multimedia et al have built two libraries that provide a nice layer between your PHP code and MySql.
The first is ez_sql. As you can imagine it provides a layer of ease for calling out to mysql functions. If you have done much with mysql you have probably written something like this already.
The second is ez_results. Now that you can access the database data, how do you do nice things like provide pagination to your end users? Again, you may have ended up hacking something that does this already.
I have used both in the past and was wondering how to merge them into a Smarty implementation. Smarty wants to own the page interactions. ez_results wants to own the database interaction to get the data needed for a page.
It turns out the guys who wrote both provided an implementation that works with Smarty out of the box. (BTW, I can no longer find the code on their site. They have a new CRM they are developing and I can no longer find the reference in there - http://www.woyano.com/topic/ezsql)
We start out with a pretty standard php page:
<?php
//step 1
define(EZSQL_DB_USER, DB_USER);
define(EZSQL_DB_PASSWORD, DB_PASSWORD);
define(EZSQL_DB_NAME, DB_DATABASE);
define(EZSQL_DB_HOST, DB_HOST);
include_once "ez_sql.php";
include_once "ez_results.smarty.php";
...
//step 2
$sql = "SELECT x, y, z FROM table";
$smarty->assign('DB_OUTPUT', $ezr->get_results($sql));
$smarty->assign('RESULTS', $ezr->get_navigation());
$smarty->assign('PAGINATION', $smarty->fetch('pagination.tpl'));
$smarty->assign($vars); //other variables used on the smarty template
...
$smarty->display('main.tpl');
?>
The page has two simple steps:
- Step 1 sets up Smarty (not included in this sample), defines datbase properties, and includes ez_esql and ezs (ez_results for smarty)
- Step 2 sets up the values needed for ezs and starts smarty
At this point Smarty is in control of the page handing.
The pagination.tpl is very simple. This file is very flexible so you can change it's stylesheet aspects without affecting any of the functionality.
<?php
<table border="0" cellspacing="0" cellpadding="3">
<tr>
<td><b>Page:</b>
{$RESULTS.current_page} of {$RESULTS.num_pages}
</td>
<td>({$RESULTS.num_records} items)</td>
<td>{$RESULTS.first_page}</td>
<td>{$RESULTS.prev}</td>
<td>{$RESULTS.nav}</td>
<td>{$RESULTS.next}</td>
<td>{$RESULTS.last_page}</td>
</tr>
</table>
?>
The RESULTS object is defined in the prior call out to ez_results.
My home.tpl looks like:
<?php
<table width="100%" cellspacing="5" cellpadding="5" border="0">
<tr>
<th><i>Id</i></th>
<th><i>Title</i></th>
<th><i>City</i></th>
<th><i>State</i></th>
<th><i>Edit</i></th>
<th><i>Delete</i></th>
</tr>
{foreach from=$DB_OUTPUT item=job key=key}
<tr bgcolor="#e5edf6">
<td>{$job.identifier}</td>
<td>{$job.title|capitalize}</td>
<td>{$job.city|capitalize}</td>
<td>{$job.state_id|capitalize}</td>
<td><a href="/admin.php?action=edit&id={$job.job_id}">Edit</a></td>
<td><a href="/admin.php?action=delete&id={$job.job_id}">Delete</a></td>
</tr>
{foreachelse}
<tr>
<td colspan="6" align="center">Sorry, there are no listings available</td>
</tr>
{/foreach}
</table>
{$PAGINATION}<br/>
?>
The variable DB_OUTPUT is defined in step 1 above. PAGINATION is really just the pagination.tpl template. As you can see, the pagination 'control' can be placed anywhere on the page.
I am putting the code at http://www.oxyscripts.com/ez_results_smarty/ezrs.zip (Again, since I can't find the reference on their site anymore)
Note, I have adjusted the ez_sql.php provided to have the database field definitions defined outside of ez_sql.php. This allows me to re-use ez_sql.php in other sites without changing anything in the guts. |
