PHP create surveys

Running a survey on your site can help you find out in real time what your visitors want, what should you improve, write, sell, modify, stop, start etc. We can build an enormous list with the advantages of running a survey. Some people like to earn by following instincts, by risking and others like to use all the strategies and weapons available for a better understanding of their goals and what people really want. Sticking to web marketing and web tools, we are going to learn in this tutorial how to build our own php/MySql survey and use it on our websites. I must admit that this application has been a little challenging. I started with a plan of 1 day, on day 2 I thaught it's pretty expensive (in time) and on day 3 I said that if there will be a day 4 I'll change the project from a tutorial into a commercial product :). We're having a tutorial so I made it on 3. For those of you who wish to skip the small introduction and description of the application please scroll down to the bottom of the page and locate the download link. Our application will need a mysql database with 4 tables: polls, questions, options of questions and answers. It's more flexible this way, you can add any number of questions, options and surveys. The results are displayed using a nice flash chart with compliments to PHP/SWF Charts. Also used is xajax (an open source PHP class library that allows you to easily create Ajax applications), Spry (I wish I used mootools but I got greedy and clicked a little in my dreamweaver) and various snippets to help our application validate, extract, limit etc.

CREATE TABLE `polls` (
  `ID` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM;

CREATE TABLE `poll_questions` (
  `ID` int(11) NOT NULL auto_increment,
  `ID_poll` int(11) NOT NULL,
  `poll_question` varchar(255) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM;

CREATE TABLE `poll_options` (
  `ID` int(11) NOT NULL auto_increment,
  `ID_question` int(11) NOT NULL,
  `ID_poll` int(11) NOT NULL,
  `poll_option` varchar(255) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM;

CREATE TABLE `poll_answers` (
  `ID` int(11) NOT NULL auto_increment,
  `ID_option` int(11) NOT NULL,
  `ID_question` int(11) NOT NULL,
  `ID_poll` int(11) NOT NULL,
  `visitor_ip` varchar(32) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM;

Our PHP survey web app. is using 1 main page (it started with 4 actually but I compressed it somewhere in day 3 lol) to add, delete surveys, questions and answers, 2 pages for displaying the charts (results per question or per survey) and some pop-ups to take a survey, grab the html code for implementing it on your pages etc. index.php

<?php
	$getsurveys = new Pagination();

	$getsurveys->start = ($_GET['start']) ? $_GET['start'] : '0';
	$getsurveys->filePath = 'index.php';
	$getsurveys->select_what = '*';
	$getsurveys->the_table = 'polls';
	$getsurveys->add_query = 'ORDER BY ID DESC';
	$getsurveys->limit = 5;

	$surveys = $getsurveys->getQuery(TRUE);
	$bg1 = "t9"; // color one
	$bg2 = "t10"; // color two
	$row_count = 0;
	$i = (($_GET['start']) ? $_GET['start'] : '0')+1;

	$content  = '	<table width="100%" border="0" cellspacing="0" cellpadding="0" class="t0">'."\n";
	$content .= '		<tr>'."\n";
	$content .= '			<td class="t1"><div align="center"><b>No.</b></div></td>'."\n";
	$content .= '			<td class="t2"><div align="center"><b>Title</b></div></td>'."\n";
	$content .= '			<td class="t3"><div align="center"><b>Answers</b></div></td>'."\n";
	$content .= '			<td class="t4"><div align="center"><b>Action</b></div></td>'."\n";
	$content .= '		</tr>'."\n";

	if(mysql_num_rows($surveys)>0)
	{
		$row = mysql_fetch_assoc($surveys);
		do {
			$row_color = ($row_count % 2) ? $bg1 : $bg2;

			$content .= '	<tr>'."\n";
			$content .= '		<td class="t5 '.$row_color.'">'."\n";
			$content .= '		<div align="center">'.$i++.'</div></td>'."\n";
			$content .= '		<td class="t6 '.$row_color.'">'."\n";
			$content .= '		<div align="left"><a href="view_survey.php?ID='.$row['ID'].'" target="_blank">'.$row['title'].'</a>'."\n";
			$content .= '		<img src="images/add.png" width="16" height="16" border="0" align="bottom" class="cat_img" onclick="MM_effectSlide(\'survey_'.$row['ID'].'\', 700, \'0%\', \'100%\', true, false, true)" />'."\n";
			$content .= '</div>'."\n";
			$content .= '<div style="height:25px">'."\n";
			$content .= '<div class="q_form" id="survey_'.$row['ID'].'">'."\n";
			$content .= '		<form action="'.selfURL().'" method="POST">'."\n";
			$content .= '			<input type="hidden" name="ID_survey" value="'.$row['ID'].'" size="32" />'."\n";
			$content .= '			<input type="text" name="new_question" value="" size="32" />'."\n";
			$content .= '			<input type="submit" name="add_question" value="Add question" />'."\n";
			$content .= '		</form>'."\n";
			$content .= '</div>'."\n";
			$content .= '</div>'."\n";

			$getQuestions = mysql_query("SELECT * FROM poll_questions WHERE ID_poll = '".$row['ID']."'");
			if(mysql_num_rows($getQuestions)>0)
			{
				$row_questions = mysql_fetch_assoc($getQuestions);
				$q = 1;

				do {
					$content .= '		<div style="padding-left:100px;">'."\n";
					$content .= '			'.$q++.' : '.$row_questions['poll_question'].''."\n";
					$content .= '			<a href="javascript:void(0);" title="add option">'."\n";
					$content .= '			<img src="images/add.png" width="16" height="16" border="0" onclick="MM_effectSlide(\''.$row_questions['ID'].'\', 700, \'0%\', \'100%\', true, false, true)" />'."\n";
					$content .= '			</a>'."\n";
					$content .= '			<a href="javascript:popUp(\'chart.php?ID='.$row_questions['ID'].'\');" title="take survey">'."\n";
					$content .= '			<img src="images/chart.png" width="16" height="16" border="0" />'."\n";
					$content .= '			</a>'."\n";
					$content .= '			<a href="javascript:void(0);" title="delete">'."\n";
					$content .= '			<img src="images/delete.png" width="16" height="16" border="0" onClick="xajax_admin_action_questions(\'delete\', \''.$row_questions['ID'].'\');" />'."\n";
					$content .= '			</a>'."\n";


					$getOptions = mysql_query("SELECT * FROM poll_options WHERE ID_question = '".$row_questions['ID']."'");
					if(mysql_num_rows($getOptions)>0)
					{
						$row_options = mysql_fetch_assoc($getOptions);

						do {
							$content .= '		<div style="padding-left:30px; height:18px">'."\n";

							$content .= '&rarr;'.$row_options['poll_option'];
							$content .= '			<a href="javascript:void(0);" title="delete">'."\n";
							$content .= '			<img src="images/delete.png" width="16" height="16" border="0" onClick="xajax_admin_action_options(\'delete\', \''.$row_options['ID'].'\');" />'."\n";
							$content .= '			</a>'."\n";

							$content .= '		</div>'."\n";

						} while ($row_options = mysql_fetch_assoc($getOptions));
					}

					$content .= '<div style="height:25px">'."\n";
					$content .= '<div class="q_form" id="'.$row_questions['ID'].'">'."\n";
					$content .= '		<form action="'.selfURL().'" method="POST" class="form">'."\n";
					$content .= '			<input type="hidden" name="ID_question" value="'.$row_questions['ID'].'" size="32" />'."\n";
					$content .= '			<input type="hidden" name="ID_poll" value="'.$row['ID'].'" size="32" />'."\n";
					$content .= '			<input type="text" name="new_option" value="" size="32" />'."\n";
					$content .= '			<input type="submit" name="add_option" value="Add option" />'."\n";
					$content .= '		</form>'."\n";
					$content .= '</div>'."\n";
					$content .= '</div>'."\n";

					$content .= '		</div>'."\n";

				} while ($row_questions = mysql_fetch_assoc($getQuestions));
			}

			$content .= '		</td>'."\n";
			$content .= '		<td class="t7 '.$row_color.'">'."\n";
			$content .= '		<div align="center">'.getSurveyAnswers($row['ID']).'</div></td>'."\n";
			$content .= '		<td class="t8 '.$row_color.'">'."\n";
			$content .= '		<div align="center">'."\n";
			$content .= '		<form name="form'.$row['ID'].'" id="form'.$row['ID'].'" class="form" >'."\n";
			$content .= '		<input name="id_survey" type="hidden" value="'.$row['ID'].'" />'."\n";
			$content .= '		<div align="center">'."\n";
			if(checkSurvey($row['ID'])):
				$content .= '			<a href="javascript:popUp(\'survey.php?ID='.$row['ID'].'\');" title="take survey">'."\n";
				$content .= '			<img src="images/survey.png" width="16" height="16" border="0" />'."\n";
				$content .= '			</a>'."\n";
				$content .= '			<a href="charts.php?ID='.$row['ID'].'" title="view results" target="_blank">'."\n";
				$content .= '			<img src="images/chart.png" width="16" height="16" border="0" />'."\n";
				$content .= '			</a>'."\n";
				$content .= '			<a href="javascript:popUp(\'code.php?ID='.$row['ID'].'\');" title="get the code">'."\n";
				$content .= '			<img src="images/code.png" width="16" height="16" border="0" />'."\n";
				$content .= '			</a>'."\n";
				endif;
				$content .= '			<a href="javascript:void(0);" title="delete">'."\n";
				$content .= '			<img src="images/delete.png" width="16" height="16" border="0" onClick="xajax_admin_action(\'delete\', \''.$row['ID'].'\');" />'."\n";
			$content .= '			</a>'."\n";
			$content .= '		</div>'."\n";
			$content .= '		</form>'."\n";
			$content .= '		</div></td>'."\n";
			$content .= '	</tr>'."\n";

			$row_count++;

		} while ($row = mysql_fetch_assoc($surveys));

		$content .= '</table>'."\n";

		echo $content;
		echo $getsurveys->paginate();
	}
	else {
		echo '<div align="center" style="margin:200px auto auto auto;">No surveys defined</div>';
	}
?>

The above code represents the main interface of our php survey, you will notice in the first lines that it uses a class (Pagination) to pull the surveys out from the database, limiting them at 5 per page. $getsurveys = new Pagination(); $getsurveys->start = ($_GET['start']) ? $_GET['start'] : '0'; $getsurveys->filePath = 'index.php'; $getsurveys->select_what = '*'; $getsurveys->the_table = 'polls'; $getsurveys->add_query = 'ORDER BY ID DESC'; $getsurveys->limit = 5;

The first line creates a new object, the second one tells the pagination class from what result to start displaying the data (for example if start = 10 the pagination will ignore the first 10 results and display the ones from 10 + $getsurveys->limit which is set to 5 in our example), the third line of code tells the pagination class what page to use when building the results, and the next ones are used in the query when selecting data. To display the pagination links you simply echo it (echo $getsurveys->paginate();) wherever you want it in the page (not before creating the object). Another important line is the one that will return the query back to us so we can create a loop and display the surveys. For this we use the same class (pagination) which has a function that, default, is set to FALSE for internal use but when we set it to TRUE ($surveys = $getsurveys->getQuery(TRUE);) returns with the clean query that was was used. It's more complicated to explain it rather than looking at the code and understanding this simple and so handy class. $bg1 = "t9"; // color one $bg2 = "t10"; // color two $row_count = 0; $i = (($_GET['start']) ? $_GET['start'] : '0')+1; The above lines are used to alternate between table rows and switch the style properties ($bg1, $bg2) in order to create a better visual result ($row_color = ($row_count % 2) ? $bg1 : $bg2;). $i will be the counter of our results which uses the $_GET['start'] parameter for showing the result number for every row in part. It starts from 1 if we're on the first page and increments by 1 the start parameter - a simple counter. In our next lines we start building a variable ($content) which is supposed to hold the entire application and display it whenever echoed/printed. Each survey displayed will contain a loop to show the questions and each question in part will also contain another loop to display the options that are linked to that question. You won't be able to view the icons for chart results, html code for implementing the survey and the actual survey starter until there are questions and options to that survey. I will also like to talk a little about the way we're going to display the questions and how we're going to store the answers. survey.php

<?php

	if(checkSurvey($_GET['ID']))
	{
		$getQuestions = mysql_query("SELECT poll_questions.ID AS ID, poll_questions.poll_question AS poll_question FROM poll_questions WHERE poll_questions.ID_poll='".mysql_real_escape_string($_GET['ID'])."' AND NOT EXISTS(SELECT poll_answers.ID FROM poll_answers WHERE poll_answers.ID_question = poll_questions.ID AND poll_answers.visitor_ip = '".getIP()."')");

		if(mysql_num_rows($getQuestions)>0)
		{
			$row_questions = mysql_fetch_assoc($getQuestions);
			echo $row_questions['poll_question']. '<br /><hr />';

			$getOptions = mysql_query("SELECT * FROM poll_options WHERE ID_question = '".mysql_real_escape_string($row_questions['ID'])."' ORDER BY ID ASC") or die(mysql_error());

			$row_options = mysql_fetch_assoc($getOptions);

			do {

				echo '<br /><input type="radio" name="answer" id="'.$row_options['ID'].'" value="'.$row_options['ID'].'" onClick="xajax_insert_answer(\''.$row_options['ID'].'\', \''.$_GET['ID'].'\', \''.$row_questions['ID'].'\');" /><label for="'.$row_options['ID'].'">'.$row_options['poll_option'].'</label>';

			} while ($row_options = mysql_fetch_assoc($getOptions));
		}
		else {
			echo 'Thank you for taking this survey! You may <a href="'.$url.'charts.php?ID='.$_GET['ID'].'" title="view survey results" target="_blank">view results</a>';
		}
	}
	else {
		echo 'This poll has no questions or options to questions';
	}
?>

The hard thing to achieve here was this simple goal: I wanted to use some ajax and display the options helped by html radio inputs. Presented with the options, the visitor will have to select one of them and then, automatically, the page will show the next set of options. Sounds simple but remember that on this "simple" change, the page will have to store the answer using AJAX, refresh the page and perform a SELECT statement to display the next question for that survey. The hard part was to create that select in such a way by actually showing it what exactly not to select: "... AND NOT EXISTS(SELECT poll_answers.ID FROM poll_answers WHERE poll_answers.ID_question = poll_questions.ID AND poll_answers.visitor_ip = '".getIP()."')". As you can see we're using the visitor's IP address to determine if he already answered to that question (if we have an entry of that question with this IP in the answers table) and if we do, we skip it until there are no more questions - a moment when we show a message with a link to the chart representing the results of that survey (echo 'Thank you for taking this survey! You may <a href="'.$url.'charts.php?ID='.$_GET['ID'].'" title="view survey results" target="_blank">view results</a>';). You can also combine this application with PHP login and make it available for use with membership required and build your queries based on user ID. Ok. I already talked too much (or too little) for this application. Let's have some fun. Here's a demo (you won't be able to modify add or delete any survey). Download link, lower on the page.

For coding help and further support please use the forum.