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. 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_manager page there. 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. From the Snippets panel, drag the Recipes > SurveyBuilder > Wireframes > Survey Manager Wireframe snippet into the Content editable region. 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. 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. 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 .SurveyEndDate, Count(DistinctResponses.QuestionSurvey) AS CountOfResponseSurvey, Surveys .SurveyLive, ([Surveys].[SurveyEndDate]>=Date() And [Surveys].[SurveyLive] And [Surveys] .[SurveyStartDate]<=Date()) AS LiveImage, [Surveys].[SurveyEndDate]<=Date() AS EndedImage FROM DistinctResponses RIGHT JOIN Surveys ON DistinctResponses.QuestionSurvey = Surveys .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 From the Bindings panel, choose Add (+) and select Recordset (Query). In the Recordset dialog, enter an appropriate name. Enter Surveys into the Name field. Select the desired data source connection. Choose Recipes from the Connection (Data Source) list. Choose the needed view. From the Tables list, select SurveyManagerQry. Leave the Columns option set to All. Make sure the Filter is set to None. Keep the Sort option set to None and click OK to close the dialog. 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: | 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. |
From the Bindings panel, choose Add (+) and select Recordset. In the advanced view of the Recordset dialog, enter an appropriate name for your recordset. Enter Surveys in the Name field. Pick the data source you want to use. Choose Recipes from the Connections list. Enter the following code in the SQL area: From the Snippets panel, press Ctrl-V (Command-V) to paste the copied snippet into the SQL text area. | [View full width] SELECT surveys.SurveyID, surveys.SurveyName, surveys .SurveyStartDate, surveys.SurveyEndDate, Count (distinctresponses.QuestionSurvey) AS CountOfResponseSurvey, surveys.SurveyLive, (surveys.SurveyEndDate>=CURDATE() AND surveys.SurveyLive AND surveys.SurveyStartDate<=CURDATE()) AS LiveImage, (surveys.SurveyEndDate<=CURDATE()) AS EndedImage FROM distinctresponses RIGHT JOIN surveys ON distinctresponses .QuestionSurvey = surveys.SurveyID GROUP BY surveys.SurveyID, surveys.SurveyName, surveys .SurveyStartDate, surveys.SurveyEndDate, surveys.SurveyLive; | 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. 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. Wrap the following code around the selected recordset: From the Snippets panel, insert the Recipes > Survey Builder > SQL > SurveyManager RS - PHP SQL snippet. Before: After: <?php $delSQL = "DELETE FROM distinctresponses"; $delRes = mysql_query($delSQL,$Recipes_PHP); ?> 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. From the Bindings panel, expand the Surveys recordset. Place the data fields on the page in their proper places. Drag SurveyName into the cell below the Survey Title label. ASP and ColdFusion users: Drag SurveyStartDate into the cell below the Start Date label. PHP users: Drag the Recipes > Survey Builder > CustomCode_PHP > Survey Manager - Reformat SurveyStartDate Column snippet into the cell below the Start Date Label. ASP and ColdFusion users: Drag SurveyEndDate into the cell below the End Date label. PHP users: Drag the Recipes > Survey Builder > CustomCode_PHP > Survey Manager - Reformat SurveyEndDate Column snippet into the cell below the End Date Label. Drag CountOfResponseSurvey into the cell below the View Results label. This field displays 0 until survey responses have been entered. ColdFusion users should apply date formatting to the two date fields. 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. 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. PHP users need to add another function to the page to reconvert the dates to their previous formats: 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. | [View full width] <?php function reformatDate($date) { return substr($date,5,2) . "/" . substr($date,8,2) . "/" . substr($date,0,4); } ?> | When you're done, save your page [r5-4].
Figure r5-4. 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. Select the text or image you want to link to the edit survey application. Choose the word icon below the Modify label. Select the folder symbol next to the Link field in the Property inspector. The Select File dialog opens. Make sure the dialog is set to Select File Name from File System. 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. In the Name column of the Parameters dialog, enter the variable name. Enter ID in the Name column. In the Value column, enter the dynamic value of the current survey's ID. 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. In the Select File dialog, select the file that will be used to edit the survey information. Choose edit_survey in the SurveyBuilder folder for your server model. When you're done, click OK to insert the link. Next, let's set the link to launch the survey. Select the text or image you want to link to the launch survey page. Choose the word icon below the Launch label. Select the folder symbol next to the Link field in the Property inspector. The Select File dialog opens. Make sure the dialog is set to Select File Name from File System. Choose Parameters at the bottom of the dialog. In the Name column of the Parameters dialog, enter the variable name. Enter ID in the Name column. In the Value column, enter the dynamic value of the current survey's ID. 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. In the Select File dialog, select the file that will be used to edit the survey information. Choose launch_survey in the SurveyBuilder folder for your server model. 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. Select the text or image you want to link to the test the survey page. Choose the word icon below the Test Survey label. Select the folder symbol next to the Link field in the Property inspector. The Select File dialog opens. Make sure the dialog is set to Select File Name from File System. Choose Parameters at the bottom of the dialog. In the Name column of the Parameters dialog, enter the variable name. Enter ID in the Name column. In the Value column, enter the dynamic value of the current survey's ID. 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. Choose Add (+) to add another parameter. In the Name column of the Parameters dialog, enter the variable name to set the testmode. Enter Preview in the Name column. In the Value column, enter the boolean value to set the preview mode. 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. In the Select File dialog, select the file that will be used to edit the survey information. Choose survey in the SurveyBuilder folder for your server model. 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. Select the CountOfResponseSurvey data field inserted on the page. Select the folder symbol next to the Link field in the Property inspector. The Select File dialog opens. Make sure the dialog is set to Select File Name from File System. Choose Parameters at the bottom of the dialog. In the Name column of the Parameters dialog, enter the variable name. Enter ID in the Name column. In the Value column, enter the dynamic value of the current survey's ID. 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. In the Select File dialog, select the file that will be used to edit the survey information. Choose survey_statistics in the SurveyBuilder folder for your server model. 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. Select any of the dynamic data fields. From the Tag Selector, choose the table row tag. Select the <tr.smallBlackText> tag from the Tag Selector. From the Server Behaviors panel, choose Add (+) and select Repeat Region. In the Repeat Region dialog, choose the desired recordset. Choose Surveys from the Recordset list. Set the Show option to display the number of records you would like. Choose Show All Records. 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. Select the <tr> tag used to apply the Repeat Region server behavior. Select the <tr.smallText> tag from the Tag Selector. Enter Code view. Move your cursor between the start of the Repeat Region code and the beginning of the <tr> tag. Press the left arrow key. Insert the following code: From the Snippets panel, open theRecipes > SurveyBuilder > Custom Code folder for your server model and insert the Determine Dynamic Image - SurveyStatus snippet. | <% 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 %> | | <% var StatusImage = "staged.gif" if (Surveys.Fields("LiveImage").value) { StatusImage = "live.gif" } if (Surveys.Fields("EndedImage").value) { StatusImage = "ended.gif" } %> | | <cfset StatusImage = "staged.gif"> <cfif (Surveys.LiveImage)> <cfset StatusImage = "live.gif"> </cfif> <cfif (Surveys.EndedImage)> <cfset StatusImage = "ended.gif"> </cfif> | | <?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. 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. Insert the following code: From the Snippets panel, open the Recipes > SurveyBuilder > Custom Code folder for your server model and insert the Dynamic Image - SurveyStatus snippet. | [View full width] <img src="/books/2/711/1/html/2/../../images/surveybuilder/<%=StatusImage%>" ALT="Survey Status" align="absmiddle"> | | [View full width] <img src="/books/2/711/1/html/2/../../images/surveybuilder/<%=StatusImage%>" ALT="Survey Status" align="absmiddle"> | | [View full width] <img src="/books/2/711/1/html/2/../../images/surveybuilder/#StatusImage#" ALT="Survey Status" align="absmiddle"> | | [View full width] img src="/books/2/711/1/html/2/../../images/surveybuilder/<?php echo $StatusImage; ?>" 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. 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. 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. Place your cursor where you would like the number of surveys to appear. Select the text [survey_total] in the phrase above the header row. ASP users can use the Total Records data element from the Bindings panel: From the Bindings panel, expand the Surveys recordset and drag the Total Records data entry onto the selected text. ColdFusion and PHP users can use the Total Records object: 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. 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. |