Page 1 of 1

The most simple way to run a voting system.

Posted: Mon Sep 18, 2006 6:54 am
by impulse()
My plan is to start with multiple of radio buttons each corresponding to their vote.
Would it be best once the values have been submitted to store them in a MySQL DB, adding 1 to the total of votes for that option.
EG -

Question - What's your favourite vowel?
Options - A, E, I, O, U.

If I select A then it adds 1 to the field 'A' in A DB and then to output the total of votes by querying the int in the MySQL DB?

Regards, Stephen

Posted: Mon Sep 18, 2006 8:17 am
by GM
Your way would work, but it's not very flexible or expandable. For a more complete voting system, that allows multiple questions to vote on etc., probably it's best to store the question number and the id of the answer given by the voter eg:

Code: Select all

CREATE TABLE question (
id_question int(5) not null auto_increment,
description_question varchar(255) not null,
primary key (id_question));

CREATE TABLE answer (
id_question int(5) not null,
id_answer int(2) not null auto_increment,
description_answer varchar(255) not null,
primary key(id_question, id_answer))
engine='myISAM';

CREATE TABLE vote (
id_user int(8) not null,
id_question int(5) not null,
id_answer int(2) not null,
primary key (id_user, id_question));

CREATE INDEX idx_vote ON vote (id_question, id_answer);
The id_user in the vote table is to prevent double voting (could be simply an IP Address, could be an actual log-in id)

To get the number of votes out, you'd do a simple

Code: Select all

SELECT q.description_question, a.description_answer, count(v.id_answer) as votes
FROM question q LEFT JOIN answer a 
   ON q.id_question = a.id_question
LEFT JOIN vote v 
   ON v.id_question = a.id_question AND v.id_answer = a.id_answer
WHERE q.id_question = ***id of question***
GROUP BY q.description_question, a.description_answer;
Some test data:

Code: Select all

INSERT INTO question VALUES (NULL, 'What is your favourite vowel?');

INSERT INTO answer VALUES (1, NULL, 'a'), (1, NULL, 'e'), (1, NULL, 'i'), (1, NULL, 'o'), (1, NULL, 'u');

INSERT INTO vote VALUES (1, 1, 1), (2, 1, 4), (3, 1, 4), (4, 1, 2), (5, 1, 5), (6, 1, 4), (7, 1, 4), (8, 1, 2), (9, 1, 2), (10, 1, 1);
and the query outputs:

Code: Select all

+-------------------------------+--------------------+-------+
| description_question          | description_answer | votes |
+-------------------------------+--------------------+-------+
| What is your favourite vowel? | a                  |     2 |
| What is your favourite vowel? | e                  |     3 |
| What is your favourite vowel? | i                  |     0 |
| What is your favourite vowel? | o                  |     4 |
| What is your favourite vowel? | u                  |     1 |
+-------------------------------+--------------------+-------+