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