t seems that every Web site you go to nowadays has some kind of survey, feedback, or entertainment mechanism to evaluate site visitors and their goals. Surveys are a valuable tool that can help you build an effective community or refine your site content to better please your customers. In this article I’ll show you how to build a simple, but highly extensible survey engine that you can use to achieve these ends.

The architecture of a survey engine is simple (see Figure 1). You need to define your surveys, you need to run your surveys, and you need to report on your surveys.

Defining the Survey
For the sample survey in this article I’ll be using a MySQL database. You can use any database you like, but MySQL is simple, powerful, and integrates very cleanly with PHP, which will power our survey engine.

The survey uses three tables on the database.

The Questions. This is a simple table of text fields containing the actual questions and an associated ID.

The Options. This is a table containing the text of each option for each question. Each option is associated with a question ID.

The Answers. This table contains the answer to a specific question as provided by a visitor to your site. It contains the QuestionID, the answer that they specified (as the OptionID of the selected option), and their IP address. The IP address is stored to prevent multiple voting. The script to handle this is demonstrated later.

Figure 1. Building a Survey: A high-level architecture of the survey engine is shown.

Figure 2. The Table Definitions. The status of the data after just two responses, each having answered ‘Red,’ is shown.

To understand how it all works, I’ll begin with a simple survey of one question?’What is your Favorite Color?’?and just three possible answers: ‘Red,’ ‘Green,’ and ‘Blue.’ Assume that two people have answered your survey, each selecting ‘Red.’ In your database:

• The Questions table would have a single record with QuestionID=1 and QuestionText=’What is your Favorite Color?’.
• The Options table would have three records, all with QuestionID=1, and OptionIDs 1, 2, and 3 for ‘Red’, ‘Green,’ and ‘Blue,’ respectively.
• The Answers Table would have two records. Each would have the QuestionID set to ‘1’; each would have the AnswerValue set to ‘1’ (as they are both for Red). The AnswerIP field for each would be set to the IP address of the person that answered the question.

Figure 2 shows the data above as it appears inside my MySQL database.

Authors Note: The screen capture in Figure 2 uses MySQLCC, an incredibly useful tool that defines and fills tables in MySQL.

Presenting the Survey
As the survey is entirely defined in a database, presenting the data can be a straightforward data transformation: You’ll write code to read the question text, read the answer options for that question, and then render them in HTML. In the download for this article, you’ll find this complete file (survey.php); in the following paragraphs I’ll walk through each section in the file and show how it contributes to the rendering of the survey.

You first need to retrieve the question text and options. The correct survey will be accessed using a parameter that is passed into the page when it is called. In other words, to access survey No. 1, you call the page using http://yourserver/survey.php?questionid=1 (see Listing 1).

Now that your code has the information pertaining to QuestionID 1, the next step is to draw this information and render it in a browser (see Figure 3). How the application does this is very simple. It takes an input parameter using the PHP $_GET command, which it uses to build a SQL query to select the question text from the Questions table. A second SQL query uses the same parameter to get a recordset of the options from the Options table.

Figure 3. The Color Question: Here is our sample survey question in action.

Now that you have the display information, you simply need to generate an HTML page to display them. Because site visitors need to interact with the page, an HTML form is used. The form will display the options in a ‘radio’ control; when the user makes a selection, the form is submitted to another script, which you will see in the next section. The PHP that generates the HTML for this form is seen in Listing 2. The completed form is shown in Figure 3.

You can see that the while loop in Listing 2 outputs the HTML for controls of type ‘radio.’ These are given a value that increments from zero. So the value for the first option is 0, for the second it is 1, for the third it is 2, etc. You can easily expand this to custom values by adding the custom value to the options table, adding that column to the options query in Listing 1 and then outputting it to the HTML. For the sake of simplicity, a hard count from zero is used in this example. This forms-submittal script is ‘process.php,’ which you will see in the next section.

Processing the Response
The submission script, which is defined in the form as process.php, will receive the results of the form as $_POST variables. You are only interested in two of these variables. First is the value of the user’s selection from the options. In HTML every radio button has a name and a value. If you give all your options the same name (in this case ‘Q’), the $_POST variable for that name will contain the value of the selected radio button.

The second variable you’re interested in is the value of the question for which the answer is being submitted. This is stored in the form (Listing 2) as a hidden field called QID. The code snippet below handles QID.

if (isset($_POST['QID']))     $QID = $_POST['QID'];else     die("Can only be called from the survey page");if (isset($_POST['Q']))     $ANSVAL = $_POST['Q'];else     die("Invalid Answer");

Author’s Note: For this example, all answers are written, with no validation to see if somebody has voted twice. In the download (process.php), there is a section of code (that is commented out) that queries the IP address of the voter to see if they have already answered this question. If they have, then their vote is rejected. If you want to use this functionality, just remove the comments. (You can get the users IP address using the command GETENV(“REMOTE_ADDR”).

With these variables in hand, the next step is to write the new ‘answer’ data to the database. Writing the result to the database is very straightforward, just using a SQL INSERT query:

INSERT INTO ANSWERS (AnswerValue, AnswerIP, QuestionID) VALUES (x,y,z)

Where x, y, and z are the appropriate values for the answer, the IP address, and the ID of the question. Here is the PHP that process the query:

$SQL =     "INSERT INTO ANSWERS (AnswerValue,AnswerIP,QuestionID) VALUES (";$SQL = $SQL . $ANSVAL . ",'";$SQL = $SQL . $ANSIP . "',";$SQL = $SQL . $QID . ")";$result = mysql_query($SQL) or die ("SQL Error: " . $SQL);

Rendering the Results
The same file (process.php) will also render the results of the poll. There are a number of steps in this process:

1. You need to know how many total answers the question has.
2. You need to count how many of each option have been answered. So, if there are 100 answers spread over three options, you need to know how many are for option 1, for option 2, etc.
3. You need to calculate the percentage response for each of the answers.
4. You need to draw the results as an histogram.

First, query the database to count the number of records for a specific question from the answers table:

$SQL =    "SELECT Count(QuestionID) FROM Answers WHERE QuestionID=" . $QID;$result = mysql_query($SQL);$row = mysql_fetch_row($result);$nTotalVotes=$row[0];

This is as straightforward as doing a SQL COUNT query and getting the results. As you are only selecting one field?Count(QuestionID)?the results will be in $row[0].

The next step is to get the total number of votes for each option. In addition, you want to get the text associated with that option (to make the histogram more meaningful), so you need to do a SQL count query on the grouped answers for this question and join it to the Options table on the answer value:

SELECT answers.answervalue, options.optiontext as tx,              count(answers.answerValue) as ct FROM answers, options WHERE answers.answervalue = options.optionvalue       AND answers.QuestionID=1 GROUP BY answers.answervalue

Figure 4. Results of the Poll. The results are queried using SQL and drawn as a histogram.

Now that you have the results, you want to draw them out as a chart (see Figure 4). You can do this with PHP’s built-in image functionality. Start by informing the browser that the page is a GIF so that it can handle it properly. This is done at the top of process.php with the following command:

Header("Content-Type: image/gif");

Next, draw the actual image itself:

$xDim = 400;$yDim = 15+(mysql_num_rows($result)*15);$im = ImageCreate($xDim, $yDim);$backg = ImageColorAllocate($im, 255,255,200);$blue = ImageColorAllocate($im, 0, 0, 255);$nY = 0;while($row = mysql_fetch_array($result)){  $percVote = round(($row['ct']/$nTotalVotes)*100,2);	  $percWidth = ($percVote * $xDim / 100) - 10 ;  ImageFilledRectangle($im, 0, $nY+1, $percWidth, $nY+10, $blue);  ImageString($im,2,$percWidth + 5, $nY+1, $row['tx'] . " " . $percVote                          . "%", $blue);  $nY=$nY+15;}ImageGIF($im);

The ImageCreate command is used to create an image in memory with the specified height and width. In this case the width is set to 400 ($xDim=400) and the height is calculated based on the number of options for this question?that is, the number of rows in the resultset for the SQL query from the previous section (using mysql_num_rows).

Next, a couple of colors are allocated, using the ImageColorAllocate command. The first time this is called, the background of the image is set. The second time it is called, a reference to the color is set to the variable that stores its response. So, in this case $blue will contain a reference to the color blue (using RGB values 0,0,255).

Finally, the script loops through the recordset and for each record calculates the width of the bar in the histogram based on the percentage it received in the vote tally. It uses the ImageFilledRectangle command to draw a filled rectangle of the appropriate color, whose width is based on this value. It uses the ImageString to place text beside the rectangle. The text is the text for the option followed by the percentage representing that vote.

Finally, once the loop through the records is complete it uses the ImageGIF function to output the image as a GIF file.

As you can see, building a simple survey poll for interactivity on your Web site is very easy using PHP. The built in database drivers for MySQL and graphics drivers for GIF, JPEG, or other formats are enormously useful. In this sample you built a very simple, one-step poll, but it gives you the foundation upon which you can built more complex and interesting applications. And most importantly, will let you see into the heads and hearts of your customers and visitors.