Administrator Recipe: Survey Manager

After the surveys and their associated questions are developed, you need a way to interact with them. The Survey Manager recipe shows how to construct a page that provides both an overview of existing surveys and a gateway to altering them. You can even examine results to a particular survey.

The Survey Manager uses a structured table in which each row represents a survey. For any survey, you can tell at a glance if the survey is currently live, what its starting and ending dates are, and how many responses to the survey have been logged. Moreover, any survey entry contains links for modifying the survey itself (and, subsequently, its questions), testing the survey, or launching it. Because this is a fair amount of information to present in a table, icons are used to symbolize survey stats and tasks.

A fairly advanced SQL view also known as a query of queries is used to pull together data from several tables and another view. Most of the data is associated with the table through the Bindings panel or through standard links. However, some hand-coding is required to display the proper icon based on the data returned for a particular survey. In addition, we'll add some code to display the total number of surveys available.

Step 1: Implement the Survey Manager Design

To display the data for the existing surveys, you use a multicolumned table structure. The table needs a header row and a data row; before you complete the page, you must apply a Repeat Region server behavior to the data row.

  1. Create a page for the Survey Manager recipe, either from a template or from the File > New dialog.

    graphics/book.gif In the SurveyBuilder folder, locate the folder for your server model and open the survey_manager page there.

  2. In the Content area of your page, create a header for the page, a placeholder to display the number of surveys, and an area for the structured data.

    graphics/book.gif From the Snippets panel, drag the Recipes > SurveyBuilder > Wireframes > Survey Manager Wireframe snippet into the Content editable region.

  3. In the structured data area, add a table (2 rows and 7 columns) with the following table headers: Survey Title, Start Date, End Date, Modify, Launch, Test Survey, and View Responses.

    graphics/book.gif Place your cursor in the row below the words SURVEY MANAGER and insert the Recipes > SurveyBuilder > ContentTables > Survey Manager - Content Table snippet [r5-3].


    Figure r5-3.

    graphics/07fig03.jpg


  4. Save the page.

Step 2: Add Database Components

The data represented in the Survey Manager is gathered from three data source tables (Surveys, SurveyQuestions, and SurveyResponses) and another view (DistinctResponses) that combines the latter two tables to eliminate repetitive information. Here's the entire SQL statement for ASP and ColdFusion:

 

[View full width]

SELECT Surveys.SurveyID, Surveys.SurveyName, Surveys.SurveyStartDate, Surveys graphics/ccc.gif.SurveyEndDate, Count(DistinctResponses.QuestionSurvey) AS CountOfResponseSurvey, Surveys graphics/ccc.gif.SurveyLive, ([Surveys].[SurveyEndDate]>=Date() And [Surveys].[SurveyLive] And [Surveys] graphics/ccc.gif.[SurveyStartDate]<=Date()) AS LiveImage, [Surveys].[SurveyEndDate]<=Date() AS EndedImage FROM DistinctResponses RIGHT JOIN Surveys ON DistinctResponses.QuestionSurvey = Surveys graphics/ccc.gif.SurveyID GROUP BY Surveys.SurveyID, Surveys.SurveyName, Surveys.SurveyStartDate, Surveys.SurveyEndDate, Surveys.SurveyLive;
 

The core SQL for PHP is similar. Creating such a SQL statement by hand obviously requires a concerted effort; however, because Dreamweaver exposes data source views as well as data source tables, ASP and ColdFusion developers can apply this to the page through the simple Recordset dialog; PHP users need to use the advanced Recordset dialog as well as a couple of helper functions to build and remove a temporary table.

For ASP and ColdFusion
  1. From the Bindings panel, choose Add (+) and select Recordset (Query).

  2. In the Recordset dialog, enter an appropriate name.

    graphics/book.gif Enter Surveys into the Name field.

  3. Select the desired data source connection.

    graphics/book.gif Choose Recipes from the Connection (Data Source) list.

  4. Choose the needed view.

    graphics/book.gif From the Tables list, select SurveyManagerQry.

  5. Leave the Columns option set to All.

  6. Make sure the Filter is set to None.

  7. Keep the Sort option set to None and click OK to close the dialog.

  8. Save the page after inserting the recordset.

For PHP

As mentioned earlier, PHP developers need to add their SQL statement by hand due to a limitation in MySQL. After the recordset is inserted, we'll add two functions one before and one after to create and remove a necessary temporary table.

Let's start by creating the recordset:

graphics/book.gif

As a preliminary step, from the Snippets panel, use the Copy Snippet command to copy the Recipes > Survey Builder > SQL > SurveyManager RS - PHP SQL Statement snippet to the Clipboard.


  1. From the Bindings panel, choose Add (+) and select Recordset.

  2. In the advanced view of the Recordset dialog, enter an appropriate name for your recordset.

    graphics/book.gif Enter Surveys in the Name field.

  3. Pick the data source you want to use.

    graphics/book.gif Choose Recipes from the Connections list.

  4. Enter the following code in the SQL area:

    graphics/book.gif From the Snippets panel, press Ctrl-V (Command-V) to paste the copied snippet into the SQL text area.

    graphics/php.gif

    [View full width]

     SELECT surveys.SurveyID, surveys.SurveyName, surveys graphics/ccc.gif.SurveyStartDate, surveys.SurveyEndDate, Count graphics/ccc.gif(distinctresponses.QuestionSurvey) AS CountOfResponseSurvey, graphics/ccc.gif surveys.SurveyLive, (surveys.SurveyEndDate>=CURDATE() AND graphics/ccc.gif surveys.SurveyLive AND surveys.SurveyStartDate<=CURDATE()) graphics/ccc.gif AS LiveImage, (surveys.SurveyEndDate<=CURDATE()) AS EndedImage FROM distinctresponses RIGHT JOIN surveys ON graphics/ccc.gif distinctresponses .QuestionSurvey = surveys.SurveyID GROUP BY surveys.SurveyID, surveys.SurveyName, surveys  graphics/ccc.gif.SurveyStartDate, surveys.SurveyEndDate, surveys.SurveyLive; 

  5. When you're done, click OK to close the dialog and insert the recordset.

To simulate the necessary view, we'll create a temporary table before the recordset is inserted and then delete it afterward. This requires placing a custom PHP function on either side of the recordset.

  1. From the Server Behaviors panel, select the just inserted recordset and switch to Code view.

    Selecting the recordset from the panel highlights it in Code view.

  2. Wrap the following code around the selected recordset:

    graphics/book.gif From the Snippets panel, insert the Recipes > Survey Builder > SQL > SurveyManager RS - PHP SQL snippet.

    Before:

    graphics/php.gif

    [View full width]

     <?php mysql_select_db($database_Recipes_PHP, $Recipes_PHP); $tempSQL = "INSERT INTO distinctresponses SELECT DISTINCT graphics/ccc.gif surveyresults.ResultSession, surveyquestions.QuestionSurvey graphics/ccc.gif FROM surveyresults,surveyquestions WHERE surveyresults graphics/ccc.gif.ResultQuestion=surveyquestions.QuestionID;"; $tempRes = @mysql_query($tempSQL,$Recipes_PHP); ?> 

    After:

     
     <?php $delSQL = "DELETE FROM distinctresponses"; $delRes = mysql_query($delSQL,$Recipes_PHP); ?> 
     
  3. When you're done, save the page.

Step 3: Data Binding Process

With the recordset in place, we're ready to bind the data to specific table cells. All the binding can be handing by dragging entries from the Bindings panel onto the page. ColdFusion users must apply some additional formatting to the date fields after insert them. PHP uses custom formatting code as well as a conversion function to properly handle the date values.

  1. From the Bindings panel, expand the Surveys recordset.

  2. Place the data fields on the page in their proper places.

    graphics/book.gif Drag SurveyName into the cell below the Survey Title label.

    graphics/vb.gifgraphics/js.gifgraphics/cf.gif ASP and ColdFusion users: Drag SurveyStartDate into the cell below the Start Date label.

    graphics/php.gif PHP users: Drag the Recipes > Survey Builder > CustomCode_PHP > Survey Manager - Reformat SurveyStartDate Column snippet into the cell below the Start Date Label.

    graphics/vb.gifgraphics/js.gifgraphics/cf.gif ASP and ColdFusion users: Drag SurveyEndDate into the cell below the End Date label.

    graphics/php.gif PHP users: Drag the Recipes > Survey Builder > CustomCode_PHP > Survey Manager - Reformat SurveyEndDate Column snippet into the cell below the End Date Label.

    graphics/book.gif Drag CountOfResponseSurvey into the cell below the View Results label. This field displays 0 until survey responses have been entered.

  3. ColdFusion users should apply date formatting to the two date fields.

    graphics/cf.gif Select the Start Date field inserted on the page and, from the Format column of the selected entry in the Bindings panel, choose Date/Time > 1/17/00 or whichever date format you would prefer.

    graphics/cf.gif Select the End Date field inserted on the page and, from the Format column of the selected entry in the Bindings panel, choose Date/Time > 1/17/00 or whichever date format you would prefer.

  4. PHP users need to add another function to the page to reconvert the dates to their previous formats:

    graphics/php.gif In Code view, position your cursor at the end of the document and, from the Snippets panel, insert the Recipes > SurveyBuilder > CustomCode_PHP > Survey Manager - Reformat Date snippet.

    graphics/php.gif

    [View full width]

     <?php function reformatDate($date) {     return substr($date,5,2) . "/" . substr($date,8,2) . "/" graphics/ccc.gif . substr($date,0,4); } ?> 

  5. When you're done, save your page [r5-4].


    Figure r5-4.

    graphics/07fig04.jpg


Step 4: Managing Session Data in Hyperlinks

The remaining columns in the on-page table those that didn't use a data field from the recordset are all links to other dynamic pages. You can either use image or text as the basis for your links for three out of the four links; the fourth link is assigned to a data field. In each case, the link passes a parameter that identifies the currently selected survey so that the proper data can be inserted into the linked page.

Note

As noted, the links can either be text or images. In this example, text links are used. If an image is preferred, make the substitution before beginning the following steps. In the Recipe files, you'll find three images available for your use as icons; the images (icon_modify.gif, icon_launch.gif, and icon_test.gif) are located in the Recipes/images/surveybuilder folder.


First, let's create the link for editing a survey.

  1. Select the text or image you want to link to the edit survey application.

    graphics/book.gif Choose the word icon below the Modify label.

  2. Select the folder symbol next to the Link field in the Property inspector.

    The Select File dialog opens.

  3. Make sure the dialog is set to Select File Name from File System.

  4. Choose Parameters at the bottom of the dialog.

    Although it might seem counterintuitive, we'll assign the parameter before we select the file. Because many operating systems are set to accept a single mouse click as a selection, if we select the file first, the dialog will choose just the file and close before the parameter can be set.

  5. In the Name column of the Parameters dialog, enter the variable name.

    graphics/book.gif Enter ID in the Name column.

  6. In the Value column, enter the dynamic value of the current survey's ID.

    graphics/book.gif Select the lightning bolt next in the Value column and from the Dynamic Data dialog, choose SurveyID. When you're done, click OK once to close the Dynamic Data dialog and again to close the Parameters dialog.

  7. In the Select File dialog, select the file that will be used to edit the survey information.

    graphics/book.gif Choose edit_survey in the SurveyBuilder folder for your server model.

  8. When you're done, click OK to insert the link.

Next, let's set the link to launch the survey.

  1. Select the text or image you want to link to the launch survey page.

    graphics/book.gif Choose the word icon below the Launch label.

  2. Select the folder symbol next to the Link field in the Property inspector.

    The Select File dialog opens.

  3. Make sure the dialog is set to Select File Name from File System.

  4. Choose Parameters at the bottom of the dialog.

  5. In the Name column of the Parameters dialog, enter the variable name.

    graphics/book.gif Enter ID in the Name column.

  6. In the Value column, enter the dynamic value of the current survey's ID.

    graphics/book.gif Select the lightning bolt next to the Value column, and from the Dynamic Data dialog, choose SurveyID. When you're done, click OK once to close the Dynamic Data dialog and again to close the Parameters dialog.

  7. In the Select File dialog, select the file that will be used to edit the survey information.

    graphics/book.gif Choose launch_survey in the SurveyBuilder folder for your server model.

  8. When you're done, click OK to insert the link.

The third link to set is for testing the survey. In this case, we'll link to the same page used to actually run the survey, except that we'll pass an additional parameter that prevents the survey from storing the results.

  1. Select the text or image you want to link to the test the survey page.

    graphics/book.gif Choose the word icon below the Test Survey label.

  2. Select the folder symbol next to the Link field in the Property inspector.

    The Select File dialog opens.

  3. Make sure the dialog is set to Select File Name from File System.

  4. Choose Parameters at the bottom of the dialog.

  5. In the Name column of the Parameters dialog, enter the variable name.

    graphics/book.gif Enter ID in the Name column.

  6. In the Value column, enter the dynamic value of the current survey's ID.

    graphics/book.gif Select the lightning bolt next to the Value column, and from the Dynamic Data dialog, choose SurveyID. When you're done, click OK once to close the Dynamic Data dialog.

  7. Choose Add (+) to add another parameter.

  8. In the Name column of the Parameters dialog, enter the variable name to set the testmode.

    graphics/book.gif Enter Preview in the Name column.

    In the Value column, enter the boolean value to set the preview mode.

    graphics/book.gif Enter true in the Value column; when you're done, click OK once to close the Dynamic Data dialog and again to close the Parameters dialog.

  9. In the Select File dialog, select the file that will be used to edit the survey information.

    graphics/book.gif Choose survey in the SurveyBuilder folder for your server model.

  10. When you're done, click OK to insert the link.

The final link displays the results for a given survey and is connected to the dynamic value in the View Results column.

  1. Select the CountOfResponseSurvey data field inserted on the page.

  2. Select the folder symbol next to the Link field in the Property inspector.

    The Select File dialog opens.

  3. Make sure the dialog is set to Select File Name from File System.

  4. Choose Parameters at the bottom of the dialog.

  5. In the Name column of the Parameters dialog, enter the variable name.

    graphics/book.gif Enter ID in the Name column.

  6. In the Value column, enter the dynamic value of the current survey's ID.

    graphics/book.gif Select the lightning bolt next to the Value column and from the Dynamic Data dialog, choose SurveyID. When you're done, click OK once to close the Dynamic Data dialog and again to close the Parameters dialog.

  7. In the Select File dialog, select the file that will be used to edit the survey information.

    graphics/book.gif Choose survey_statistics in the SurveyBuilder folder for your server model.

  8. When you're done, click OK to close the dialog and insert the link.

Step 5: Adding Repeat Region

Next, we'll apply a Repeat Region server behavior to display a number of survey entries. The Repeat Region is applied to the table row that contains the dynamic data fields previously inserted.

  1. Select any of the dynamic data fields.

  2. From the Tag Selector, choose the table row tag.

    graphics/book.gif Select the <tr.smallBlackText> tag from the Tag Selector.

  3. From the Server Behaviors panel, choose Add (+) and select Repeat Region.

  4. In the Repeat Region dialog, choose the desired recordset.

    graphics/book.gif Choose Surveys from the Recordset list.

  5. Set the Show option to display the number of records you would like.

    graphics/book.gif Choose Show All Records.

  6. Click OK when you're done.

Step 6: Adding Dynamic Status Image

Rather than add another column to show the status of the survey whether it is ready to go, currently running, or completed we'll use a simple image. A little hand-coding is required to display the proper image for each survey. The code used is basically a conditional statement that sets a variable, StatusImage, to one of three image files: staged.gif, live.gif, or ended.gif. The variable is then used as the src attribute for the image.

Note

If this application is going to have a lot of surveys in the database, it is a good idea to limit the Repeat Region to 10 records and then add recordset navigation to the page. If you use this technique, then all applications that link to this page, such as add_survey, must use GET instead of POST for the <form> tag containing the link.


The LiveImage and EndedImage data fields used to determine which image to display are calculated fields that are constructed in the SQL statement. These fields are based on the starting and ending dates for each survey. If today's date precedes the start date, then neither LiveImage nor EndedImage is set to true. If today's date follows the start date but precedes the completion date, then LiveImage is set to true. Finally, if today's date follows the survey's ending date, then the EndedImage field is set to true.

  1. Select the <tr> tag used to apply the Repeat Region server behavior.

    graphics/book.gif Select the <tr.smallText> tag from the Tag Selector.

  2. Enter Code view.

  3. Move your cursor between the start of the Repeat Region code and the beginning of the <tr> tag.

    graphics/book.gif Press the left arrow key.

  4. Insert the following code:

    graphics/book.gif From the Snippets panel, open theRecipes > SurveyBuilder > Custom Code folder for your server model and insert the Determine Dynamic Image - SurveyStatus snippet.

    graphics/vb.gif

     <% StatusImage = "staged.gif" if (Surveys.Fields("LiveImage").value)  then StatusImage = "live.gif" end if if (Surveys.Fields("EndedImage").value)  then StatusImage = "ended.gif" end if %> 

    graphics/js.gif

     <% var StatusImage = "staged.gif" if (Surveys.Fields("LiveImage").value)  { StatusImage = "live.gif" } if (Surveys.Fields("EndedImage").value)  { StatusImage = "ended.gif" } %> 

    graphics/cf.gif

     <cfset StatusImage = "staged.gif"> <cfif (Surveys.LiveImage)> <cfset StatusImage = "live.gif"> </cfif> <cfif (Surveys.EndedImage)> <cfset StatusImage = "ended.gif"> </cfif> 

    graphics/php.gif

     <?php $StatusImage = "staged.gif"; if ($row_Surveys['LiveImage']) {     $StatusImage = "live.gif"; } else if ($row_Surveys['EndedImage']) {     $StatusImage = "ended.gif"; } ?> 

    Now that the code is set, let's insert the <img> tag with the variable src value.

  5. Place your cursor before the SurveyName dynamic text element. Add a space to keep a bit of distance between the image and the survey title.

    A nonbreaking space works the most consistently across the browsers and in Dreamweaver Live Data view.

  6. Insert the following code:

    graphics/book.gif From the Snippets panel, open the Recipes > SurveyBuilder > Custom Code folder for your server model and insert the Dynamic Image - SurveyStatus snippet.

    graphics/vb.gif

    [View full width]

     <img src="/books/2/711/1/html/2/../../images/surveybuilder/<%=StatusImage%>" graphics/ccc.gif ALT="Survey Status" align="absmiddle"> 

    graphics/js.gif

    [View full width]

     <img src="/books/2/711/1/html/2/../../images/surveybuilder/<%=StatusImage%>" graphics/ccc.gif ALT="Survey Status" align="absmiddle"> 

    graphics/cf.gif

    [View full width]

     <img src="/books/2/711/1/html/2/../../images/surveybuilder/#StatusImage#" graphics/ccc.gif ALT="Survey Status" align="absmiddle"> 

    graphics/php.gif

    [View full width]

     img src="/books/2/711/1/html/2/../../images/surveybuilder/<?php echo $StatusImage; graphics/ccc.gif ?>" ALT="Survey Status" align="absmiddle"> 

    This code assumes that staged.gif, live.gif, and ended.gif are stored in the images/surveybuilder folder two levels above the current page; if the path to your images is different, alter the code accordingly.

  7. Save your page.

Note

Dreamweaver inserts a 32x32 placeholder for the dynamic image. The actual images are 38 pixels wide by 32 pixels high. You can change the size of the placeholder by selecting it and then, in the Property inspector, entering modified values as needed in the Width and Height fields.


After some surveys have been entered, you can test the display of the images in Live Data view. Our example figure illustrates the use of graphics replacing the "icon" text [r5-5].


Figure r5-5.

graphics/07fig05.jpg


Step 7: Display Total Surveys

The finishing touch to this page is to display the total number of surveys in the system, whether staged, active, or completed. ASP users can drag a data field from the Bindings panel onto the page to accomplish this, whereas ColdFusion and PHP users can take advantage of a Dreamweaver application object.

  1. Place your cursor where you would like the number of surveys to appear.

    graphics/book.gif Select the text [survey_total] in the phrase above the header row.

  2. ASP users can use the Total Records data element from the Bindings panel:

    graphics/vb.gifgraphics/js.gif From the Bindings panel, expand the Surveys recordset and drag the Total Records data entry onto the selected text.

  3. ColdFusion and PHP users can use the Total Records object:

    graphics/cf.gifgraphics/php.gif Delete the placeholder text and choose Insert > Application Objects > Display Record Count > Total Records.

    The same object is available from the Application category of the Insert bar.

  4. Save your page.

As noted earlier, after you've added one or more surveys, you can test the page either in Live Data view or on your testing server.



Macromedia Dreamweaver MX 2004 Web Application Recipes
Macromedia Dreamweaver MX 2004 Web Application Recipes
ISBN: 0735713200
EAN: 2147483647
Year: 2003
Pages: 131

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net