SQL Poll Question in JSP

[Mike Spenser]

I'm trying to create a simple polling system in a JSP with a database backend.
The basic table structure is
+-----------+
| Id |
|-----------|
| Question |
|-----------|
| Answer1 |
|-----------|
| Answer2 |
|-----------|
| Answer3 |
|-----------|
| Answer4 |
|-----------|
| Answer5 |
+-----------+

I manually specify the id for the question/answer set in question in the sql:query tag.

My question is how do I iterate through the 5 possible ansers for creating a bunch of radio buttons?

This is what I have right now but its not working.

    <sql:query var="res" dataSource="jdbc/MysqlPool">
        SELECT * FROM `pollQuestions` WHERE pk_Id = 1
    </sql:query>
    <c:forEach var="row" items="${res.rows}">
        <h1><c :o ut value="${row.QUESTION}" /></h1>
    </c:forEach>
    
    <form name="poll" action="" method="POST" >
    
    <%! int count=1; %>
    <c:forEach var="row" items="${res.rows}">
        <%= ++count %>
        <input type="radio" name="question_group" value="<c :o ut value="${row.ANSWER + count}" />">
    </c:forEach>
    </form>

Before anyone mentions it, yes I realize including the SQL tags is breaking with MVC patterns, but I'm just starting, and don't want to overcomplicate things.

[ July 21, 2006: Message edited by: Mike Spenser ]
[ July 21, 2006: Message edited by: Mike Spenser ]

[Bear Bibeault]

Originally posted by Mike Spenser:
but I'm just starting, and don't want to overcomplicate things.

Not to take this where you obviously don't want to go, but starting out doing things the wrong way doesn't seem to be over-complicating things to me.

The very fact that you're here asking questions indicates that doing SQL in the JSP page isn't saving you any time.

Now back to our regularly scheduled program.

[Paul Clapham]

You didn't say what the symptoms of "not working" were but let me start by questioning your question (as well):

My question is how do I iterate through the 5 possible questions...

But your picture shows 5 possible answers. I suppose these 5 answers aren't in a separate table, you have a repeating group in your table and the column names are ANSWER1, ANSWER2, and so on? I don't know of any way to fiddle with the SQL tags to get to there, but what's wrong with

<input type="radio" name="question_group" value="<c:out value="${row.ANSWER1}" />">
<input type="radio" name="question_group" value="<c:out value="${row.ANSWER2}" />">
<input type="radio" name="question_group" value="<c:out value="${row.ANSWER3}" />">
<input type="radio" name="question_group" value="<c:out value="${row.ANSWER4}" />">
<input type="radio" name="question_group" value="<c:out value="${row.ANSWER5}" />">

[Mike Spenser]

I did mean answers, I corrected the post.
I was just trying to make the code a bit simplier, but that works too.

[Ben Souther]

This is going to cause a big nasty bug when you start getting multiple concurrent hits too:

<%! int count=1; %>

Search this thread with keywords "instance variable" and "thread safe" for more information on why this is a bad way to declare variables in JSPs.

[William Brogden]

I have implemented online question/poll systems many times. Relational databases may be great for some things but they are a horrible way to store and access question data. (IM-not so-HO)
Why? Because they are so inflexible - XML works well for flexible creation of questions.
Bill

[Charles Lyons]

I have implemented online question/poll systems many times. Relational databases may be great for some things but they are a horrible way to store and access question data. (IM-not so-HO) Why? Because they are so inflexible - XML works well for flexible creation of questions.

You can also include the XML data in your database. You'd normally have to write a parser to deal with the XML, but for some time I've been working with Java's XML serialisation and it works really well and is very straightforward to start using... all you need to do is:

(1) Create a bean class (say PollBean) to store your poll data in. For example, containing the fields "question" (String), "answers" (String[]).

(2) Use java.beans.XMLEncoder to serialise a PollBean instance containing all the required data into XML. The instance can be obtained by completing an admin form or from some other console (or indeed written by hand once you see the format).

(3) Use java.beans.XMLDecoder to deserialise the object from the file/database when you're ready to read its contents (e.g. on each loading of the page). This will automatically extract all the data from your XML source into a new bean, which will then 'look like' the original bean.

Now you have a PollBean instance in the JSP, so you can easily use EL to manipulate your bean. For example, to write out all the answers just use the JSTL <c:forEach> on its "answers" property.

One final comment: you have incorrectly implemented this bit:

<%= ++count %>
<input type="radio" name="question_group" value="<c:out value="${row.ANSWER + count}" />">

This will not work because "count" here is a JSP scripting variable and not a scoped attribute. It is therefore not accessible from your EL code. You would therefore instead want to set the page-scoped attribute through the pageContext, or alternatively use <c:set> like:

<c:set var="count" value="${ count + 1 }" />

Better still, you needn't do any of that, you can simply use the "varStatus" attribute of <c:forEach>:

<c:forEach var="row" items="${res.rows}" varStatus="status">
<input type="radio" name="question_group" value="<c:out value="${row.ANSWER + status.count}" />">    
</c:forEach>

Note that "status.count" is 1-based, so you don't need to initialise any variables, and you don't need to use scripting elements.