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
The most simple way to run a voting system.
Moderator: General Moderators
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:
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
Some test data:
and the query outputs:
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);
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;
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);
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 |
+-------------------------------+--------------------+-------+