A poll

[SharkBait]

I am looking at coding up a poll that will take a maximum of 5 answers.

 

Now I'm trying to think of how to handle the database portion of this and wondering will two tables be enough. One that holds the title and choices and then another table to hold the vote?

 

tbl_poll

----------------

id  |  question |  option1  |  option2  |  option3  |  option4  |  option5  | date_started

 

tbl_poll_answers

----------------

id  |  poll_id  | answer | ip | date_voted

 

 

I would use the tbl_poll_answers to limit the person from voting more than once (yes I know people can spoof the IP but this isn't critical).

 

I would link the two tables based on: tbl_poll_answers.poll_id = tbl_poll.id 

 

Now when the user voted for an option what is the best way to handle that portion? in tbl_poll_answsers.answer would I putt in the exact text I have in tbl.poll_{WhateverOptonTheyChoose} ? or am I missing a field or two to get the answer to link up with the question?

 

 

[utexas_pjm]

I would do:

 

tbl_poll

----------------

tbl_poll_id  |  question | date_started

 

tbl_poll_options

----------------

tbl_poll_option_id  |  option_string

 

tbl_poll_to_option

----------------

tbl_poll_to_option_id  | tbl_poll_id  | tbl_poll_option_id

 

tbl_poll_answers

----------------

tbl_poll_answer_id  | tbl_poll_option_id | ip | date_voted

 

That way you're not limiting the number of options any poll can have.  You can query the results like:

 

SELECT options.option_string, COUNT(answers.*)

FROM tbl_poll AS poll

  INNER JOIN tbl_poll_to_option AS option_map USING (tbl_poll_id)

  INNER JOIN  tbl_poll_options AS options USING (tbl_poll_option_id)

  LEFT JOIN tbl_poll_answers AS answers USING(tbl_poll_option_id)

WHERE poll.tbl_poll_id = '1'

GROUP BY answers.tbl_poll_option_id

 

Best,

 

Patrick

[SharkBait]

Ok I see what you mean.

 

Have all seperate tables and have one table the joins the question to the options.

 

 

I'll look into using something like that. Thanks.

[SharkBait]

Ah ok I finished building it.

 

Thanks for the table structure I used something really similar, but used 3 tables instead.

 

I have some tweaking to do but it was fun creating it!

 

You can view it on my blog: http://www.tyleringram.com/blog/PHP-and-MySQL-Poll-Based-System