Despite what you might think, it's fairly easy to write MySQL-based Web applications with Zope. In this case study, find out how to do just that by building a Zope-based online poll which uses MySQL to store poll questions and user responses, and DTML Methods to extract and present this information.
Once the form is submitted, the "addUserResponse" method takes over to process the vote.
<dtml-var standard_html_header>
<dtml-if expr="REQUEST.has_key('submit')
and
REQUEST.has_key('response')and REQUEST.has_key('qid')">
<dtml-call insertUserResponse>
Thank
you for voting. Here are the results so far:<p>
<dtml-in getPollResults>
<table
border=0 cellspacing=0 cellpadding=5>
<tr><td colspan=3><b><dtml-var
question></b></td></tr> <tr><td><dtml-var
response1></td><td>
<dtml-var votes1> </td></tr> <tr><td><dtml-var
response2></td><td>
<dtml-var votes2> </td></tr> <tr><td><dtml-var
response3></td><td>
<dtml-var votes3> </td></tr> <tr><td><font
size=-2>Posted on
<dtml-var date fmt="%d/%m/%Y"> </font></td><td><font
size=-2><dtml-var
expr="votes1 + votes2 + votes3"> total
votes</font></td></tr>
</table><p>
</dtml-in>
<dtml-else>
<h4><a
href="viewCurrentPoll">Try again!</h4>
</dtml-if>
<dtml-var standard_html_footer>
Now. this probably looks waaaaay complicated. But take a closer look:
<dtml-if expr="REQUEST.has_key('submit') and
REQUEST.has_key('response')and REQUEST.has_key('qid')">
The first step is to check that the form was correctly submitted. This is accomplished by verifying the presence of the required form variables via the has_key() method of the REQUEST object; this method returns true if the form variable exists.
Next, the "insertUserResponse" Z SQL Method is invoked; this method increments the vote count for that particular question with the user's choice. Here's what it looks like:
UPDATE poll SET <dtml-var expr="'votes' + response"> = <dtml-var
expr="'votes' + response"> + 1 WHERE qid = <dtml-sqlvar qid type="int">
Here, I've used a combination of DTML expressions as well as good ol' SQL to do the work. The statement above dynamically creates the name of the table field to be updated, and increments its value by one. This is one of the advantages of using Zope with MySQL - you can do fairly complicated things with the application's business logic without affecting the presentation layer.
Once the database has been updated, it's only polite to show the user the current vote statistics.
Once again, the <dtml-in> construct has been used to call the "getPollResults" Z SQL Method and process the returned result set. This method takes a single argument - the question ID - and fetches the details for the corresponding poll.
SELECT question, response1, response2, response3, votes1, votes2,
votes3, date from poll WHERE qid = <dtml-sqlvar qid type="int">
