| The Update Employee page uses the standard Dreamweaver Update Record server behavior and includes a few enhancements. In addition to modifying an employee record, code on this page can also delete the record. Advanced SQL statements are used here to combine data from two related tables in a single drop-down list. Step 1: Implement Update Employee Design Build the basic static page before inserting any dynamic elements. 1. | Create a basic dynamic page, either by hand or derived from a template.
 In the EmployeeLookup folder, locate the folder for your server model and open the update_employee page found there.
| 2. | Add a table to the content region of your page to contain the interface elements for the application.
From the Snippets panel, drag the Recipes > EmployeeLookup > Wireframes > Update Employee - Wireframe snippet into the Content editable region.
| 3. | Within the table, insert the form and necessary form elements for the employee record. For this page, you'll need either a text field or a list for every data source field you want to update, as well as submit and delete buttons. As with the Advanced Search page, our example uses five text fields and one list element [r2-13].
r2-13. 
Place your cursor in the row below the words UPDATE EMPLOYEE and insert the Recipes > EmployeeLookup > Forms > Update Employee - Form snippet.
| 4. | ColdFusion and PHP users: A unique record ID is required for the Update Record server behavior to work properly. A hidden form field conveys the needed data.
ColdFusion and PHP developers should drag a hidden form field from the Forms category of the Insert bar and name it EmployeeID.
| 5. | Save your page.
|
Step 2: Add Database Components Two recordsets power the employee update page. As you might expect, one recordset contains the details from the selected employee's record; this recordset uses the familiar technique of filtering on a URL parameter. 1. | From the Bindings panel, choose Add (+) and select Recordset.
| 2. | With the Simple Recordset dialog displayed, enter a meaningful name for the recordset, such as Employees.
| 3. | Select the connection to your data source.
Choose Recipes from the Connection (Data Source) list.
| 4. | Select the table containing the employee record details.
Choose Employees (employees for PHP) from the Table list.
| 5. | Keep the Columns option set to All.
| 6. | In the Filter area of the Recordset dialog, set the four Filter list elements like this:
EmployeeID | = (Equals) | URL Parameter | ID |
| 7. | Leave the Sort option set to None and click OK to close the dialog.
|
The second recordset is somewhat more advanced and concerns the JobTitle list element. Job titles can be present in more than one department. (Each department could have a manager and an assistant manager, for example.) Therefore, the JobTitle list element needs to show both the name of the job and, parenthetically, the department the employee is in. For ASP and ColdFusion, this is handled through a combination of an Access view and the nesting of one SQL statement inside another. The Access view joins fields from the Jobs and Department tables found in the data source: [View full width] SELECT [Jobs].[JobTitle] & ' (' & [Departments].[DepartmentName] & ')' AS JobAndDepartment  , Jobs.JobID FROM Jobs INNER JOIN Departments ON Jobs.JobDept = Departments.DepartmentID;
PHP does not allow this type of nesting (also called subselects). A different SQL statement must be used: SELECT jobswithdepartments.* FROM jobswithdepartments LEFT JOIN employees ON jobswithdepartments.JobID= employees.EmployeeJob WHERE employees.EmployeeJob IS NULL OR (employees.EmployeeID = IDParam)
In addition, PHP users will have to take some extra steps to manipulate the recordsets and compensate for the lack of view support. For ASP Before you begin this step, you'll need to place your cursor in any text in Design view and then copy the SQL code from the appropriate snippet by right-clicking the Recipes > EmployeeLookup > SQL > Jobs RS - ASP SQL Statement snippet and then, from the context menu, choosing Copy Snippet. 1. | From the Bindings panel, choose Add (+) and select Recordset (Query).
| 2. | In the advanced Recordset dialog, enter an appropriate name for the recordset.
Enter Jobs in the Name field.
| 3. | Choose your connection from the drop-down list.
Select Recipes from the Connection list.
| 4. | In the SQL area, enter the following code:
Paste the copied snippet into the SQL field by pressing Ctrl-V (Command-V):
SELECT * FROM JobsWithDepartments WHERE Jobs.JobID NOT IN (SELECT EmployeeJob AS JobID FROM Employees WHERE EmployeeID <> IDParam)
| 5. | In the Variable area, choose Add (+) and enter IDParam in the Name column.
| 6. | In the Default Value column, enter 0.
| 7. | In the Run-Time Value column, enter Request.QueryString("ID") and click OK to close the dialog and insert the recordset.
| 8. | Save the page.
|
For ColdFusion Before you begin this step, you'll need to place your cursor in any text in Design view and then copy the SQL code from the appropriate snippet by right-clicking the Recipes > EmployeeLookup > SQL > Jobs RS - CFML SQL Statement snippet and then, from the context menu, choosing Copy Snippet. 1. | From the Bindings panel, choose Add (+) and select Recordset (Query).
| 2. | In the advanced Recordset dialog, enter an appropriate name for the recordset.
Enter Jobs in the Name field.
| 3. | Choose your data source from the drop-down list.
Select Recipes from the Data Source list.
| 4. | If necessary, enter the username and password for the data source in the corresponding fields.
| 5. | In the SQL area, enter the following code:
Paste the copied snippet into the SQL field by pressing Ctrl-V (Command-V):
SELECT * FROM JobsWithDepartments WHERE Jobs.JobID NOT IN (SELECT EmployeeJob AS JobID FROM Employees WHERE EmployeeID <> #URL.ID#)
| 6. | In the Page Parameters area, choose Add (+) to open the Add Parameter dialog.
| 7. | Make sure that User.ID is selected in the Name list of the Add Parameter dialog and enter 0 as the Default Value. When you're ready, click OK to close the dialog.
| 8. | Verify your code, and click OK to close the dialog and insert the recordset.
|
For PHP Before you begin this step, you'll need to place your cursor in any text in Design view and then copy the SQL code from the appropriate snippet by right-clicking the Recipes > EmployeeLookup > SQL > Jobs RS - PHP SQL Statement snippet and then, from the context menu, choosing Copy Snippet. 1. | From the Bindings panel, choose Add (+) and select Recordset (Query).
| 2. | In the advanced Recordset dialog, enter an appropriate name for the recordset.
Enter Jobs in the Name field.
| 3. | Choose your connection from the drop-down list.
Select Recipes from the Connection list.
| 4. | In the SQL area, enter the following code:
Paste the copied snippet into the SQL field by pressing Ctrl-V (Command-V):
SELECT jobswithdepartments.* FROM jobswithdepartments LEFT JOIN employees ON jobswithdepartments.JobID= employees.EmployeeJob WHERE employees.EmployeeJob IS NULL OR (employees.EmployeeID = IDParam)
| 5. | In the Variable area, choose Add (+) and enter IDParam in the Name column.
| 6. | In the Default Value column, enter 0.
|
In the Run-Time Value column, enter $_GET['ID'] and click OK to close the dialog and insert the recordset. When you add two or more recordsets in PHP, Dreamweaver merges them. For our purposes, you'll need to separate the Employees recordset from the Jobs recordset. Once separated, additional code is wrapped around the Jobs recordset to make it temporary. 1. | From the Server Behaviors panel, select the Jobs recordset entry.
| 2. | Switch to Code view to find the highlighted combined recordsets.
| 3. | Locate the first code line of the second recordset:
| $IDParam_Jobs = "0";
|
| 4. | Insert the following code before the identified code:
| ?> <?php
|
| 5. | Select the Jobs recordset that starts with the newly inserted opening <?php tag. The selected code should be:
| 6. | Insert the following code:
From the Snippets panel, open the Recipes > EmployeeLookup > CustomCode_PHP folder and insert the Temporary Query - JobsWithDepartments snippet.
| Before: | | | [View full width] <?php mysql_select_db($database_Recipes, $Recipes); $query_JobsWithDepartments = "INSERT INTO jobswithdepartments SELECT CONCAT(jobs.JobTitle,' (',departments.DepartmentName,')') AS JobAndDepartment, jobs.JobID FROM jobs INNER JOIN departments ON jobs.JobDepartment = departments .DepartmentID;"; mysql_query($query_JobsWithDepartments,$Recipes); ?>
|
| After: | | | [View full width] <?php $query_JobsWithDepartments = "DELETE FROM jobswithdepartments"; mysql_query($query_JobsWithDepartments,$Recipes); ?>
|
| 7. | Save the page.
|
Step 3: Update Employee Profile After defining the recordsets, we're ready to apply Dreamweaver's Update Record server behavior. For ASP 1. | From the Server Behaviors panel, choose Add (+) and select Update Record.
| 2. | In the Update Record dialog, select your connection from the list.
Choose Recipes from the Connection list.
| 3. | From the list, select the table in the data source to modify.
Choose Employees from the Table to Update list.
| 4. | Choose the recordset to use.
Select Employees from the Select Record From list.
| 5. | Select the Unique Key Column from the list; if the data is a number, make sure the Numeric option is chosen.
Select EmployeeID from the list.
| 6. | Select the path to the file you want the user to visit after the record has been updated.
Choose Browse and locate the employee_results.asp file.
| 7. | With the current form selected in the Get Values From list, set the form elements to their corresponding data source fields:
| Set the FirstName form element to the EmployeeFirst data column and submit as Text. | | | Set the LastName form element to the EmployeeLast data column and submit as Text. | | | Set the Email form element to the EmployeeEmail data column and submit as Text. | | | Set the Phone form element to the EmployeePhone data column and submit as Text. | | | Set the Phone2 form element to the EmployeeMobile data column and submit as Text. | | | Set the JobTitle form element to the EmployeeJob data column and submit as Numeric. | | | Set the CubeNumber form element to the EmployeeCube data column and submit as Numeric. |
| 8. | Verify your choices and click OK to close the dialog.
| 9. | Save the page.
|
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 UpdateEmployee 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 update from the list.
Choose Employees (employees for PHP) from the Update Table list.
| 6. | Set the data source fields to their corresponding form elements.
| Make sure the EmployeeID data column is set to FORM.ElementID as a Numeric type (Integer in PHP) and set as the Primary Key. | | | Set EmployeeJob to the FORM.JobTitle form element and submit as Numeric for ColdFusion and Integer for PHP. | | | Set EmployeeFirst to the FORM.FirstName form element and submit as Text. | | | Set EmployeeLast to the FORM.LastName form element and submit as Text. | | | Set EmployeeEmail to the FORM.Email form element and submit as Text. | | | Set EmployeePhone to the FORM.Phone form element and submit as Text. | | | Set EmployeeMobile to the FORM.Phone2 form element and submit as Text. | | | Set EmployeeCube to the FORM.CubeNumber form element and submit as Numeric/Integer. |
| 7. | Select the path to the file you want the user to visit after record has been updated.
Choose Browse and locate the employee_results file for your server model.
| 8. | Verify your choices and click OK to close the dialog.
|
For ColdFusion users only The standard Dreamweaver Update Server Behavior is really intended for pages where there is only one submit button. Because we've incorporated a delete button as well as an update button, we'll need to adjust the opening <cfif> tag to ensure that the update code executes only when the Update button is clicked. 1. | From the Server Behaviors panel, select the Update Record entry to locate the appropriate code.
| 2. | Switch to Code view and locate the opening code for the Update Record server behavior:
| [View full width] <cfif IsDefined("FORM.MM_UpdateRecord") AND FORM .MM_UpdateRecord EQ "UpdateEmployee">
|
| 3. | Change the <cfif> to this code:
| [View full width] <cfif IsDefined("FORM.MM_UpdateRecord") AND IsDefined("FORM.UpdateEmployee")>
|
| 4. | Save your page when you're done.
|
Step 4: Insert Delete Command As we did in Recipe 1, "User Login," we're going to combine code for deleting a record on a page containing an Update Record server behavior. The procedure is basically the same. Again, we're adding a specialized SQL statement (called a command in ASP), which we can do through the Dreamweaver interface. Once the command is inserted, we need to move it to the top of the page and wrap it in an If statement so that it only executes when we click the Delete button. For ASP 1. | From the Bindings panel, choose Add (+) and select Command.
| 2. | In the Name field, enter an appropriate name.
Enter DeleteCommand in the Name field.
| 3. | Choose your data source connection from the list.
Select Recipes from the Connection list.
| 4. | Choose a command type.
Choose Delete from the Type drop-down list.
| 5. | Enter the following code in the SQL field:
DELETE FROM Employees WHERE EmployeeID = IDParam
| 6. | In the Variables section, select Add (+) and enter IDParam under the Name column.
| 7. | In the Run-Time Value column, enter Request("MM_recordId") and click OK when you're done.
Inserting the command creates two code blocks. We'll need to move both of them so that the Delete command is processed before the Update command is. First, we'll move the code block we just created. If you haven't selected anything else, it should still be highlighted.
| 8. | In Code view, find the code block for the Delete command, which will look like this:
| [View full width] <% set DeleteCommand = Server.CreateObject("ADODB .Command") DeleteCommand.ActiveConnection = MM_Recipes_STRING DeleteCommand.CommandText = "DELETE FROM Employees WHERE EmployeeID = " + Replace (DeleteCommand__IDParam, "'", "''") + "" DeleteCommand.CommandType = 1 DeleteCommand.CommandTimeout = 0 DeleteCommand.Prepared = true DeleteCommand.Execute() %>
|
| [View full width] <% var DeleteCommand = Server.CreateObject("ADODB .Command"); DeleteCommand.ActiveConnection = MM_Recipes_STRING; DeleteCommand.CommandText = "DELETE FROM Employees WHERE EmployeeID "+ DeleteCommand__IDParam.replace (/'/g, "''") + " "; DeleteCommand.CommandType = 1; DeleteCommand.CommandTimeout = 0; DeleteCommand.Prepared = true; DeleteCommand.Execute(); %>
|
| 9. | Cut the selected code block and paste it at the top of the page just under the <!include> statement.
| 10. | Locate and cut this related code block:
| [View full width] <% if(Request("MM_recordId") <> "") then DeleteCommand__IDParam = Request("MM_recordId") %>
|
| [View full width] <% if(String(Request("MM_recordId")) != "undefined"){ DeleteCommand__IDParam = String(Request ("MM_recordId"));} %>
|
| 11. | Paste the just-cut code block after the <!include> tag and before the Delete command.
The last part of this step is to make sure that the Delete command executes only after we select the Delete button on the form.
| 12. | In Code view, locate and select the two adjacent Delete command code blocks moved to the top of the page.
| 13. | Insert the following code:
From the Snippets panel, open the Recipes > EmployeeLookup > CustomCode folder for your server model and insert the Edit User - Conditional snippet.
| Before: | | | <% if (cStr(Request.Form("DeleteEmployee"))<>"") then %>
|
| After: | | | <% Response.Redirect("employee_results.asp") end if %>
|
| Before: | | | [View full width] <% if (String(Request.Form ("DeleteEmployee"))!="undefined") { %>
|
| After: | | | <% Response.Redirect("employee_results.asp"); } %>
|
|
You can, of course, change the page to redirect after the delete is completed, if you would prefer to use something other than the employee_results page. For ColdFusion 1. | From the Bindings panel, choose Add (+) and select Recordset.
Unlike the ASP server models, there is no separate user interface in ColdFusion for commands. The Recordset dialog is used.
| 2. | If the Simple view is displayed, select Advanced.
| 3. | In the Name field, enter an appropriate name.
Enter DeleteCommand in the Name field.
| 4. | Choose your data source connection from the list.
Select Recipes from the Data Source list.
| 5. | If necessary, enter the username and password in their respective fields.
| 6. | Enter the following code in the SQL field:
DELETE FROM Employees WHERE EmployeeID = #FORM.EmployeeID#
| 7. | In the Page Parameters section, select Add (+) to display the Add Parameter dialog.
| 8. | In the Add Parameter dialog, enter FORM.EmployeeID in the Name field.
| 9. | In the Default Value fields, enter 0 and click OK to close the Add Parameter dialog.
| 10. | When you're done, click OK to close the Recordset dialog.
Now, all that remains to complete the Delete command is to make sure it executes only when the user selects the Delete button on the form. To effect this process, we'll wrap the Delete command code block within an If statement.
| 11. | In Code view, find the code block for the Delete command, which looks like this:
<cfquery name="DeleteCommand" datasource="Recipes"> DELETE FROM Employees WHERE EmployeeID = #FORM.EmployeeID# </cfquery>
Select the code block to prepare for inserting the snippet.
| 12. | Insert the following code:
From the Snippets panel, open the Recipes > EmployeeLookup > CustomCode_CF folder and insert the Edit User - Conditional snippet.
| Before: | | | <cfif IsDefined("FORM.DeleteEmployee")>
|
| After: | | | <cflocation url="employee_results.cfm"> </cfif>
|
| 13. | Save your page.
|
If you'd prefer to redirect the page to something other than the employee_results page, feel free to adapt the code. For PHP PHP does not include direct support for commands, but you can use the Recordset dialog to enter the equivalent SQL. After the recordset has been inserted, we'll need to add some code to make its execution conditional on the pressing of the Delete button. 1. | In Code view, place your cursor beneath the connection code at the top of the file that starts <?php require-once... and enter the following code:
From the Snippets panel, insert the Recipes > EmployeeLookup > CustomCode_PHP > Delete Employee Record snippet.
| [View full width] <?php if (isset($_POST['EmployeeID'])) { mysql_select_db($database_Recipes, $Recipes); $query_DeleteOperation = "DELETE FROM employees WHERE EmployeeID = '".$_POST['EmployeeID']."'"; mysql_query($query_DeleteOperation,$Recipes); } ?>
|
The next part of this step is to make sure that the Delete command is executed only when someone selects the Delete button on the form.
| 2. | Select the just-inserted code block and enter the following code:
From the Snippets panel, insert the Recipes > EmployeeLookup > CustomCode_PHP > Edit User - Conditional snippet.
| Before: | | | <?php if (isset($HTTP_GET_VARS['DeleteEmployee'])) { ?>
|
| After: | | | <?php header("Location: employee_results.php"); } ?>
|
| 3. | Save your page.
|
Naturally, you're free to change the page to redirect to after the delete is completed to something other than the employee_results page. Step 5: Data Binding Process The final step for this page is to bind the recordset data to the various form elements. All but one of the form elements are text fields and can be bound by dragging the proper item from the Bindings panel. For the list element, you'll need to enter the information through the Dynamic List/Menu dialog, which is accessible through either the Property inspector's Dynamic button or the Server Behaviors panel. 1. | From the Bindings panel, expand the Employees recordset and drag the data source fields over their corresponding text fields.
| Drag the data source field EmployeeFirst to the form field FirstName. | | | Drag the data source field EmployeeLast to the form field LastName. | | | Drag the data source field EmployeeEmail to the form field Email. | | | Drag the data source field EmployeePhone to the form field Phone. | | | Drag the data source field EmployeeMobile to the form field Phone2. | | | Drag the data source field EmployeeCube to the form field CubeNumber. |
| 2. | Select the JobTitle list element.
| 3. | From the Property inspector, select Dynamic.
| 4. | In the Dynamic List/Menu dialog, select the Jobs recordset from the Options From Recordset list.
Remember that the Jobs recordset is derived from an Access view combining three different tables.
| 5. | In the Values field, choose JobID.
| 6. | In the Labels field, select JobAndDepartment.
| 7. | In the Select Value Equal To Field, click the lightning bolt icon and, from the Dynamic Data dialog, choose EmployeeJob from the Employee recordset.
| 8. | Click OK twice to close the Dynamic Data and Dynamic List/Menu dialogs.
| 9. | Save your page.
|
For PHP and ColdFusion Only It's time to add the data binding to the hidden form element previously inserted into the page. You'll recall that the hidden form element is called EmployeeID; now you'll link the value passed in the URL query string to it. 1. | Select the hidden form element inserted near the top of the form.
| 2. | In the Property inspector, enter the following code in the Value field:
| <cfoutput>#url.ID#</cfoutput>
|
| ?php echo $_GET['ID']; ?>
|
| 3. | Save your page.
|
By incorporating data fields from two recordsets in the list form element, we accomplish both of our goals. First, the connection to the EmployeeJob data field assures us that when the update page is opened, the employee's current job will be shown. Second, the list will display all available jobs in the proper department, and we can choose a new job. With all the form fields now attached to data, the Update Employee page is complete. |
