End User Recipe: Update Employee

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, five text fields and one list element are used in our example [r2-13].

   
   

   Figure r2-13.

   

   
   

   Place your cursor in the row below the words UPDATE 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 Connections or 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:

   

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 Job Title 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 Job Title 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 nesting 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) 

For ASP

Before you begin this step, you'll need to copy the SQL code from the appropriate snippet by right-clicking on Recipes > EmployeeLookup > SQL > Jobs RS - ASP SQL Statement snippet and then, from the context menu, choose 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 Connections 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 copy the SQL code from the appropriate snippet by right-clicking on Recipes > EmployeeLookup > SQL > Jobs RS - CFML SQL Statement snippet and then, from the context menu, choose 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 copy the SQL code from the appropriate snippet by right-clicking on Recipes > EmployeeLookup > SQL > Jobs RS - PHP SQL Statement snippet and then, from the context menu, choose 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 Connections 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.

7. In the Run-Time Value column, enter $_GET['ID'] and click OK to close the dialog and insert the recordset.

8. 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 Connections list.

3. Select the table in the data source to modify from the list.

   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 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 EmployeeJob to the FORM.JobTitle form element and submit as Numeric for ColdFusion and Integer for PHP.

   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.

Step 4: Insert Delete Command

As we did in the User LogIn recipe, 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 inserted, we need to move the command to the top of the page and wrap it in an If statement so that it only executes when we select 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 Connections 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 just created. If you haven't selected anything else, it still should 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:

    	<% if(Request("MM_recordId") <> "") then DeleteCommand__IDParam = Request("MM_recordId") %>
    	<% 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 > Custom Code folder for your server model and insert the Edit User - Conditional snippet.

    Before:

    

     <% if (cStr(Request.Form("Delete"))<>"")  then %> 

    After:

     <%   Response.Redirect("employee_results.asp") end if %> 

    Before:

    

     <% if (String(Request("Delete"))!="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 Users WHERE UserID = #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 two Delete command code blocks 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 > Custom Code-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 // mysql delete command if (isset($_GET['MM_recordId'])) {      mysql_select_db($database_Recipes, $Recipes);      $query_DeleteOperation = "DELETE FROM employees WHERE  EmployeeID = '".$_GET['MM_recordId']."'";      mysql_query($query_EmployeeLookup,$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 > Custom Code-PHP > Edit User - Conditional snippet.

   Before:

   

    <?php if (isset($_GET['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 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 either through the Dynamic button on the Property inspector 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, choose the lightning bolt symbol and, from the Dynamic Data dialog, choose EmployeeJob from the Employee recordset.

8. Click OK once to close the Dynamic Data dialog and then again to close the Dynamic List/Menu dialog.

9. 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 [r2-14].

Figure r2-14.