The Number Game

Build a Zope-based online poll with MySQL and DTML.

Keeping It Simple

They say that the best ideas are the simple ones - and the adage is as true in the realm of the Web as anywhere else. At the end of the day, it doesn't matter which operating system your Web site is built on, or how many buzzwords you throw about in your conversations - if you want your Web portal to be successful, you need to get back to the basics, and build in features that will keep your members coming back for more.

One of the simplest things you can add to your Web portal is an online poll, one designed to elicit responses and reactions from members to the hot-buttons issues of the day. It might seem trite (not to mention common), but it's still one of the most effective methods of promoting a sense of community amongst a portal's members, and of generating demographic data on a site's visitors.

Building an online poll isn't very hard; as a matter of fact, you can slap one together in a couple of hours. And you won't even need a degree in rocket science to accomplish this feat - Zope makes the process so simple and friendly you'll wonder why you waited so long to add it. Keep reading, and I'll show you how.

Reading The Funnies

Since I'm going to be using a MySQL database to store the poll's questions and answers, together with user responses, the first task is to design the relevant tables. Here's what I came up with:

#
# Table structure for table 'poll'

CREATE TABLE poll (
   qid int(10) unsigned NOT NULL auto_increment,
   question varchar(255) NOT NULL,
   response1 varchar(255) NOT NULL,
   response2 varchar(255) NOT NULL,
   response3 varchar(255) NOT NULL,
   votes1 int(10) unsigned DEFAULT '0' NOT NULL,
   votes2 int(10) unsigned DEFAULT '0' NOT NULL,
   votes3 int(10) unsigned DEFAULT '0' NOT NULL,
   date date DEFAULT '0000-00-00' NOT NULL,
   PRIMARY KEY (id)
);

#
# Column descriptions:
#
# qid - a unique identifier for each poll question
# question - the poll question
# response1 - possible response #1
# response2 - possible response #2
# response3 - possible response #3
# votes1 - number of votes for response #1
# votes2 - number of votes for response #2
# votes3 - number of votes for response #3
# date - date on which poll was posted
#

Just to get things started, I also INSERTed the first question into the database, together with three possible responses.

#
# Dumping data for table 'poll'
#

INSERT INTO poll (qid, question, response1, response2, response3, votes1,
votes2, votes3, date) VALUES ( '1', 'The coolest comic strip of all time is', 'Dilbert', 'Peanuts', 'Calvin and Hobbes', '0', '0', '0',
'2002-03-07');

At this point, it's also a good idea to verify that your Zope installation supports MySQL via the Z MySQL Database Adapter. In case it doesn't, drop by http://www.melonfire.com/community/columns/trog/article.php?id=116, which tells you how to install this adapter - the remainder of this article assumes that you have a working Zope/MySQL installation.

Start Me Up

With the database taken care of, it's time to get started with the user interface for our application. First things first - create a database connection as an instance of the Z MySQL Database Connection object and pass it the appropriate database name, user name and password. Remember that you can use the object's Test function to verify that the connection is working as it should.

Now, the first DTML method needed is "viewCurrentPoll"; this invokes a Z SQL Method that connects to the database to retrieve the latest poll, and displays it with a list of possible responses. Take a look:

<dtml-var standard_html_header>

<dtml-in getCurrentPollDetails>
<form action="addUserResponse">
<b><dtml-var question></b>
<p>
<input type="Radio" name="response" value="1"><dtml-var response1>
<p>
<input type="Radio" name="response" value="2"><dtml-var response2>
<p>
<input type="Radio" name="response" value="3"><dtml-var response3>
<input type="hidden" name="qid" value="&dtml-qid;">
<p>
<font size=-2><a href="viewPollResults?id=&dtml-qid;">view results</a></font>
<font size=-2><a href="viewPollResults">view past polls</a></font>
<p>
<input type=submit name=submit value="Vote">
</form>
</dtml-in>
<dtml-var standard_html_footer>

This DTML Method merely contains some plain-vanilla code to display a simple form to the user. The values in the form are fetched via the "getCurrentPollDetails" Z SQL Method. Here's what it looks like:

SELECT qid, question, response1, response2, response3 from poll ORDER BY qid DESC

Pay special attention to the SQL query that I've used here - I'm using the ORDER BY and DESC keywords to get all the records in descending order, so that the latest question appears first in the result set. Obviously, I also need to limit the result set to a single record - which can easily be done by hitting the advanced configuration for the Z SQL Method and setting the maximum number of rows to be retrieved to 1. In case you're wondering why I didn't just add a LIMIT clause to the SQL query above, there's a simple reason - Zope's MySQL database adapter doesn't support the use of LIMIT clauses in SQL statements (http://www.zope.org/Members/adustman/Tips/no_limits has more on this).

The <dtml-in> construct is used to iterate through the results of the Z SQL Method invoked, and the form, once submitted, invokes the "addUserResponse" method to add the user's response to the database. Note that the identifier for the poll question is also included in the form, as a hidden field; when the form is submitted; this identifier will be used to ensure that the correct record is updated with the user's choice.

Here's what it looks like:

Rocking The Vote

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.

<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>

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">

Here's what the finished product looks like:

Down Memory Lane

So that takes care of the mechanics of displaying a question, registering votes, and displaying totals. The next item to address is the ability to view an archive of previous polls. The DTML Method to accomplish this is called "viewPollResults", and it looks like this:

<dtml-var standard_html_header>
<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>
<p>
<font size="-2"><a href="viewCurrentPoll">back to main page</a></font>
<p>
<dtml-var standard_html_footer>

This method is extremely simple - it merely calls the "getPollResults" Z SQL Method without any arguments, and iterates through the returned result set to display a list of previous polls.

At this point, you should be thinking to yourself, "...that doesn't compute". This is because a couple of paragraphs ago, I said that the "getPollResults" required the question ID as mandatory argument. And now I'm contradicting myself by saying that I'm going to call the same method without passing it any arguments. Huh?

Well, you're right - calling "getPollResults" without any arguments is sure to make Zope barf. Unless, that is, I alter the Z SQL Method and add a little more intelligence to it, so that it can make a decision about which query to execute based on the presence or absence of the question ID.

<dtml-if qid>
SELECT question, response1, response2, response3, votes1, votes2, votes3, date from poll WHERE qid = <dtml-sqlvar qid type="int">
<dtml-else>
SELECT question, response1, response2, response3, votes1, votes2, votes3, date from poll
</dtml-if>

Simple, isn't it?

Here's what it all looks like:

Cookie-Cutter Code

The way things are currently set up, a single user can vote for a specific option more than once, thereby contravening one of the basic principles of democracy: one citizen, one vote. Not many users would have the patience or inclination to do this; however, it is a hole, and should be plugged.

I've decided to make it slightly more difficult for users to vote more than once by setting a cookie on their system, once their vote has been successfully cast. With the addition of a few lines of script, I can now check for the presence or absence of the cookie, and thereby decide whether or not to accept 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-if expr="REQUEST.has_key('lastpolled') and lastpolled == qid">
       You have already voted, and your vote cannot be accepted again. Here are the results so far:<p>
    <dtml-else>
       <dtml-call "RESPONSE.setCookie('lastpolled', qid, expires='ZopeTime() + 2592000')">
       <dtml-call insertUserResponse>
       Thank you for voting. Here are the results so far:<p>
    </dtml-if>

<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!</a></h4>
</dtml-if>
<dtml-var standard_html_footer>

Once the user votes, a cookie is set on the client browser.

<dtml-if expr="REQUEST.has_key('lastpolled') and lastpolled == qid">
You have already voted, and your vote cannot be accepted again. Here are the results so far:<p>
<dtml-else>
       <dtml-call "RESPONSE.setCookie('lastpolled', qid, expires='ZopeTime() + 2592000')">
       <dtml-call insertUserResponse>
       Thank you for voting. Here are the results so far:<p>
</dtml-if>

Now, on each subsequent vote attempt, the script will first check for the presence of the cookie and, if it exists, the value of the cookie variable "lastpolled". Only if the cookie is absent (indicating that this is a first-time voter) or the value of "lastpolled" is different from the identifier for the current poll question (indicating that the user has voted previously, but in response to a different question) will the vote be accepted.

This is by no means foolproof - any reasonably adept user can delete the cookie from the client's cache and vote more than once - but it does perhaps offer an additional layer of security to the process. The ideal method, of course, is to track voters on the server itself, and deny votes to those who have already voted - and indeed, this is a feasible alternative if your Zope site only allows registered users to access its online polls.

Adding It All Up

The final item on today's menu is perhaps the simplest - a form which allows administrators to easily add new questions to the system.

This DTML method, aptly named "addPoll", is divided into two sections, as seen in the listing below:

<dtml-var standard_html_header>
<h3>Add New Poll</h3>

<dtml-if submit>

<dtml-call insertPoll>
<h4>Poll added successfully!</h4>
<p>
Click here to <a href="viewCurrentPoll">view</a> it.
<p>
<dtml-else>
<table border="0" cellspacing="5" cellpadding="5">

<form action="addPoll" method="post">

<tr>
<td>Poll question<br><input type="Text" name="question" size="25"></td>
</tr>

<tr>
<td>Response 1<br><input type="Text" name="response1" size="25"></td>
</tr>

<tr>
<td>Response 2<br><input type="Text" name="response2" size="25"></td>
</tr>

<tr>
<td>Response 3<br><input type="Text" name="response3" size="25"></td>
</tr>

<tr>
<td align=center><input type=submit name=submit value="Add Poll"></td>
</tr>

</form>
</table>

</dtml-if>

<dtml-var standard_html_footer>

The first task is to check if the form has been submitted. If it has not yet been submitted, then the method generates an empty form, with fields for the question and possible responses. Once the form is submitted, the data entered into the form by the administrator is inserted into the database via the "insertPoll" Z SQL Method below.

INSERT INTO poll (question, response1, response2, response3, date)
VALUES (
<dtml-sqlvar question type="string">,
<dtml-sqlvar response1 type="string">,
<dtml-sqlvar response2 type="string">,
<dtml-sqlvar response3 type="string">,
<dtml-sqlvar ZopeTime type="string">)

Note that the built-in ZopeTime DTML variable (which returns the current system time) has been used to insert the current date as part of the table record. This is similar to the now() function that is built into the MySQL database.

Here's what the form and its result look like:

And that's about it. Hopefully, this exercise gave you some insight into how Zope can be used to build a simple Web application, and illustrated its power and flexibility as a rapid development tool for the Web medium. See ya!

Note: All examples in this article have been tested on Linux/i586 with Zope 2.5. Examples are illustrative only, and are not meant for a production environment. Melonfire provides no warranties or support for the source code described in this article. YMMV!

This article was first published on29 Mar 2002.