Polls and Comments with PHP and MySQL

Posted by martin on 1 Jun 2002, last updated on 30 Aug 2002.

Create a poll voting and comments system with the help of PHP and MySQL. Explains in details the script and database design.

What do you need:

• PHP - obviously you know this from the title
  Get it from PHP's website, available in source or binary for free.
• MySQL - here we store the data. You should probably be able to use some other database also with a few modifications.
  Download from your nearest MySQL mirror. It is available through a GPL license in source or binary.
• Apache or some other web server to test it all, and a hosting service for permanent storage.
  You can get Apache for free too. Source and binaries are available.

So, I don't think you have to spend any money to get these wonderful software packages, and you should be ready to go if you already have them.

What are you expected to already know:

• XHTML/HTML of course, after all this is what PHP sends the browser.
• CSS - basic knowledge, if you like to control the way things look.
• JavaScript - for client-side validation, not needed if you want to only check on the server-side, which is a lot slower and higher network load.
• PHP - this is not a PHP tutorial, just an example. At least basic knowledge is needed.
• MySQL - you have to create the database and tables or let someone else do it for you.

A word about the configurations

I use a .htaccess file to setup PHP. If you have any trouble using the code try using these settings. If you get escaped quotes (like \' instead of ') you should use magic_quotes_gpc = off in php.ini or paste the following contents into a .htaccess file (or whatever it's called on your server). This should be in the directory where your code is, or in its parent directory.

php_value arg_separator.output "&"
php_value arg_separator.input "&"
php_flag register_globals off
php_flag short_open_tag off
php_flag magic_quotes_gpc off

Getting started

Globally included code

function connect_db() {
   global $link;

   $db_host = "localhost";
   $db_username = 'username';
   $db_pass = 'password';
   $db_name = 'database_name';

   $link = @mysql_connect($db_host, $db_username, $db_pass)
      or die ("Could not connect to database");
   @mysql_select_db ($db_name)
      or die ("Could not select database");
}

This is a function that connects to the database; I put this in a file that's included from every page at the site. If you think you don't understand anything you should check out the PHP manual. The @ in front of the functions is used to suppress the default error messages if you can't connect to the database.

<?php
   if ( !isset($link) )
      connect_db();
   $res = @mysql_query("select count from poll where id=0")
      or die("Query cm p0 failed");
   $result = mysql_fetch_object($res);

   $poll = (int) $HTTP_GET_VARS['poll'];
   if ( ($poll < 1) or ($poll > $result->count) ) {
      mt_srand ((double) microtime() * 1000000);
      $poll = mt_rand(1,$result->count);
   }
?>

Connect to the database if no connection is established yet, and get the total number of polls available. Check if a GET variable poll has been passed. If negative or greater than the number of polls, this is an illegal value so get a random one.

<form method="get" action="poll.php"
   onsubmit="return validateVote(this.vote)">
<table border="0" summary="poll inputs">

<?php
   $res = @mysql_query("select title, count, last from poll where id=$poll")
      or die("Query poll failed");
   $result = mysql_fetch_object($res);
?>
   <tr><td><?php echo $result->title?></td></tr>
<?php
   $total = $result->count;
   $date = $result->last;

   $res = @mysql_query("select text, vote from poll_data where id=$poll")
      or die("Query poll_data failed");
   while ($row = mysql_fetch_object ($res)) {
      $input ='<input type="radio" name="vote" value="' . 
         $row->vote . '" />';
      echo "<tr><td>$input $row->text</td></tr>";
   }
?>
   <tr><td>Total&#160;votes:&#160;<?php echo $total?></td></tr>
   <tr><td>Last&#160;vote:&#160;<?php echo $date?></td></tr>
   <tr><td><input type="submit" value="Vote" />   
      <a href="poll.php?poll=<?php echo $poll?>"
         title="View the current poll results">Results</a></td></tr>
</table>
   <div>
   <input type="hidden" id="poll" name="poll" value="<?php echo $poll?>" />
   </div>
</form>

This one defines the form which is submitted to the polls handling page, in my case poll.php. Get the poll title, total vote count, and the last time someone voted. Then loop over each option and create a radio input for all of them. Finally display the total votes, last voted date, a submit input for voting and a link to the results (if somebody doesn't want to vote). We also pass the poll id to the poll handler. This can also be implemented with session variables but see first if PHP is compiled with --enable-trans-sid. If yes, then it is safe, otherwise your option is to pass the SID constant, but this is essentially the same.

Now for the client-side validation function that is run onsubmit.

function validateVote(vote) {
   valid = false;

   // Opera 5.05 Linux does not support for/in on this object
   for ( var i = 0; i <  vote.length; i++ ) {
      if ( vote[i].checked ) {
         valid = true;
         break;
      }
   }
   if ( ! valid ) {
      alert("You must choose one");
   }
   return valid;
}

Well, that's all for the include code (this is usually shown on every page). The only other thing you may consider is protecting the file that holds the db username and password, in case PHP isn't there to process the code with something like this in a .htaccess file:

<Files "my_db_password_in_here.php">
    Order deny,allow
    Deny from all
</Files>

Now to get our hands on the input handling code.

The Poll Handling Code

<?php
   require "global_includes_file.php";
   if ( !isset($link) )
      connect_db();

   $res = @mysql_query("select count from poll where id=0")
      or die("Query polls failed");
   $result = mysql_fetch_row($res);
   $polls = $result[0];

   $poll = (int) $HTTP_GET_VARS['poll'];
   if ( $poll > $polls or $poll < 1 )
      $poll = 1;

Include a global_includes_file.php and check if a link to the database is established. Get the number of polls from the db and check if there is a GET passed variable poll. If not just display the first.

$poll_voted = (int) $HTTP_COOKIE_VARS['voted'];
$poll_bit = ( 1 << $poll );
if ( !($poll_voted & $poll_bit) ) {
	$poll_ok = true;
} else {
	$poll_ok = false;
}

$vote = (int) $HTTP_GET_VARS['vote'];
if ( $poll_ok and $vote > 0 and $vote < 6 ) { // count the vote
	$cookie_voted = $poll_voted | $poll_bit;
	setcookie('voted', $cookie_voted, time()+2592000); // one month time
	
	$sql = "update poll set count=count+1, last=now() where id=$poll";
	@mysql_query($sql) or die('Query p2 failed');

	$sql = "update poll_data set count=count+1 " .
		"where id=$poll and vote=$vote";
	@mysql_query($sql) or die('Query p3 failed');
}

Now we do not require visitors to register, just to vote but we neither allow them to vote again and again (except if they erase the cookies, but that's another story in which case you definitely need user registration (there are two articles on evolt: Creating a Login Script with PHP 4 and Creating a Login Script with PHP 4 - Part II on evolt). Make sure you read the articles, if this is what you need.

To get by on cookies, get one called voted, if available, and check whether the user has already voted for this poll. Get the vote that is handled; here's an example of some hard-coded variables that you may wish to get rid of, if you think there may be a different number of answers to one question. Check whether he/she is allowed to vote and if the vote is ok, and set a cookie that will stay for one month to keep track that the user has voted (not that he/she cannot use another browser or just delete the cookie, but anyway).

To finish the voting update the total number of votes and the current choice (increment by one). I'm not quite sure if MySQL applies locking and other stuff when you pass the queries this way, but this is definitely safer than extracting the value from the database and then explicitly setting it to a value that is incremented in PHP.

function my_esc($input) {
	return mysql_escape_string(htmlspecialchars($input) );
}

if ( $HTTP_SERVER_VARS['REQUEST_METHOD'] == 'POST' ) {
	$comment = my_esc($HTTP_POST_VARS['fComments']);
	$subject = my_esc($HTTP_POST_VARS['fSubject']);
	$name = my_esc($HTTP_POST_VARS['fName']);
	if ( $name == '' )
		$name = 'anonymous';
	$email = my_esc($HTTP_POST_VARS['fEmail']);
	$poll = (int) $HTTP_POST_VARS['poll'];

	if ( $comment != '' )
		$sql = "insert into comments " .
			"(name, subject, email, comment, date, poll)" .
			"values('$name', '$subject', '$email', " .
			"'$comment', now(), $poll)"

		@mysql_query($sql)
			or die('Query add comments failed');
}

Define a function that should escape the HTML and SQL if some smarty guy has decided to pass it to see what can happen. I'm not quite sure if this name is OK according to the PHP coding standards but anyway it's short and convenient, think up something else if you want. This is where you should change the code if you definitely want magic_quotes_gpc = on.

Check if any POSTing has happened, if so escape all the variables passed, and instead of displaying nothing replace the name with "anonymous". If any comments have been POSTed we are ready to insert that in the database.

	begin_head();
?>
<meta name="description" content="Poll results and comments" />
<title>Think up one</title>
<?php
	begin_body()
?>

That's supposed you have a function called< code>begin_head() that prints the document type declaration and stuff. Remember we used cookies, so nothing but headers should have been printed until now. The other function begins the body and prints some headers (like navbar or logo, etc.).

<h1>Poll results and comments</h1>
<?php
   if ( !$poll_ok and $vote )
      echo "<p>You are not allowed to vote again</p>";

   $sql = "select title, count, last from poll where id=$poll";
   $res = @mysql_query($sql)
      or die("Query c2 failed");
   $result = mysql_fetch_object($res);
   echo "<h2>$result->title</h2>";
   $total = $result->count;
   $date = $result->last;

?>

Print the first level header of the page and a message that the user is not allowed to vote again, if they've tried. Query the database to get the title, total count and last vote date for the current poll. I know the query failed messages are not user friendly, unfortunately I found that out long after I started using this code.

<table border="0" cellspacing="5" summary="votes statistics">
<?php
   $sql = "select text, count from poll_data where id=$poll";
   $res = @mysql_query($sql)
      or die("Query c3 failed");
   if ( mysql_num_rows($res) == 0 ) {
      echo "<tr><td><p>Sorry still nothing in here</p></td><
;/tr>";
   } else {
      while ($row = mysql_fetch_object ($res)) {
         $share = round(100 * $row->count / $total, 2);
            echo "<tr><td>$row->text</td>
            <td>$share% ($row->count)</td></tr>";
      }
   }
?>
</table>
<p>
Total votes: <?php echo $total?><br />
Last vote: <?php echo $date?>
</p>

Print a table with the poll results, if any. Otherwise a message will be shown, which is needed. Without it you will get a division by zero error when calculating the percentage share of each choice.

<h2>Other polls</h2>
<?php
   $res = @mysql_query("select * from poll where id!=$poll and id!=0")
      or die("Query c4 failed");
?>
<table border="0" cellspacing="5" summary="other polls">
<tr><th>Poll</th>
     <th>Votes</th>
     <th>Comments</th>
</tr>
<?php
   while ($row = mysql_fetch_object ($res)) {
?>
      <tr><td><a
href="poll.php?poll=<?php echo "$row->id" .'">' .
$row->title?></a></td>
      <td><?php echo $row->count?></td>
      <td><?php echo $row->comments?></td>
      </tr>
<?php
   }
?>
</table>

Print a table with links to other polls to be viewed/voted for. Note the query where .. and id!=0. That's because I use id=0 to store the total number of polls.

<h2>Comments</h2>
<div>
<?php
   $sql = "select name, subject, email, comment, date " .
      "from comments where poll=$poll&q
uot;;
   $res = @mysql_query($sql)
      or die("Query poll comments failed");
   if ( mysql_num_rows($res) == 0 ) {
      echo "<p>Sorry still nothing in here</p>";
   } else {
      while ($row = mysql_fetch_object ($res)) {
?>
         <div class="commenthead">
            <div class="subject"><?php echo $row->subject ?></div>
            <div class="info">by <a href="mailto:<?php echo $row->email ?>">
               <?php echo $row->name ?></a> on
               <?php echo gmdate('d M Y g:ia', strtotime($row->date)) ?> GMT
            </div>
         </div>

         <div class="comment">
         <?php echo $row->comment ?>
         </div>
<?php
      }
   }
?>
</div>

Now for the comments: get all for the current poll from the database (or if nothing is available say so). Iterate over each comment and print its subject. On the next line print the name provided (you may consider not creating it as a mailto: link because e-mail is optional) and the date and time. I know many people don't like UTC (that's Universal Coordinated Time, also informally known as GMT), but I think this is what should be used all over the Internet. Or print the GMT offset (though this is rarely seen). Many sites would rather print their local time, but this doesn't help people in different time zones.

<h2>Say your mind</h2>

<form method="post" action="<?php echo $HTTP_SERVER_VARS['PHP_SELF']?>"
   onsubmit="return validateString(this.comment)">
<div><input type="hidden" name="poll" value="<?php echo $poll?>" /></div>
<div class="formwrapper">

<div class="row">
   <label for="fName"
      title="Write down your name here (optional)">Name:</label>
   <span><input type="text" name="fName" id="fName" size="25"
      title="Write down your name here (optional)" tabindex="1" /></span>
</div>

<div class="row">
   <label for="fEmail"
      title="Write down your e-mail here (optional)">E-mail:</label>
   <span><input type="text" name="fEmail" id="fEmail" size="25"
      title="Write down your e-mail here (optional)" tabindex="2" /></span>
</div>

<div class="row">
   <label for="fSubject"
      title="Write down a short subject, maximum 60 chars">Subject:</label>
   <span><input type="text" name="fSubject" id="fSubject" size="25"
      title="Write down a short subject, maximum 60 chars" tabindex="3" />
   </span>
</div>

<div class="row">
   <textarea cols="40" rows="6" name="fComments" id="fComments"
      title="Just write the message here" tabindex="4"></textarea>
</div>

<div class="row" style="padding-bottom : 10px;">
   <input type="submit" value="Submit comment"
      title="Submit the comment" tabindex="5" />
    
   <input type="reset" value="Clear form"
      title="Clears the text box" tabindex="6" />
</div>

</div>
</form>

<p>Note: All fields except the comments are optional.</p>

This is pretty straight forward XHTML so I don't think anyone should need an explanation. The function that handles onsubmit follows.

function validateString(str) {
   if ( str.value && str.value.length > 0 ) return true
   else {
      alert("You don't think I can read your mind do you?");
      str.focus();
      return false;
   }
}

Database stuff

I apologize for the mixed case database queries; MySQL doesn't care about it so I don't feel I need to type all keywords in uppercase. You first have to create the database and then use mysql -p your_database_name < this_code.sql where this_code.sql is the filename you used to save the following SQL code.

CREATE TABLE poll (
   id int NOT NULL auto_increment,
   title varchar(100) NOT NULL default '',
   count int NOT NULL default '1',
   last date,
   PRIMARY KEY  (id)
);

CREATE TABLE poll_data (
   id int NOT NULL default '0',
   text char(50) NOT NULL default '',
   count int NOT NULL default '1',
   vote tinyint NOT NULL default '0'
);

create table comments(
   name varchar(30) not null default '',
   subject varchar(50) not null default '',
   email varchar(30) not null default '',
   comment text not null default '',
   date datetime not null default '',
   poll int not null default '0',
);

Stylesheet

You can use the following selectors in a stylesheet linked from the poll.php:

• div.formwrapper
• div.row
• div.row label
• div.row span, you can make it div.row input if you clear up the code
• div.commenthead
• div.subject
• div .comment

To finish up

The code and a more advanced version of it is available for download.

Comments