Adding a Poll to Your Web Site - Setting the Stage
(Page 2 of 6 )
Let's suppose that Nashville's less known but more pretentious music hall, the Grand Old Opera, has decided to add a poll to their website to measure their visitors' preferences.
The first step is to create the database. For now we will create one table, the poll answers. Later I'll discuss ways to expand the database to handle a multi-question survey or a series of polls.
The following code will create the database and table, insert the choices, and grant permissions for the user.
CREATE DATABASE polls; USE polls; CREATE TABLE poll_answers (choice INT NOT NULL PRIMARY KEY, activity VARCHAR(35) NOT NULL, votes INT DEFAULT 0); INSERT INTO poll_answers(choice, activity, votes) VALUES(1, "Going to the opera", 4), (2, "Listening to opera music on CD", 2), (3, "Getting bitten by a ferret", 14); GRANT SELECT, UPDATE ON polls.* TO user IDENTIFIED BY 'pass';
This creates a database named polls, and a table named poll_answers. This table has three fields: choice, a number representing the choice; activity, a text description of the choice; and votes, the number of votes this choice has received. Later I'll explain how to expand the database to handle multiple polls.
Normally we would initialize the votes field to zero for each choice, but for this tutorial I am using nonzero values to simulate a history of previous votes.
The GRANT statement lets the user with name 'user' and password 'pass' select and/or update rows from the database. In a live environment, you'll want to choose a username and password that would not be so easy for a malicious user to guess.