SQL - ColdFusion Poll

[burningsoul]

I am currently working on creating a poll for my website (

http://www.burningsouls.com),

however, it's a bit more than a poll, what i call a 'top ten'.

the idea behind it is for people to be able to vote for their favorite album, song and video. i want to display all 3 voting options on the same page dynamically using a Microsoft Access DB. My trouble is writing the correct SQL statement to join the two tables together so I can query and display all 3 questions with their associated answers (which will be in a drop-down box).

I have created two tables: toptenQuestions and toptenAnswers

toptenQuestions:
QuestionID (autonumber, PK)
Question (text)

toptenAnswers:
AnswerID (autonumber, PK)
QuestionID (number, FK)
Answer (text)
Vote (number, to hold the vote count)

As mentioned, my trouble is creating a query that will read the data from the toptenQuestions table and at the same time the toptenAnswers table. I then want to be able to reference the query on the page and list 1) the question for each QuestionID and 2) the associated answers for each question in a drop-down box.

When submitted to results.cfm the query will update the Vote field depending on what the user chose for each question.

Thoughts are greatly appreciated!

 

[burningsoul]

If I may reply to my own question. lol

After some hours of reading and trial and error I've been able to set up a single query to display each questions with a drop down box of answers.

The SQL statement I used was as follows:

<cfquery name="getQuestionsAndAnswers" datasource="burningsouls">
SELECT toptenQuestions.QuestionID, toptenQuestions.Question, toptenAnswers.AnswerID, topTenAnswers.Answer
FROM toptenQuestions INNER JOIN toptenAnswers ON toptenQuestions.QuestionID = toptenAnswers.QuestionID
ORDER BY toptenQuestions.QuestionID, toptenAnswers.Answer
</cfquery>

And the part that really got me turned out to be quite simple using the form:

<cfoutput query="getQuestionsAndAnswers" group="Question">
#Question#<br><br>
<form name="#getQuestionsAndAnswers.QuestionID#" method="post" action="results.cfm">
<select name="answer">
<cfoutput>
<option value="#getQuestionsAndAnswers.AnswerID#">#getQuestionsAndAnswers.Answer#</option>
</cfoutput>
</select>
</form>
</cfoutput>

Learned of the group function in a cfoutput tag, works very nice for this situation!

Now to send the results to the results.cfm page. I think I need to enclose this in one big form, though I don't think you can nest forms? If so I can just grab each form name and mach it to the QuestionID and Answer to update the Vote count.

Sorry if this isn't the right place to put this, thought I should put my thoughts down before heading to bed!

 

[r937]

that's right, you cannot nest forms

use just one form for the entire page, use #getQuestionsAndAnswers.QuestionID# to distinguish the SELECT dropdowns, and loop over these in the action template

r937.com | rudy.ca

 

[burningsoul]

all is well on the form displaying dynamically with the data. now i'm working on processing the form info thought a bit confused.

first, how am i going to be able to retrieve the results from the dynamically created select names? i will have them named 1, 2 and 3 (taken from the QuestionID). when they get sent to be processed, i need a query to find each answer from each select name, however i'm not sure how to do this. do i need to run a cfloop through the query until it is done processing each QuestionID and store them into a temporary var that will then get written to the DB?

i also need to update the vote count, however my math functions aren't very good...currently all vote counts are at 0 (there's a vote column in the answers and they need to be incremented by 1 when a user selects that answer...doing SET Vote = Vote + 1 doesn't seem to work in my query.

 

[r937]

no, the cfloop you need is to loop over the form fields, and within each loop, process the form answer field into the database

name your dropdown like this:

<select name="answer#QuestionID#">

so the form fields are called answer36, answer37, etc.

then in the action page, loop over the form fields like this --

<CFLOOP COLLECTION="#form#" ITEM="foo">
<CFIF Left(LCase(foo),6) EQ "answer">
<CFSET questionid = Trim(Mid(foo,7,3))>
<CFQUERY ...>
insert into ...
values ( ... #questionid#, #form[foo]# )
</CFQUERY>
</CFIF>
</CFLOOP>


F.Y.I. this was the microsoft sql server forum -- questions about microsoft access should be posted in one of the microsoft access forums, and questions about coldfusion should be posted in the coldfusion forum


r937.com | rudy.ca