Polls Amp Comments With Php Amp Mysql

Introduction

Ever wanted to know what the visitors of your site think? Well, wonder no more because with the help of

PHP and

MySQL this is a piece of cake. In this article, I will explain how you can collect your visitors' opinions about a topic you (or somebody else) is interested in.

What do you need:

• PHP - obviously you know this from the title, right?
  
  Get it from www.php.net, 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 www.mysql.com. 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 from httpd.apache.org for free. Source and binaries are available.

So, I don't think you have to spend any money to get this wonderful software package, 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 iterate 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;
	for ( var i = 0; i <  vote.length; i++ ) {	// Opera 5.05 Linux does not support for/in on this object
		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
		@mysql_query("update poll set count=count+1, last=now() where id=$poll")
			or die('Query p2 failed');
		@mysql_query("update poll_data set count=count+1 where id=$poll and vote=$vote")
			or die('Query p3 failed');
	}

Now we do not require visitors to register, just to vote. Don't just allow them to vote again and again (except if they erase the cookies, but that's another story). Definitely require registration (there's an article on

Creating a login script with PHP 4 by

Jester uk) Be sure to read the article, if this is your case.

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 some 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, ENT_QUOTES) );
	}
	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 != '' )
			@mysql_query("insert into comments (name, subject, email, comment, date, poll) values('$name', '$subject', '$email', '$comment', now(), $poll)")
				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 supposing you have a function called 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>";
	$res = @mysql_query("select title, count, last from poll where id=$poll")
		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
	$res = @mysql_query("select text, count from poll_data where id=$poll")
		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
	$res = @mysql_query("select name, subject, email, comment, date from comments where poll=$poll")
		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)) ?> UTC
				</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, the once called 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="2" /></span>
</div>
<div class="row">
	<textarea cols="40" rows="6" name="fComments" id="fComments"
		title="Just write the message here" tabindex="3"></textarea>
</div>
<div class="row" style="padding-bottom : 10px;">
	<input type="submit" value="Submit comment" title="Submit the comment" tabindex="4" />
	 
	<input type="reset" value="Clear form" title="Clears the text box" tabindex="5" />
</div>
</div>
</form>
<p>Note: All fields except the comments are optional. If you think not to write any comments, I don't
really think you should waste disk space for the db.
</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

I don't claim this is the best code. You can tweak it a bit to get the result you want. If you need to get all the code at once instead of copy/pasting from the textareas, you can grab a zip file or a

gzipped tarball with it. You know linebreaks are not always where they have to be and stuff, but this prevents it and you know everything should be working right.