Analysis of a survey's results is key to its success. The whole point of taking a survey is to find out what people think and, more precisely, what percentage of people surveyed think a particular way. The Survey Statistics page described here offers one way to visualize a survey's results. When executed by selecting the entry in the View Results column of the Survey Manager page the Survey Statistics application displays each of the answers to the questions broken down by percentages. If, for example, 50 out of 200 people surveyed chose yellow as their favorite color, the Survey Statistics page would show that yellow was chosen 25% of the time.
Generating the percentage calculations is the major objective of any statistics application and a chore best handled by SQL. We've provided a SQL view that draws data from three primary tables (Surveys, SurveyQuestions, and SurveyResults). After the view is used to create a recordset, implementation in Dreamweaver is a drag-and-drop affair.
Step 1: Implement Survey Statistics Design
The goal of the Survey Statistics page is to show how the responses to each question compare. Therefore, the page uses a structured layout that includes the question, the particular response, and the related percentage all of which is eventually wrapped in a Repeat Region server behavior.
Create a page for the Survey Manager recipe, either from a template or from the File > New dialog.
In the SurveyBuilder folder, locate the folder for your server model and open the survey_statistics page there.
In the Content area of your page, create a header for the page and an area for the survey response statistics.
From the Snippets panel, drag the Recipes > SurveyBuilder > Wireframes > Survey Statistics Wireframe snippet into the Content editable region.
In the structured data area, add a table with 4 rows and 2 columns; the first row, which holds the questions, should span both columns.
Place your cursor in the first row below the words SURVEY STATISTICS and insert the Recipes > SurveyBuilder > ContentTables > Survey Statistics - Content Table snippet.
Place your cursor in the bottom row, right-hand cell of the just-added table, and insert the Recipes > SurveyBuilder > ContentTables > Survey Question Statistics - Content Table snippet [r5-16].
Figure r5-16.
Save the page.
Step 2: Adding Database Components
As noted earlier, a SQL view is constructed to extract the survey results and calculate the percentages. The view is quite complex:
[View full width]
SELECT Surveys.SurveyID, Surveys.SurveyName, Count(SurveyResults.ResultAnswer) AS TimesAnswered, SurveyQuestions.QuestionSurvey, SurveyQuestions.QuestionAnswers, SurveyResults.ResultAnswer, SurveyQuestions.QuestionID, SurveyResults.ResultQuestion, (SELECT Count(SurveyResults2.ResultQuestion) FROM SurveyResults AS SurveyResults2 WHERE (((SurveyResults2.ResultQuestion)=SurveyResults.ResultQuestion))) AS QuestionAnswered, (Count(SurveyResults.ResultAnswer)/(SELECT Count(SurveyResults2.ResultQuestion) FROM SurveyResults AS SurveyResults2 WHERE (((SurveyResults2.ResultQuestion)=SurveyResults .ResultQuestion)))) AS AnswerPercent, SurveyQuestions.QuestionText FROM (SurveyResults INNER JOIN SurveyQuestions ON SurveyResults.ResultQuestion = SurveyQuestions.QuestionID) INNER JOIN Surveys ON SurveyQuestions.QuestionSurvey = Surveys .SurveyID GROUP BY Surveys.SurveyID, Surveys.SurveyName, SurveyQuestions.QuestionSurvey, SurveyQuestions.QuestionAnswers, SurveyResults.ResultAnswer, SurveyQuestions.QuestionID, SurveyResults.ResultQuestion, SurveyQuestions.QuestionText ORDER BY SurveyQuestions.QuestionID;
Because Dreamweaver lists SQL views as just another data source for ASP and ColdFusion, creating the needed recordset is quite straightforward; PHP developers need to add some custom code in addition to the recordset.
From the Bindings panel, choose Add (+) and select Recordset (Query) from the list.
In the simple Recordset dialog, enter an appropriate name for the recordset.
Enter Statistics in the Name field.
Choose your data source.
From the Connections (Data Source) list, select Recipes.
Choose the prebuilt view.
Select SurveyStatistics (surveystatistics for PHP) from the Table list.
Leave Columns set to All.
In the Filter area of the Recordset dialog, set the four Filter list elements like this:
Leave Sort set to None and click OK to close the dialog.
Save the page.
PHP developers must also insert code to build a temporary table, following our often-used technique for simulating views with MySQL:
In Code view, place the cursor after the line near the top of the page that starts <?php require_once and insert the following code:
From the Snippets panel, insert the Recipes > SurveyBuilder > CustomCode_PHP > Survey Statistics Calculate Temporary Table snippet.
[View full width]
<?php // First retrieve the total of questions answered mysql_select_db($database_Recipes_PHP, $Recipes_PHP); $query_QuestionAnswered = "SELECT surveyquestions.QuestionID , surveys.SurveyName, surveyquestions.QuestionText, COUNT (surveyresults.ResultQuestion) AS total FROM surveys, surveyquestions, surveyresults, surveyresults surveyresults_1 WHERE (((surveyresults.ResultQuestion = surveyquestions.QuestionID) AND (surveyquestions .QuestionSurvey = surveys.SurveyID)) AND (surveyresults_1 .ResultID = surveyresults.ResultID)) GROUP BY surveys .SurveyName, surveyquestions.QuestionText"; $QuestionAnswered = mysql_query($query_QuestionAnswered, $Recipes_PHP) or die(mysql_error()); $row_QuestionAnswered = mysql_fetch_assoc($QuestionAnswered); $totalRows_QuestionAnswered = mysql_num_rows($QuestionAnswered); // empty the statistics table $cleanupSQL = "DELETE FROM surveystatistics"; $cleanupRS = mysql_query($cleanupSQL,$Recipes_PHP); // retrieve statistics $insSQL = "INSERT INTO surveystatistics SELECT NULL, surveys .SurveyID, surveys.SurveyName, Count(surveyresults .ResultAnswer) AS TimesAnswered, surveyquestions .QuestionSurvey, surveyquestions.QuestionAnswers, surveyresults.ResultAnswer, surveyquestions.QuestionID, surveyresults.ResultQuestion, surveyquestions.QuestionText,0 ,0 FROM (surveyresults INNER JOIN surveyquestions ON surveyresults.ResultQuestion = surveyquestions.QuestionID) INNER JOIN surveys ON surveyquestions.QuestionSurvey = surveys.SurveyID GROUP BY surveys.SurveyID, surveys .SurveyName, surveyquestions.QuestionSurvey, surveyquestions .QuestionAnswers, surveyresults.ResultAnswer, surveyquestions .QuestionID, surveyresults.ResultQuestion, surveyquestions .QuestionText ORDER BY surveyquestions.QuestionID"; $insRS = mysql_query($insSQL,$Recipes_PHP); do { $insSQL = "UPDATE surveystatistics SET QuestionAnswered = " . $row_QuestionAnswered['total'] . " WHERE QuestionID = " . $row_QuestionAnswered['QuestionID']; $insRS = mysql_query($insSQL,$Recipes_PHP); } while ($row_QuestionAnswered = mysql_fetch_assoc ($QuestionAnswered)); // retrieve TotalAnswered and QuestionAnswered columns $insSQL = "SELECT StatsID, (TimesAnswered/QuestionAnswered) AS AnswerPercent FROM surveystatistics"; $insRS = mysql_query($insSQL,$Recipes_PHP); $insRow = mysql_fetch_assoc($insRS); do { $updSQL = "UPDATE surveystatistics SET AnswerPercent='" .$insRow['AnswerPercent']."' WHERE Statsfont-weight:normal" value="2">
When you're done, save the page.
Step 3: Data Binding Process
The steps for completing this page begin with binding four data fields. After applying a format to the percentage field, you'll attach a Repeat Region server behavior.
From the Bindings panel, expand the Statistics recordset.
Place the dynamic text elements in their proper place on the page.
Drag SurveyName next to the Survey Title label.
Drag QuestionText in the first cell below the Survey Question heading.
Drag ResultAnswer to the second row below the Survey Question heading, in the cell on the left.
Drag AnswerPercent to the same row, below the Survey Question heading, in the cell on the right.
Select the Statistics.AnswerPercent data field on the page.
Format the data field in the following manner:
In the Bindings panel, select the arrow next to the highlighted entry in the Format column and, from the list, choose Percent > Default [r5-17].
Figure r5-17.
The Default setting for the Percent format allows decimals to two places, so 1/3 is shown as 33.33%. You can, of course, choose another percentage format.
Now that we have our dynamic elements on the page, all that remains is to apply a Repeat Region server behavior.
Select the area you want to repeat.
From the Tag Selector, choose the <tr> tag that encompasses the Survey Question heading and all the dynamic data.
The easiest way to accomplish this is to first select the nested table that encompasses the heading and the dynamic data and then choose the <tr> tag to the left of the selected table in the Tag Selector.
From the Server Behaviors panel, choose Add (+) and select Repeat Region from the list.
In the Repeat Region dialog, choose the desired recordset.
Select Statistics from the Recordset list.
Select the Show All Records option.
Click OK to close the dialog [r5-18].
Figure r5-18.
Charting Survey Statistics
The percent values returned in the Survey Statistic page provide an accurate overview of the results but the representation could be more compelling. Although the statistics are perfectly understandable, the numbers are not as immediately apparent as the same values in a chart would be. A pie chart, for example, showing that over one-half of the survey respondees favored a particular approach is immediately recognizable and very persuasive.
Charting dynamic statistics requires linking real-time graphics engine to data a task Macromedia Flash is definitely suited for, but difficult to program. WebAssist offers an extension that allows Dreamweaver developers to link their dynamics data to a variety of charts. Combining both the data and the graphics, WA Dynamic Flash Charts lets you visually analyze your data.
To find out more about WA Dynamic Flash Charts, visit http://www.webassist.com/Products/Recipes/WADynamicFlashCharts.asp
In the 
TimesAnswered, SurveyQuestions.QuestionSurvey, SurveyQuestions.QuestionAnswers, 
In the Bindings panel, select the arrow next to the highlighted entry in the Format column and, from the list, choose 
