The most simple way to run a voting system.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

The most simple way to run a voting system.

Post 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
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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 |
+-------------------------------+--------------------+-------+
Post Reply