Administrator Recipe: Edit Survey Questions

Just as the previous recipe allowed editing of the overall survey record, this recipe does the same for individual questions. In fact, the path to this page is through the link on the Edit Survey application. Moreover, many of the same techniques applied in the previous recipe, such as those for deleting a record and all its associated parts, are used here.

Step 1: Implement the Edit Survey Question Design

The layout for the Edit Survey Question design follows that of the Add Survey Question page. There are only two real differences: First, the name of the associated survey is displayed atop the form, and second, a Delete button is added.

1. Create a new dynamic page through the File > New dialog or by using a template.

   In the SurveyBuilder folder, locate the folder for your server model and open the edit_question page there.

2. Insert a table in the Content region of your page to hold the form elements for the Add Survey Question application.

   From the Snippets panel, drag the Recipes > SurveyBuilder > Wireframes > Edit Survey Questions Wireframe snippet into the Content editable region.

3. Add the form and the required elements: a drop-down list to choose the question type, a text field for entering the question, a text area field for the answers, and three form buttons one for submitting the question, another for deleting it, and one for clearing the form. ColdFusion and PHP users will add a hidden form element later.

   Place your cursor in the row below the words EDIT SURVEY QUESTION and insert the Recipes > SurveyBuilder > Forms > Edit Survey Question - Form snippet [r5-9].

   
   

   Figure r5-9.

   

   
   

4. Save the file.

Step 2: Adding Database Components

Although it would appear that only the questions data source would be required here, a second recordset one for the surveys is also needed. All the standard question modifications are updated through the Questions recordset, as you would expect. The survey recordset is needed only to supply the survey title; however, an interesting wrinkle is the way this recordset is filtered: through a value contained in the Questions recordset. Because one recordset is dependent on another, the order the recordsets are declared is vital; the Question recordset must execute before the Survey recordset.

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

2. In the dialog's simple view, enter an appropriate name for the recordset.

   Enter Question in the Name field.

3. Choose a connection (data source) to use.

   Select Recipes from the Connection list.

4. Choose the table in the data source to work with.

   Select SurveyQuestions from the Table list.

5. Leave the Columns option set to All.

6. In the Filter area of the Recordset dialog, set the four Filter list elements like this [r5-10]:

   

   
   

   Figure r5-10.

   

   
   

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

Now that the Question recordset is defined, we can use one of its values to filter the Survey recordset.

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

2. Switch to the advanced view of the Recordset dialog.

3. Enter an appropriate name for the recordset.

   Enter Survey in the Name field.

4. Choose a connection (data source) to use.

   Select Recipes from the Connection list.

5. In the SQL area, enter the following code:

   	SELECT * FROM Surveys WHERE QuestionID = IDParam
   	SELECT * FROM Surveys WHERE QuestionID = IDParam
   	SELECT * FROM Surveys WHERE QuestionID = #URL.ID#
   	SELECT * FROM surveys WHERE surveys.QuestionID = IDParam

The remaining steps are for ASP and PHP users only. ColdFusion users can click OK to close the dialog at this point.

6. In the Variable section, choose Add (+) to add a new variable.

7. In the Name column, enter a name for your variable.

   Enter IDParam.

8. Enter a default value.

   Enter 0 in the Default Value column.

9. In the Run-Time Value column, enter the following code:

   	Question.Fields("QuestionSurvey").
   	$row_Question["QuestionSurvey"]

10. Click OK to close the dialog and insert the recordset.

Step 3: Data Binding Process

In this step, we'll bind the data made available through the two defined recordsets to various sections and elements on the page. As noted earlier, the Survey recordset is only used for one dynamic element: the related survey's title. The rest of the bound data is drawn from the Question recordset. In addition to the obvious form elements on the page, ColdFusion and PHP users also need to bind a SurveyID to a hidden element to allow the Update Record server behavior, attached later, to work properly

We'll start with the Survey recordset and then continue with the Question recordset.

1. From the Bindings panel, expand the Survey recordset, if necessary.

2. Insert the needed dynamic data onto the page.

   Drag the SurveyName data field into the table cell to the right of the Survey Name label.

3. In the Bindings panel, expand the Question recordset.

4. Select the QuestionType drop-down list.

5. From the Property inspector, choose Dynamic to open the Dynamic List/Menu dialog.

6. Choose the lightning bolt next to the Select Value Equal To field.

7. In the Dynamic Data dialog, select QuestionType from the Question recordset and click OK twice: once to close the Dynamic Data dialog and again to close the Dynamic List/Menu dialog.

8. Add the required dynamic data to the form elements.

   Drag QuestionText onto the QuestionText text field.

   Drag QuestionAnswers onto the AnswerText text area.

The remaining steps are for ColdFusion and PHP users only.

9. From the Forms tab of the Insert bar, drag a hidden form element onto the table cell next to the explanatory text.

10. Enter QuestionID as the name for the hidden element on the Property inspector.

11. Choose the lightning bolt symbol to open the Dynamic Data dialog.

12. From the Question recordset, choose QuestionID and click OK to close the dialog.

Step 4: Update Record for Survey Question

The standard Update Record server behavior serves us well on this page.

For ASP

1. From the Server Behaviors panel, choose Add (+) and select Update Record.

2. In the Update Record dialog, select the desired data source connection. Choose Recipes from the Connection list.

3. Choose the table that contains the data you are updating.

   From the Table to Update list, choose SurveyQuestions.

4. Choose the recordset from which to get data source fields.

   From the Select Record From field, choose Question.

5. Set the primary key for the recordset.

   From the Unique Key Column list, choose QuestionID and make sure the Numeric option is selected.

6. Select the file you would like to appear when the update is complete.

   For the After Updating, Go To field, select the edit_survey.asp page.

7. Choose the form on the page from which to obtain the values.

   From the Get Values From list, choose EditSurveyQuestion.

8. In the Form Elements area, set the form elements to their corresponding data source fields.

   Set the QuestionType form element to update the QuestionType data source field as Numeric type.

   Set the QuestionText form element to update the QuestionText data source field as Text type.

   Set the AnswerText form element to update the QuestionAnswer data source field as Text type.

9. Make sure your entries are correct and then click OK to close.

For ColdFusion and PHP

1. From the Server Behaviors panel, choose Add (+) and select Update Record.

2. In the Update Record dialog, choose the current form.

   Select EditSurveyQuestion from the Submit Values From list.

3. Select your data source from the list.

   Choose Recipes from the Data Source list.

4. Enter your username and password, if needed.

5. Select the table in the data source to insert into from the list.

   Choose SurveyQuestions (surveyQuestions for PHP) from the Insert into Table list.

6. Set the data source fields to their corresponding form elements.

   Set QuestionID to the FORM.QuestionID form element and submit it as Numeric, with the Primary Key option selected.

   Make sure QuestionSurvey does not get a value.

   Set QuestionType to the FORM.QuestionType form element and submit as Numeric (Integer in PHP).

   Set QuestionText to the FORM.QuestionText form element and submit as Text.

   Set QuestionAnswers to the FORM.AnswerText form element and submit as Text.

7. In the After Inserting, Go To field, enter the path to the file you want displayed after the record is updated.

   Choose Browse and select the edit_survey.cfm or edit_survey.php file, as appropriate.

8. Check your entries to verify they are correct and, if so, click OK.

Step 5: Add Delete Command/Query for Survey Question

If you worked through the Edit Survey recipe, these next steps for adding delete functionality should be familiar. These steps are, in fact, a subset of the earlier steps. The basic procedure is the same: Commands (queries in ColdFusion) are added (two instead of three); the commands and other delete-related code are moved to the top of the code; and conditional logic is applied to the adjusted code. We'll start by adding the commands/queries.

One last similarity between the two recipes: Again, you should not test the page until all the delete-associated operations are complete.

For ASP

First, we'll insert a SQL command to remove the current record. The ID for the current record, MM_recordId, is gathered from the Update Server behavior, applied earlier.

1. From the Bindings panel, choose Command.

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

   Enter DeleteQuestion in the Name field.

3. Choose the data source.

   From the Connections list, select Recipes.

4. Set the type of command.

   From the Type list, choose Delete.

5. Leave the Return Recordset Named options blank.

6. In the SQL area, enter the following code:

   DELETE FROM SurveyQuestions.

    WHERE QuestionID = IDParam 

7. In the Variable area, choose Add (+) to add a new SQL variable.

8. Enter the name of the variable.

   In the Name column, enter IDParam.

9. Insert the run-time value.

   In the Run-Time value column, enter Request.Form("MM_recordId") [r5-11].

   
   

   Figure r5-11.

   

   
   

10. Click OK to insert the command.

Next, we'll access a command included in the data source to remove any results that are attached to the previously removed questions.

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

2. Enter an appropriate name for the command.

   Enter RemoveOrphanResults in the Name field.

3. Choose a data source.

   From the Connections list, choose Recipes.

4. Select the proper type of command.

   Choose Stored Procedure from the Type list.

5. Leave the Return Recordset Named options empty.

6. In the SQL area, enter the following command call:

    RemoveOrphanResults 

7. Click OK to close the dialog and insert the command.

Note

Again, refrain from testing the delete command until you've completed the remaining steps in this chapter.

For ColdFusion

The first task is to insert a SQL command to remove the current record. We'll get the ID for the current record from the session variable SurveyID stored earlier.

1. From the Bindings panel, choose Recordset (Query).

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

   Enter DeleteQuestion in the Name field.

3. Choose the data source.

   From the Data Source list, select Recipes.

4. Enter the username and password, if required.

5. In the SQL area, enter the following code:

   

    DELETE FROM SurveyQuestions WHERE QuestionID = #Form.QuestionID# 

6. Click OK to insert the command.

The next command to insert serves to remove any results attached to the previously removed questions.

1. From the Bindings panel, choose Recordset (Query).

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

   Enter DeleteQuestion in the Name field.

3. Choose the data source.

   From the Data Source list, select Recipes.

4. Enter the username and password, if required.

5. In the SQL area, enter the following code:

   

    EXECUTE RemoveOrphanResults 

6. Click OK to insert the command.

Note

Again, refrain from testing the delete command until you've completed the remaining steps in this chapter.

For PHP

As with the Edit Survey page, PHP developers need to use custom code to add delete functionality. The upside of this is that no additional action is necessary for this page.

1. In Code view, place your cursor after the line that begins <?php require_once and insert the following code:

   From the Snippets panel, insert the Recipes > SurveyBuilder > CustomCode_PHP > Edit Question - Delete Record snippet.

    <?php mysql_select_db($database_Recipes_PHP, $Recipes_PHP); if ((isset($_POST['Delete'])) && ($_POST['Delete'] == "Delete")) {      // First delete the selected record     $deleteSQL = "DELETE FROM surveyquestions WHERE     QuestionID = " . $_POST['QuestionID'];     $deleteRes = mysql_query($deleteSQL,$Recipes_PHP);     // Now remove orphaned results     $deleteSQL = "DELETE surveyresults.* FROM surveyquestions RIGHT JOIN surveyresults ON  surveyquestions.QuestionID=surveyresults.ResultQuestion WHERE     surveyquestions .QuestionID IS NULL";     $deleteRes = mysql_query($deleteSQL,$Recipes_PHP); } ?> 

2. Save your page.

Feel free to test your page completely. When you're ready, you can continue with the next page in the application: Launch Survey.

Step 6: Move Commands' Source Code

Now that the commands/queries are in place, we need to adjust their position in the code. To do this, we'll move both commands and one additional function. Let's start with the commands.

Note

The remainder of the steps for this page is for ASP and ColdFusion users only.

1. Switch to Code view.

2. In the Server Behaviors panel, choose the DeleteQuestion command/query.

   The first of the two commands is selected in the code.

3. Select the current code function as well as the one following it. The selected code should look like this:

   	[View full width] <% set DeleteQuestion = Server.CreateObject("ADODB.Command") DeleteQuestion.ActiveConnection = MM_Recipes_VB_STRING DeleteQuestion.CommandText = "DELETE FROM SurveyQuestions WHERE QuestionID = " + Replace(DeleteQuestion__IDParam, "'", "''") + "" DeleteQuestion.CommandType = 1 DeleteQuestion.CommandTimeout = 0 DeleteQuestion.Prepared = true DeleteQuestion.Execute() %> <% set RemoveOrphanResults = Server.CreateObject("ADODB.Command") RemoveOrphanResults.ActiveConnection = MM_Recipes_STRING RemoveOrphanResults.CommandText = "RemoveOrphanResults" RemoveOrphanResults.CommandType = 4 RemoveOrphanResults.CommandTimeout = 0 RemoveOrphanResults.Prepared = true RemoveOrphanResults.Execute() %>
   	[View full width] <% var DeleteQuestion = Server.CreateObject("ADODB.Command"); DeleteQuestion.ActiveConnection = MM_Recipes_STRING; DeleteQuestion.CommandText = "DELETE FROM SurveyQuestions WHERE QuestionID = "+ DeleteQuestion__IDParam.replace(/'/g, "''") + " "; DeleteQuestion.CommandType = 1; DeleteQuestion.CommandTimeout = 0; DeleteQuestion.Prepared = true; DeleteQuestion.Execute(); %> <% set RemoveOrphanResults = Server.CreateObject("ADODB.Command") RemoveOrphanResults.ActiveConnection = MM_Recipes_STRING RemoveOrphanResults.CommandText = "RemoveOrphanResults" RemoveOrphanResults.CommandType = 4 RemoveOrphanResults.CommandTimeout = 0 RemoveOrphanResults.Prepared = true RemoveOrphanResults.Execute() %>
   	<cfquery name="DeleteQuestion" datasource="Recipes"> DELETE FROM SurveyQuestions WHERE QuestionID=#FORM.QuestionID# </cfquery> <cfquery name="RemoveOrphanResults" datasource="Recipes"> EXECUTE RemoveOrphanResults </cfquery>

4. Cut the code by choosing Edit > Cut or by pressing Ctrl-X (Command-X).

5. Place the cursor at the top of the page. ASP users should place the cursor just after the Connection Include.

6. Paste the code by choosing Edit > Paste or by pressing Ctrl-V (Command-V).

7. Save the page.

Next, we'll need to move the delete operation declaration. (This step is only necessary for the ASP server models.)

1. In Code view, use Find and Replace to locate the first instance of DeleteQuestion__IDParam. (Note the double-underscore in the phrase.)

2. Cut the surrounding function by choosing Edit > Cut or by pressing Ctrl-X (Command-X). The code to cut looks like this:

   	[View full width] <% if(Request("MM_recordId") <> "") then DeleteQuestion__IDParam = Request("MM_recordId") %>
   	[View full width] <% if(String(Request("MM_recordId")) != "undefined") { DeleteQuestion__IDParam = String(Request("MM_recordId"));}} %>

3. Move to the top of the page above the two commands moved previously.

4. Paste the code by choosing Edit > Paste or by pressing Ctrl-V (Command-V).

Step 7: Add Conditional Code

To make sure the various delete code functions are executed only when the Delete button is pressed, we need to wrap the functions with conditional statements one before and one after the functions. The closing statement also serves to redirect the user to the Edit Survey page after the deletion is complete.

1. In Code view, position the cursor before the delete operation declaration just moved.

2. Insert the following code:

   From the Snippets panel, open the Recipes > SurveyBuilder > Custom Code folder for your server model and insert the If Statement - Delete Operation snippet.

   	<% if (cStr(Request("Delete"))<>"") then %>
   	<% if (String(Request("Delete"))!="undefined") { %>
   	[View full width] <cfif IsDefined("Session.SurveyID") AND IsDefined("FORM .Delete")>

   Now let's add the second half of our conditional code.

3. Place the cursor below the second delete command, which took care of removing the orphaned survey responses.

4. Insert the following code:

   From the Snippets panel, open the Recipes > SurveyBuilder > Custom Code folder for your server model and insert the Redirect Edit Survey snippet.

   	<% Response.Redirect("edit_survey.asp") end if %>
   	<% Response.Redirect("edit_survey.asp"); } %>
   	<cflocation url="edit_survey.cfm"> </cfif>

5. Save the page.