Creating a Voting Poll With PHP And MySQL Part 1/2 - Creating the database

(Page 2 of 5 )

All of the polls questions and answers will be stored in a MySQL database. Our database will contain two tables: one to store the voting questions, and the other to store our visitor's responses to those questions.

Start the MySQL console application (located in c:\mysql\bin by default for Windows users, and /usr/local for Linux/Unix users) and enter the following commands to both create and connect to our voting polls new database:

create database polls;

use polls;

Next, we want to create the table to store the voting polls questions. Use the following MySQL commands to do so:

create table pollQuestions

(

pk_Id int auto_increment not null,

question varchar(100) not null,

answer1 varchar(50) not null,

answer2 varchar(50) not null,

answer3 varchar(50) not null,

answer4 varchar(50) not null,

answer5 varchar(50) not null,

primary key(pk_Id),

unique id(pk_Id)

);

We have just created a new table named "pollQuestions". The details of each of the table’s fields are shown below:

• pkId: An auto incrementing number, which will allow us to store a unique reference to each specific voting poll question.
• question: The question that all votes for this poll will be gathered from. The question can be anything you like, just as long as it's 100 characters or less in length, for example: "What do you like most about devArticles.com?" or "Which book will you purchase this week".
• answer1...5: These fields represent the answers that the users will be able to choose for this poll question. Each of our voting questions can have a maximum of five answers.

In our "create table..." statement, we also declare the pkId field as a primary key and as a unique identity. Let's now create the table to store our visitor's answers for the poll questions:

create table pollAnswers

(

pk_Id int auto_increment not null,

pollId int not null,

answer varchar(50) not null,

visitorIP varchar(15) not null,

primary key(pk_Id),

unique Id(pk_Id)

);

The descriptions of the fields in our pollAnswers table are shown below:

• pkId: An auto incrementing number, which will allow us to store a unique reference to each users poll answers.
• pollId: The primary key of the poll that this vote is for (i.e. the pk_Id field in the pollQuestions table).
• answer: The visitors answer to the question with a primary key of pollId.
• visitorIP: The IP address of the visitor who placed this vote. Storing the visitors IP address is one way to stop the same visitor voting more than once. More on this later.

As with the pollQuestions table, we declare the pkId field as a primary key and as a unique identity. Use the "describe pollQuestions;" and "describe pollAnswers;" MySQL commands to make sure that both tables were created OK:



Before I move onto the next section, let's take a look at the relationship between the pollQuestions table and the pollAnswers table:



As you can see from the diagram above, the pollId field in the pollAnswers table holds a value that matches the pk_Id field of the poll that the vote is for. Also, the value of the answer field in the pollAnswers table can be either of the values stored in the answer1...5 fields for the poll in the pollQuestions table.

Next: Adding a poll to the database >>
 

More MySQL Articles
More By Mitchell Harper

Comments On: Creating a Voting Poll With PHP And MySQL Part 1/2

· This is a nice tutorial. Exactly what I am looking to do.Only problem is, it...   >>> Post your comment now!