DTML Basics (part 3) - Bringing In The Database (
Page 5 of 6 )
Another common
application of sequences involves using them in combination with database result
sets to break up a large result set into smaller batches.
Consider the following example, which invokes a ZSQL Method (for the uninitiated,
a ZSQL Method is a special Zope object that allows you to communicate with a database
- read more about it at (
http://www.devshed.com/Server_Side/Zope/ZopeWithApache). In the following example, the GetUsers() method retrieves a list of users
from a database
select * from person
as a sequence and uses a DTML loop to iterate through it and print the data.
<table cellpadding="5" cellspacing="0" border="1" width="250"> <tr
bgcolor="#CFCFCF">
<td align="left"><b>User Details</b></td> </tr>
<dtml-in GetUsers
sort=FirstName> <tr> <td align="left"><font
size="2"><b><dtml-var
sequence-number>. <dtml-var
Title> <dtml-var FirstName> <dtml-var Surname></b></font><br>
<font
size="1"><i><dtml-var Email></i><br><dtml-var Tel><br><dtml-var
Fax></font></td>
</tr>
</dtml-in>
</table>
The output displays all the records in a single page, as shown below.
This example uses the <dtml-in> tag to loop over the result set returned by
the GetUsers() ZSQL Method. The rest of the code is good ol' HTML to make the
page look pretty.
Depending on the number of records in the table, you can rest assured that this
is a definite no-no as far as the usability and performance of the site is concerned.
A better idea would be to split this result set into multiple "pages", so as to
reduce overhead and also to make the site more usable. Here's the code:
<table cellpadding="5" cellspacing="0" border="1" width="250"> <tr
bgcolor="#CFCFCF">
<td align="left"><b>User Details</b></td> </tr>
<dtml-in GetUsers
size="6" start=start_here sort=FirstName>
<dtml-if sequence-start>
<dtml-if
previous-sequence>
<tr><td align="right"><font size="2">[ <a href="<dtml-var
URL><dtml-var
sequence-query>start_here=<dtml-var previous-sequence-start-number>">
Previous
<dtml-var previous-sequence-size> users</a> ]</font></td></tr>
<dtml-else>
<tr><td align="right"> </td></tr> </dtml-if>
</dtml-if>
<dtml-if
sequence-even>
<tr bgcolor="#DCDCDC">
<dtml-else>
<tr bgcolor="#CECECE">
<tr>
</dtml-if>
<td
align="left"><font size="2"><b><dtml-var sequence-number>. <dtml-var
Title>
<dtml-var FirstName> <dtml-var Surname></b></font><br>
<font
size="1"><i><dtml-var Email></i><br><dtml-var Tel><br><dtml-var
Fax></font></td>
</tr>
<dtml-if
sequence-end>
<dtml-if next-sequence>
<tr><td align="right"><font
size="2">[ <a href="<dtml-var URL><dtml-var
sequence-query>start_here=<dtml-var
next-sequence-start-number>"> Next
<dtml-var next-sequence-size> users</a>
]</font></td></tr> <dtml-else>
<tr><td align="right"> </td></tr>
</dtml-if> </dtml-if>
</dtml-in>
</table>
Phew! That's a lot of code...but the result is worth it.
Take a close look at the output. No scroll bar. Neat little links to the next
and previous "batch" of users. Just what I wanted.
