Going to the Polls

Languages‎ > ‎Php‎ > ‎PHP For the Absolute Beginner‎ > ‎

Adding More...

The next step in building this application is to provide the administrator with an easy way to add and delete questions and answers from the MySQL database. Consider the script admin.php, which provides the starting point for these tasks:

<html> <head><basefont face = 'Arial'></head> <body> <h2>Administration</h2> <h4>Current Questions:</h4> <table border = '0' cellspacing = '10'> <?php // include configuration file include('config.php'); // open database connection $connection = mysql_connect($host, $user, $pass) or die('ERROR: Unable to connect!'); // select database mysql_select_db($db) or die('ERROR: Unable to select database!'); // generate and execute query $query = 'SELECT qid, qtitle, qdate FROM questions ORDER BY qdate DESC'; $result = mysql_query($query) or die('ERROR: $query. '.mysql_error()); // if records are present if (mysql_num_rows($result) > 0) { // iterate through resultset // print question titles while($row = mysql_fetch_object($result)) { ?> <tr> <td><?php echo $row->qtitle; ?></td> <td><font size = '-2'><a href = 'view.php?qid=<?php echo $row->qid; ?>'>view report</a></font></td> <td><font size = '-2'><a href = 'delete.php?qid=<?php echo $row->qid; ?>'>delete</a></font></td> </tr> <?php } } // if no records are present, display message else { ?> <font size='-1'>No questions currently configured</font> <?php } // close connection mysql_close($connection); ?> </table> <h4>Add New Question:</h4> <form action = 'add.php' method ='post'> <table border = '0' cellspacing = '5'> <tr> <td>Question</td> <td><input type = 'text' name = 'qtitle'></td> </tr> <tr> <td>Option #1</td> <td><input type = 'text' name = 'options[]'></td> </tr> <tr> <td>Option #2</td> <td><input type = 'text' name = 'options[]'></td> </tr> <tr> <td>Option #3</td> <td><input type = 'text' name = 'options[]'></td> </tr> <tr> <td>Option #4</td> <td><input type = 'text' name = 'options[]'></td> </tr> <tr> <td>Option #5</td> <td><input type = 'text' name = 'options[]'></td> </tr> <tr> <td colspan = '2' align = 'right'><input type = 'submit' name = 'submit' value = 'Add Question'></td> </tr> </table> </form> </body> </html>

Here's what it looks like:

As you can see, there are two sections in this script. The first half connects to the database and prints a list of all available questions, with "view report" and "delete" links next to each (more on this these shortly). The second half contains a simple form for the administrator to add a new question and up to five possible answers.

Once the form is submitted, the data entered by the administrator gets POST-ed to the script add.php, which validates it and saves it to the database. Here's the code:

<html> <head><basefont face = 'Arial'></head> <body> <h2>Administration</h2> <?php if (isset($_POST['submit'])) { // check form input for errors // check title if (trim($_POST['qtitle']) == '') { die('ERROR: Please enter a question'); } // clean up options // add valid ones to a new array foreach ($_POST['options'] as $o) { if (trim($o) != '') { $atitles[] = $o; } } // check for at least two options if (sizeof($atitles) <= 1) { die('ERROR: Please enter at least two answer choices'); } // include configuration file include('config.php'); // open database connection $connection = mysql_connect($host, $user, $pass) or die('ERROR: Unable to connect!'); // select database mysql_select_db($db) or die('ERROR: Unable to select database!'); // generate and execute query to insert question $query = "INSERT INTO questions (qtitle, qdate) VALUES ('{$_POST['qtitle']}', NOW())"; $result = mysql_query($query) or die("ERROR: $query.".mysql_error()); // get the ID of the inserted record $qid = mysql_insert_id(); // reset variables unset($query); unset ($result); // now insert the options // linking each with the question ID foreach ($atitles as $atitle) { $query = "INSERT INTO answers (qid, atitle, acount) VALUES ('$qid', '$atitle', '0')"; $result = mysql_query($query) or die("ERROR: $query. ".mysql_error()); } // close connection mysql_close($connection); // print success message echo "Question successfully added to the database! Click <a href='admin.php'>here</a> to return to the main page"; } else { die('ERROR: Data not correctly submitted'); } ?> </body> </html>

This script has a lot of things happening in it, so let's go through it step-by-step.

The first order of business is to sanitize the data entered by the user. There are a bunch of lines of code at the top of the script that do this, by checking for a question title and verifying that at least two answer choices are present. Notice my use of the trim() function to weed out any input that contains only empty spaces, and the sizeof() function that verifies the presence of at least two valid answer choices in the $POST['options'] array. Any failure here results in an error message, and the script will refuse to proceed further.

Assuming all the data is acceptable, the next step is to save it to the database. First, the question is saved to the questions table via an INSERT query. The ID generated by this INSERT query is retrieved via the mysql_insert_id() function, and used to link the answer choices to the question when saving them to the answers table. Since there will be more than one answer choice for each question, a foreach() loop is used to repeatedly run an INSERT query - once for each possible answer choice (with MySQL 4.1 and the PHP 5 mysqli extension, you could instead use a prepared query here - feel free to experiment with this alternative yourself).

That takes care of adding questions and answers. Now, what about removing them?

Well, go back and take a look at the admin.php script. You'll see that, next to each question displayed, there is a "delete" link, which points to the script delete.php. You'll also see that this script is passed an input parameter, the question ID, on the URL itself. It's clear, then, that delete.php can use this input parameter to identify the corresponding question in the questions table (as well as its answers - the question ID is common to both tables, remember) and run a DELETE query to erase this data from the system.

Here's the code that actually does the work:

As you can see, the question ID passed through the GET method is retrieved by the script, and used inside two DELETE queries to remove all the records linked to that ID.

Playing the Numbers

Now for possibly the most interesting section of this tutorial: Item #3. Obviously, once you have users and votes coming in, you'd like to see reports of how the votes are distributed. This involves connecting to the database, using the question ID to extract the correct record set, calculating the total number of votes and the percentage each option has of the total, and displaying this information in a table.

Here's what all that looks like in PHP:

<html> <head><basefont face = 'Arial'></head> <body> <h2>Administration</h2> <?php if ($_GET['qid'] && is_numeric($_GET['qid'])) { // include configuration file include('config.php'); // open database connection $connection = mysql_connect($host, $user, $pass) or die('ERROR: Unable to connect!'); // select database mysql_select_db($db) or die('ERROR: Unable to select database!'); // get the question $query = "SELECT qtitle FROM questions WHERE qid = '".$_GET['qid']."'"; $result = mysql_query($query) or die("ERROR: $query. ".mysql_error()); $row = mysql_fetch_object($result); echo '<h3>'.$row->qtitle.'</h3>'; // reset variables unset($query); unset($result); unset($row); // find out if any votes have been cast $query = "SELECT qid, SUM(acount) AS total FROM answers GROUP BY qid HAVING qid = ".$_GET['qid']; $result = mysql_query($query) or die("ERROR: $query. ".mysql_error()); $row = mysql_fetch_object($result); $total = $row->total; // if votes have been cast if ($total > 0) { // reset variables unset($query); unset($result); unset($row); // get individual counts $query = "SELECT atitle, acount FROM answers WHERE qid = '".$_GET['qid']."'"; $result = mysql_query($query) or die("ERROR: $query. ".mysql_error()); // if records present if (mysql_num_rows($result) > 0) { // print vote results echo '<table border=1 cellspacing=0 cellpadding=15>'; // iterate through data // print absolute and percentage totals while($row = mysql_fetch_object($result)) { echo '<tr>'; echo '<td>'.$row->atitle.'</td>'; echo '<td>'.$row->acount.'</td>'; echo '<td>'.round(($row->acount/$total) * 100, 2).'%</td>'; echo '</tr>'; } // print grand total echo '<tr>'; echo '<td><u>TOTAL</u></td>'; echo '<td>'.$total.'</td>'; echo '<td>100%</td>'; echo '</tr>'; echo '</table>'; } } // if votes have not been cast else { echo 'No votes cast yet'; } // close connection mysql_close($connection); } else { die('ERROR: Data not correctly submitted'); } ?> </body> </html>

Here's an example of what the output might look like:

view.php

This script, view.php, is activated from admin.php in much the same way as delete.php - a question ID is passed to it as an input parameter, and that ID is used to retrieve the corresponding answers and the votes each one has gathered. Once the answer set has been retrieved, the total number of votes submitted can be calculated, and the percentage share of each option in the total vote can be obtained. This data is then displayed in a simple HTML table.

You need to be careful when converting the absolute numbers into percentages - if there aren't any votes yet, you can get some pretty strange division by zero errors. To avoid this, the second query in the script uses MySQL's SUM() function and GROUP BY clause to obtain the total number of votes for a particular question. If this total is 0, no votes have yet been cast, and a message to that effect is displayed; if the total is greater than 0, the individual percentages are calculated.

Exit Poll

The way things are currently set up, a single user can vote for a particular option more than once, thereby contravening one of the basic principles of democracy: one citizen, one vote. Although it's unlikely that many users would have the patience or inclination to do this; however, it is a hole, and should be plugged.

I've decided to set a cookie on the voter's system once the vote has successfully been cast. With the addition of a few lines of script, I can now check for the presence or absence of this cookie whenever a user tries to vote, and thereby decide whether or not to accept the vote.

Here's the code, which gets added to the very top of user_submit.php:

<?php // check if a cookie exists for this question // deny access if it does if (isset($_COOKIE) && !empty($_COOKIE)) { if ($_COOKIE['lastpoll'] && $_COOKIE['lastpoll'] == $_POST['qid']) { die('ERROR: You have already voted in this poll'); } } // set cookie setCookie('lastpoll', $_POST['qid'], time() + 2592000); ?>

With this in place, when a user votes, a cookie is set on the client browser, containing the ID for the question the user voted on. At each subsequent vote attempt, the script will first check for the presence of the cookie and, if it exists, the value of the cookie variable $_COOKIE['lastpoll']. Only if the cookie is absent (indicating that this is a first-time voter) or the value of $_COOKIE['lastpoll'] is different from the ID of the current poll question (indicating that the user has voted previously, but in response to a different question), will the vote be accepted.

This is by no means foolproof: any reasonably adept user can delete the cookie from the client's cache and vote again - but it does add a layer of security to the process. The ideal method, of course, would be to track voters on the server itself and deny votes to those who have already voted; and indeed, this is a feasible alternative if the site requires users to register with unique usernames before accessing its online polls.

Well, that's about it. Hopefully, this exercise has given you some insight into how PHP can be used to build a simple web application, and illustrated its power and flexibility as a rapid development tool for the web medium. Come back soon for the final PHP 101, and one more do-it-yourself application!

The Real World

In the course of this series, I've taken you on a tour of PHP, teaching you everything you need to know to get started with this extremely powerful toolkit. You've learned how to process arrays, write functions, construct objects, and throw exceptions. You've also learned how to read user input from forms, search databases, and use cookies and sessions to maintain state. You're no longer the timid PHP newbie you used to be, but a bold and powerful PHP warrior, ready to take on anything the world (or your boss) throws at you...

There's only one drawback. Sure, you have all the weaponry... but you haven't ever used it in the real world. That's where these concluding segments of PHP 101 come in.

Over the final two chapters of this tutorial, I'm going to guide you through the process of creating two real-world PHP applications. Not only will this introduce you to practical application development with PHP, but it will also give you an opportunity to try out all the theory you've imbibed over the past weeks.

Drivers, start your engines!

Burning Questions

The first application is fairly simple. It's a polling system for a web site, one which allows you to quickly measure what your visitors think about controversial issues (Kerry versus Bush, to-mah-to versus to-mae-to, that kind of thing). This online polling mechanism is fairly popular, because it lets you find out what your visitors are thinking, and makes your web site more dynamic and interactive.

I'm sure you've seen such a system in action on many web portals, and have a fairly clear mind's-eye picture of how it works. Nevertheless, it's good practice to write down exactly what the end product is supposed to do before you begin writing even a single line of code (geeks call this defining requirements).

There needs to be a mechanism by which the user can view a question, and then select from a list of possible answers. This "vote" then needs to be captured by the system, and added to the existing tally of votes for that question.
There needs to be a way for the site administrator to add new questions, or delete old ones. A MySQL database is a good place to store these questions and answers, but the administrator may not necessarily be proficient enough in SQL to change this data manually. Therefore, a form-based interface should be provided, to make the task simple and error-free.
Obviously, there also needs to be a way to view reports of the votes submitted for each question and its answers. The report would contain a count of the total votes registered for a question, as well as a breakdown of the votes each answer received.

An important question here is: Does it make sense to fix the number of available choices for each question? In my opinion, it doesn't, because the number of available choices is likely to change with each question. It's better to leave this number variable, and to allow the poll administrator to add as many choices per question as appropriate. We can, however, define an upper limit on the number of possible choices for each question - for argument's sake let's say five.

With this basic outline in mind, the next step is to design a database that supports these requirements.

Designer Databases

This is a good time for you to download the source code for this application, so that you can refer to it throughout this tutorial. (Note that you will need a MySQL server and a PHP-capable Web server to run this code.)

Here's the database which I'll be using for this application, stored in db.sql:

#
# Table structure for table `questions`
#
CREATE TABLE `questions` (
  `qid` tinyint(3) unsigned NOT NULL auto_increment,
  `qtitle` varchar(255) NOT NULL default '',
  `qdate` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`qid`)
);

#
# Table structure for table `answers`
#
CREATE TABLE `answers` (
  `aid` tinyint(3) unsigned NOT NULL auto_increment,
  `qid` tinyint(4) NOT NULL default '0',
  `atitle` varchar(255) NOT NULL default '',
  `acount` int(11) NOT NULL default '0',
  PRIMARY KEY  (`aid`)
);

As you can see, this is pretty simple: one table for the questions, and one for the answers. The two tables are linked to each other by means of the qid field. With this structure, it's actually possible to have an infinite numbers of answers to each question. (This is not what we want - we'd prefer this number to be five or less - but the logic to implement this rule is better placed at the application layer than at the database layer).

To get things started, and to give you a better idea of how this structure plays in real life, let's INSERT a question into the database, together with three possible responses:

INSERT INTO `questions` VALUES (1, 'What version of PHP are you using?', '2004-10-15');
INSERT INTO `answers` VALUES (1, 1, 'PHP 3.x', 0);
INSERT INTO `answers` VALUES (2, 1, 'PHP 4.x', 0);


INSERT INTO `answers` VALUES (3, 1, 'PHP 5.x', 0);

Alternatively, you could create a new database and type source db.sql from the command prompt to load the table structures and data directly.

Rocking the Vote

With the database taken care of, it's time to put together the web pages that the user sees. The first of these is user.php, which connects to the database to get the latest poll question and displays it together with all its possible responses. Take a look:

<html> <head><basefont face = 'Arial'></head> <body> <?php // include configuration file include('config.php'); // open database connection $connection = mysql_connect($host, $user, $pass) or die('ERROR: Unable to connect!'); // select database mysql_select_db($db) or die('ERROR: Unable to select database!'); // generate and execute query $query = "SELECT qid, qtitle FROM questions ORDER BY qdate DESC LIMIT 0, 1"; $result = mysql_query($query) or die("ERROR: $query.".mysql_error()); // if records are present if (mysql_num_rows($result) > 0) { $row = mysql_fetch_object($result); // get question ID and title $qid = $row->qid; echo '<h2>'.$row->qtitle .'</h2>'; echo "<form method = post action = 'user_submit.php'>"; // get possible answers using question ID $query = "SELECT aid, atitle FROM answers WHERE qid = '$qid'"; $result = mysql_query($query) or die("ERROR: $query.".mysql_error()); if (mysql_num_rows($result) > 0) { // print answer list as radio buttons while ($row = mysql_fetch_object($result)) { echo "<input type = radio name = aid value = '".$row->aid."'>'".$row->atitle."'</input><br />"; } echo "<input type = hidden name = qid value = '".$qid."'>"; echo "<input type = submit name = submit value = 'Vote!'>"; } echo '</form>'; } // if no records present, display message else { echo '<font size="-1">No questions currently configured</font>'; } // close connection mysql_close($connection); ?> </body> </html>

Pay special attention to the SQL query I'm running: I'm using the ORDER BY, DESC and LIMIT keywords to ensure that I get the latest record (question) from the questions table. Once the query returns a result, the record ID is used to get the corresponding answer list from the answers table. A while() loop is then used to print the answers as a series of radio buttons. The record ID corresponding to each answer is attached to its radio button; when the form is submitted, this identifier will be used to ensure that the correct counter is updated.

Note that if the database is empty, an error message is displayed. In this example, we've already inserted one question into the database, so you won't see it at all; however, it's good programming practice to ensure that all eventualities are accounted for, even the ones that don't occur that very often.

The file config.php included at the top of the script contains the access parameters for the MySQL database. This data has been placed in a separate file to make it easy to change it if you move the application to a new server. Take a look inside:

<?php // database access parameters $host = 'localhost'; $user = 'guest'; $pass = 'guessme'; $db = 'db3'; ?>

Here's what the form looks like:

Okay, now you've got the poll displayed. Users are lining up to participate, and clicks are being generated by the millions. What do you do with them?

The answer lies in the script that gets activated when a user casts a vote and submits the form described earlier. This script, user_submit.php, takes care of updating the vote counter for the appropriate question/answer combination. Take a look:

This script first checks to ensure that an answer has been selected, by verifying the presence of the answer ID $_POST['aid']. Assuming the ID is present, the script updates the database to reflect the new vote and displays an appropriate message.

Now, flip back through your notebook and look at the initial requirement list. Yup, you can cross off Item #1. Onwards to Item #2...

Comments

programming

Navigation

Recent site activity