PHP & MySQL Poll System Tutorial

First we'll create the MySQL Tables for the database. We're using the MySQL database "webcodez" in this example, but you can name it anything you like ( or even use any of your existing mysql databases ). Now we'll create 3 mysql tables in it:

Polls

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE IF NOT EXISTS `polls` (
`id` int(250) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`question` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `polls` (`id`, `name`, `question`) VALUES
(1, 'Test Poll', 'What do you think is the best answer for this poll?');

Answers

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE IF NOT EXISTS `answers` (
`id` int(250) NOT NULL AUTO_INCREMENT,
`poll_id` int(250) NOT NULL,
`answer` varchar(250) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `answers` (`id`, `poll_id`, `answer`) VALUES
(1, 1, 'This is answer 1'),
(2, 1, 'This is answer 2');

Votes

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE IF NOT EXISTS `votes` (
`id` int(250) NOT NULL AUTO_INCREMENT,
`ip` varchar(30) NOT NULL,
`answer_id` int(250) NOT NULL,
`poll_id` int(250) NOT NULL,
`timestamp` int(250) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `votes` (`id`, `ip`, `answer_id`, `poll_id`, `timestamp`) VALUES
(1, '127.0.0.1', 1, 1, 1309261119);

Now it's time to code the actual script. First I'll provide the code and then we'll go through it and the code will be explained.

function showPoll($id = NULL) {

if(is_null($id)) {
$id = (int)$_GET['poll_id'];
}

$get_poll = mysql_query("SELECT * FROM polls WHERE id = $id")or die(mysql_error());
$poll = mysql_fetch_assoc($get_poll);

if(empty($poll)) { //poll not found?
exit();
}

$poll_name = $poll['name'];
$poll_question = $poll['question'];

echo "{$poll_name}";
echo $poll_question." <br />";

if($_POST['vote'] || $_GET['showResults'] == 1) {

$get_votes = mysql_query("SELECT * FROM votes WHERE poll_id = '$id' "); //select all votes to this poll

$votes = array(); //the array that will be containing all votes ( we add them as we retrieve them in a while loop )

$total_votes = 0;

while($vote = mysql_fetch_assoc($get_votes)) { //retrieve them
$answer_id = $vote['answer_id'];
$votes[$answer_id] = (int)$votes[$answer_id]+1; //add 1 vote for this particulair answer
$total_votes++;
}

//now loop through the answers and get their corresponding amount of votes from $votes[id_of_answer]

$get_answers = mysql_query("SELECT * FROM answers WHERE poll_id = '$id' ");

while($answer = mysql_fetch_assoc($get_answers)) { //loop through all answers this poll has

$id1 = $answer['id']; //the id of the answer -> the amount of votes for each answer we stored in $votes[id_of_answer] so for this id it would be $votes[$id1]
$width = round((int)$votes[$id1]/$total_votes*399+1); //400px = max, 100% of votes

echo "<p>".$answer['answer']." (".(int)$votes[$id1]." vote".((int)$votes[$id1] != 1 ? "s":"").")<br /> <div style='background-color: #BABABA; width: {$width} px; height: 150px;"> </div> </p>";

}

echo "<a href='".$_SERVER['PHP_SELF']."?poll_id=".$_GET['poll_id']."'>[Show Poll]</a>";

}else{

$get_answers = mysql_query("SELECT * FROM answers WHERE poll_id = $id ");

echo "<form method='POST' action='".$_SERVER['PHP_SELF']."?poll_id=".$_GET['poll_id']."'>";
while($answer = mysql_fetch_assoc($get_answers)) { //loop through all answers this poll has

echo "<input type='radio' name='answer_id' value='".$answer['id']."'>".$answer['answer']." <br />";

}
echo "<input type='submit' name='vote' value='Vote!' />";
echo "</form>";
echo "<a href='".$_SERVER['PHP_SELF']."?poll_id=".$_GET['poll_id']."&showResults=1'>[Show Results]</a>";

}

if($_POST['vote']) {

$time = time(); //current time
$ip = $_SERVER['REMOTE_ADDR']; //user ip
$answer_id = (int)$_POST['answer_id'];

if($answer_id <= 0) {
die("<p>Invalid answer.</p>");
}

$check_double = mysql_query("SELECT * FROM votes WHERE ip = '$ip' AND poll_id = '$id' "); //check user already voted for this poll

if(mysql_num_rows($check_double) > 0) { //already voted for this poll?
echo "<p>Sorry, you may only vote ONCE for a poll.</p>";
}else{
$vote = mysql_query("INSERT INTO votes(ip, answer_id, poll_id, timestamp)VALUES('$ip', '$answer_id', '$id', '$time')");
if($vote) {
echo "<p>Successfully voted!</p>";
}else{
echo "<p>Error occured.</p>";
}
}

}

}

The first thing the function needs to do now is check whether the poll with the id equal to $id actually exists. We'll use a simple MySQL query to do this and catch the result ( if any found ) in a variable $poll using mysql_fetch_assoc. If no result was found, the variable $poll will be empty and thus we can verify this way whether the poll exists or not. If the poll with the id equal to $id does not exist (if $poll is empty), the function should be stopped with an error message (die('error message')).

$get_poll = mysql_query("SELECT * FROM polls WHERE id = $id");
$poll = @mysql_fetch_assoc($get_poll);

if(empty($poll)) { //poll not found?
die("Error: poll not found.");
}

Note: We put a @ sign before the mysql_fetch_assoc function because this way it will not output an error message if no result was caught/found.

We now have ( if the poll was found ) the info about the poll (id,name,question) stored inside of the variable $poll as an array. Like this:

array("id" => id, "name" => name, "question" => question

As formed in our MySql Database Table 'polls'.

We use this to display the current poll name and question:

$poll_name = $poll['name'];
$poll_question = $poll['question'];

echo "{$poll_name}";
echo $poll_question." <br />";

What we need to display next depends on whether the user has just submitted a vote or the results of the poll were demanded. If any of these 2 cases are true, we will need to display the results of the poll. Otherwise we will want to display the form in which the user can submit a vote or click the link to display the results.

if($_POST['vote'] || $_GET['showResults'] == 1) {
//display results
}else{
//display vote form
}

To display the results, we first get all votes for this poll for each answer the poll has:

$get_votes = mysql_query("SELECT * FROM votes WHERE poll_id = '$id' "); //select all votes to this poll

$votes = array(); //the array that will be containing all votes ( we add them as we retrieve them in a while loop )

$total_votes = 0;

while($vote = mysql_fetch_assoc($get_votes)) { //retrieve them
$answer_id = $vote['answer_id'];
$votes[$answer_id] = (int)$votes[$answer_id]+1; //add 1 vote for this particulair answer
$total_votes++;
}

All we do is select all votes for answers within the current poll from the mysql table 'votes', then we loop through them and create an array $votes and each answer that's voted for has its own sub-variable with as key the id of the answer ($answer_id) and as value the amount of votes for that current answer. We cast $votes[$answer_id] to be integer as it may be empty in the first place ( when no votes for the particular answer were found yet ) which is equal to the integer 0 which we'll want to use instead.

$get_answers = mysql_query("SELECT * FROM answers WHERE poll_id = '$id' ");

while($answer = mysql_fetch_assoc($get_answers)) { //loop through all answers this poll has

$id1 = $answer['id']; //the id of the answer -> the amount of votes for each answer we stored in $votes[id_of_answer] so for this id it would be $votes[$id]
$width = round((int)$votes[$id1]/$total_votes*399+1); //400px = max, 100% of votes

echo "<p>".$answer['answer']." (".(int)$votes[$id1]." vote".((int)$votes[$id1] != 1 ? "s":"").")<br /> <div style='background-color: #BABABA; width: {$width} px; height: 150px;"> </div> </p>";

}

echo "<a href='".$_SERVER['PHP_SELF']."?poll_id=".$_GET['poll_id']."'>[Show Poll]</a>";

We now make a query to get all answers that were available for this current poll ($get_answers) and for each of these answers we display the amount of votes that were found in the code before (stored in $votes[answer_id] for each answer). We also display a div (bar) with a grey color which illustrates the percentage of votes that went to this particular answer. The width of this bar is for 100% equal to 400px and for 0% equal to 1px. When we divide the amount of votes for the answer by the total amount of votes, we get the factor that indicates the part of votes that went to that answer. We use this factor to get the width to display the percentage bar in. And finally we show the link that displays the poll vote form ( by linking to the page without passing the showResults GET variable ).

Then we've got the next code which displays not the results but the form to vote for an answer for the poll:

$get_answers = mysql_query("SELECT * FROM answers WHERE poll_id = $id ");

echo "<form method='POST' action='".$_SERVER['PHP_SELF']."?poll_id=".$_GET['poll_id']."'>";
while($answer = mysql_fetch_assoc($get_answers)) { //loop through all answers this poll has

echo "<input type='radio' name='answer_id' value='".$answer['id']."'>".$answer['answer']." <br />";

}
echo "<input type='submit' name='vote' value='Vote!' />";
echo "</form>";
echo "<a href='".$_SERVER['PHP_SELF']."?poll_id=".$_GET['poll_id']."&showResults=1'>[Show Results]</a>";

Again we make a mysql query in $get_answers to get all answers available for the current poll. Next we make a form that allows the user to vote and within the while loop that retrieves all answers, we make a radio button for each of them. And again we display the link to show the results of the poll ( if the user doesn't want to vote but directly see the results ).

if($_POST['vote']) {

$time = time();
$ip = $_SERVER['REMOTE_ADDR']; //user ip
$answer_id = (int)$_POST['answer_id'];

if($answer_id <= 0) {
die("<p>Invalid answer.</p>");
}

$check_double = mysql_query("SELECT * FROM votes WHERE ip = '$ip' AND poll_id = '$id' "); //checks whether not already voted for this poll

if(mysql_num_rows($check_double) > 0) { //already voted for this poll?
echo "<p>Sorry, you may only vote ONCE a day.</p>";
}else{
$vote = mysql_query("INSERT INTO votes(ip, answer_id, poll_id, timestamp)VALUES('$ip', '$answer_id', '$id', '$time')");
if($vote) {
echo "<p>Successfully voted!</p>";
}else{
echo "<p>Error occured.</p>";
}
}

}

The last part of the code handles the submission of a vote: when $_POST['vote'] is set ( when the user presses the submit button to vote ) the vote is handled. First we check whether the user actually selected a valid answer ( catch the id of the selected answer ($_POST['answer_id']) and whether it's greater than zero ). Then we check whether the user hasn't already voted for this poll before with a mysql query that attempts to select a vote from the ip of the current user and for the current poll. With mysql_num-rows we check whether any vote was found, if so the user may not vote again.

Update: Width of bars fixed (was wrongly rounded).

To use our script (make sure a connection with the MySQL database is established) we simply include our poll.php file and we can simply call our showPoll function to display the poll. By default it uses the poll with the id given through the url as poll_id ($_GET['poll_id']), however we can as well supply one ourselves. E.g.

<?php

mysql_connect("localhost", "root", "");
mysql_select_db("webcodez");

include_once('poll.php');

showPoll(); //shows poll with id = $_GET['poll_id'] if any

?>

or

<?php

mysql_connect("localhost", "root", "");
mysql_select_db("webcodez");

include_once('poll.php');

showPoll(1); //shows poll with id = 1

?>

Note: The script automaticly goes to the current page upon submitting the vote form or clicking on the 'Show Results'/'Show Poll' link leaving out any GET variables the url may have. Therefore, if your website requires it ( by making use of GET variables through the url ), you may want to create a function that gets the real url and use that instead. For example:

function getUrl() {

$url = $_SERVER['PHP_SELF'];

if(count($_GET) > 0) {
$url .= "?";

foreach($_GET AS $key => $val) {
if($first) {
$url .= "?";
$first = 0;
}else{
$url .= "&";
}
$url .= "{$key}={$val}";
}
}

return $url;

}

It's also possible to make an AJAX-integration to make the script dynamic. All there's to do for that is make an AJAX/javascript function that calls the script function dynamicly, and load those contents in a Div and change the links hrefs to that javascript function, passing the GET variables through as parameter(s). Checkout this AJAX tutorial for more info.