Setting up a poll database
I'm trying to create a poll. Since I want to use radio buttons on the form, I'm creating one field for each category of information that uses the numbers 1 through 5 to allow the user to make one selection only in each category. The problem I'm having is figuring out how I'm going to count the responses and display them. If each entry were in a different db field, I could do a count on each field and only use one SELECT query. But with different numbers in the same field, all I can think of is to use multiple queries with each one having its own WHERE clause. This seems like poor form. Does anyone have advice on how to proceed? If I get into thousands of responses, I don't want the queries to bog down. If you suggest structures, please assume I know nothing about them (which is true, BTW). TIA, --John ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Setting up a poll database
Hi John, Here's some code a guy in our office gave me a while back for this: For example, if I was retrieving the responses from a Yes/No survey, I could run the following: SELECT column , Count(*) FROM table GROUP BY column The results could be: column --- Yes 432 No 165 Best, John -Original Message- From: John Allred [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 17, 2000 1:02 PM To: CF-Talk Subject: Setting up a poll database I'm trying to create a poll. Since I want to use radio buttons on the form, I'm creating one field for each category of information that uses the numbers 1 through 5 to allow the user to make one selection only in each category. The problem I'm having is figuring out how I'm going to count the responses and display them. If each entry were in a different db field, I could do a count on each field and only use one SELECT query. But with different numbers in the same field, all I can think of is to use multiple queries with each one having its own WHERE clause. This seems like poor form. Does anyone have advice on how to proceed? If I get into thousands of responses, I don't want the queries to bog down. If you suggest structures, please assume I know nothing about them (which is true, BTW). TIA, --John ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Setting up a poll database
I just finished helping a user on my site with a similiar project. Maybe you can glean something from that? http://cfhub.com/discussion/viewmessages.cfm?Forum=11Topic=90 The idea there was simply have one table with the "Poll Questions" and one table with "Poll Answers". The answer table had answers and a foreign key pointing to the question it belonged to. The form allowed an "unlimited" number of "Poll Answers" but checks that there are at least two. IE; Poll Question: Why is the sky blue? Answer1 Because pink would be tacky. Answer2 Who Cares? Answer3 ...etc... I'm trying to create a poll. Since I want to use radio buttons on the form, I'm creating one field for each category of information that uses the numbers 1 through 5 to allow the user to make one selection only in each category. The problem I'm having is figuring out how I'm going to count the responses and display them. If each entry were in a different db field, I could do a count on each field and only use one SELECT query. But with different numbers in the same field, all I can think of is to use multiple queries with each one having its own WHERE clause. This seems like poor form. Does anyone have advice on how to proceed? If I get into thousands of responses, I don't want the queries to bog down. If you suggest structures, please assume I know nothing about them (which is true, BTW). TIA, --John ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Setting up a poll database
John, Thanks for the quick reply. I can get this query working in the query builder, but I can't figure out how to display it. It looks like a structure. A little more help? Thanks, --John "John J. Kirker" wrote: Hi John, Here's some code a guy in our office gave me a while back for this: For example, if I was retrieving the responses from a Yes/No survey, I could run the following: SELECT column , Count(*) FROM table GROUP BY column The results could be: column --- Yes 432 No 165 Best, John -Original Message- From: John Allred [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 17, 2000 1:02 PM To: CF-Talk Subject: Setting up a poll database I'm trying to create a poll. Since I want to use radio buttons on the form, I'm creating one field for each category of information that uses the numbers 1 through 5 to allow the user to make one selection only in each category. The problem I'm having is figuring out how I'm going to count the responses and display them. If each entry were in a different db field, I could do a count on each field and only use one SELECT query. But with different numbers in the same field, all I can think of is to use multiple queries with each one having its own WHERE clause. This seems like poor form. Does anyone have advice on how to proceed? If I get into thousands of responses, I don't want the queries to bog down. If you suggest structures, please assume I know nothing about them (which is true, BTW). TIA, --John ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Setting up a poll database
Webtricks has some source code available for creating a poll. http://www.webtricks.com/SourceCode/ Dave Bosky -Original Message- From: Joseph Thompson [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 17, 2000 4:28 PM To: CF-Talk Subject: Re: Setting up a poll database I just finished helping a user on my site with a similiar project. Maybe you can glean something from that? http://cfhub.com/discussion/viewmessages.cfm?Forum=11Topic=90 The idea there was simply have one table with the "Poll Questions" and one table with "Poll Answers". The answer table had answers and a foreign key pointing to the question it belonged to. The form allowed an "unlimited" number of "Poll Answers" but checks that there are at least two. IE; Poll Question: Why is the sky blue? Answer1 Because pink would be tacky. Answer2 Who Cares? Answer3 ...etc... I'm trying to create a poll. Since I want to use radio buttons on the form, I'm creating one field for each category of information that uses the numbers 1 through 5 to allow the user to make one selection only in each category. The problem I'm having is figuring out how I'm going to count the responses and display them. If each entry were in a different db field, I could do a count on each field and only use one SELECT query. But with different numbers in the same field, all I can think of is to use multiple queries with each one having its own WHERE clause. This seems like poor form. Does anyone have advice on how to proceed? If I get into thousands of responses, I don't want the queries to bog down. If you suggest structures, please assume I know nothing about them (which is true, BTW). TIA, --John ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
