In the best of all worlds, an administrator would never have to see a user's record, much less alter it. Very few administrators are lucky enough to live in such a world, however, so an administrative-strength edit record application is needed.
In addition to the functionality we've already seen in the user-oriented edit record page, the administrator also requires the capability to alter a user's access group level and to delete the record altogether. Moreover, to maintain security, the SQL statements involved in this page are somewhat more complex than those in the user version.
Step 1: Implement Edit User Design
Let's begin by building the basic page.
1.
Open a new dynamic page, either constructing one by hand or deriving one from a template.
In the UserLogin folder, locate the folder for your server model and open the edit_user 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 > UserLogin > Wireframes > Edit User - Wireframe snippet into the Content editable region.
3.
Within the table, insert the form and any necessary form elements for the application. You'll need five text fieldsone for each of the fields (First Name, Last Name, User Name, Password, and Email Address)and a select list to hold the Group Access data. In addition, this application requires three buttons: Update, Delete, and Cancel.
Place your cursor in the row below the words EDIT USER in the second cell and insert the Recipes > UserLogin > Forms > Edit User - Form snippet [r1-23].
r1-23.
4.
ColdFusion and PHP users: Both of these server models require a unique record ID for the Update Record server behavior to work properly. In these situations, a hidden form field is used to convey the needed data.
ColdFusion and PHP developers should drag a Hidden Field object from the Forms category of the Insert bar and name it UserID.
5.
Save the page.
Step 2: Add Database Components
The Edit User page displays when a link is selected in the Manage Users page. The link selected contains a URL parameter, ID, which we will use on this page to filter one of the two recordsets required. The second recordset gets the values necessary to dynamically fill the Access Group list.
1.
From the Server Behaviors panel, choose Add (+) and select Recordset (Query).
2.
Using the simple view of the Recordset dialog, give the recordset a meaningful name.
Enter Users in the Recordset field.
3.
Choose the connection for your data source.
Select Recipes from the Connection list.
4.
Choose the table.
Select Users (users 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:
UserID
= (Equals)
URL Parameter
ID
7.
Leave the Sort option set to None, and then click OK to confirm your choices and close the dialog.
8.
Save the page.
The second recordset, AccessGroups, is just as straightforward.
1.
From the Bindings panel, choose Add (+) and select Recordset (Query).
2.
In the Recordset dialog's simple view, enter the desired name, such as AccessGroups.
3.
Select your data source connections.
Choose Recipes from the Connections list.
4.
Select the table containing the user information.
Choose AccessGroups (accessgroups for PHP) from the Table list.
5.
Leave the Filter and Sort options at their default settings and click OK to close the dialog.
Step 3: Data Binding Process
The data binding procedure for this page is identical to the one for the edit profile page, with one exception. The Access Group form elementa drop-down listmust be bound to data from the accessgroup table. ColdFusion and PHP users will also need to bind the current record ID to the hidden form field.
1.
In the Bindings panel, make sure the Users recordset entry is expanded so that all data columns are displayed.
2.
Drag each of the following data columns over the corresponding text field:
Drag the column UserFirstName to the field FirstName.
Drag the column UserLastName to the field LastName.
Drag the column UserName to the field UserName.
Drag the column Password to the field Password.
Drag the column UserEmail to the field EmailAddress.
3.
Leave the default formatting for all dynamic text fields.
Let's now set up the list element to populate dynamically.
4.
Select the Access Group list element.
5.
In the Property inspector, click the Dynamic button to open the Dynamic List/Menu dialog.
6.
Verify that the AccessGroup list element is selected in the Menu list.
7.
From the Options From Recordset list, choose AccessGroups.
8.
Set the Values list to AccessGroupID.
9.
Set the Labels (what the user sees) to AccessGroupNames.
10.
In the Select Value Equal To field, click the lightning bolt icon to open the Dynamic Data dialog.
11.
Expand the Users recordset, if necessary, and choose the UserAccess - field.
Linking the selected value to the UserAccess/AccessGroup field, the record will initially display the current user group level while allowing the administrator to adjust it if necessary.
12.
Click OK to close the Dynamic Data dialog. After you've confirmed your selections, click OK in the Dynamic List/Menu dialog to close it.
ColdFusion and PHP users need to complete one additional set of steps in binding dynamic data to the hidden form field previously inserted.
1.
Select the form element.
Choose the UserID hidden form element.
2.
Bind the current record ID to the element.
Click the lightning bolt icon next to the Value field in the Property inspector to open the Dynamic Data dialog.
In the Dynamic Data dialog, expand the Users recordset and choose UserID.
Click OK to close the dialog.
3.
Save your page.
Step 4: Update User Profile
Now we're ready to insert the Update Record server behavior. As part of the procedure, we'll set the behavior to display the Manage User page after the update is complete so that the administrator can continue working.
For ASP
1.
From the Server Behaviors panel, choose Add (+) and select Update Record to display the dialog.
2.
Select the connection to the data source.
Choose Recipes from the Connection list.
3.
Choose the table containing the user data.
From the Table to Update list, select Users.
4.
Select the recordset from which to get data source fields.
Set the Select Record From field to Users.
5.
Set the Primary Key for the recordset.
From the Unique Key Column list, choose UserID and make sure that the Numeric option is checked.
6.
Enter the page you want the users to see after successfully registering.
In the After Inserting, Go To field, click the Browse button and locate the user_manager file for your server model.
7.
Choose the form on the page from which the values are to be taken.
Set the Get Values From field to EditUser.
8.
For the form elements shown in the list, set each one to its equivalent in the data source. All form elements should be submitted as Text type, except AccessGroup, which should be Numeric type:
Set form element FirstName to field UserFirstName as Text type.
Set form element LastName to field UserLastName as Text type.
Set form element UserName to field UserName as Text type.
Set form element Password to field UserPassword as Text type.
Set form element EmailAddress to field UserEmail as Text type.
Set form element AccessGroup to field UserAccess as Numeric type.
9.
When you're done, click OK to close the dialog and insert the behavior.
10.
Save your 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 EditUsers from the Submit Values From list.
3.
Select your data source from the list.
Choose Recipes from the Data Source list.
4.
Enter your user name and password, if needed.
5.
From the list, select the table in the data source to insert into.
Choose Users (users for PHP) from the Insert Into Table list.
6.
Set the data source fields to their corresponding form elements.
As the Primary Key, UserID selects the record using FORM.UserID as Numeric (Integer in PHP) type.
Set UserAccess to get its value from the FORM.AccessGroup from element as Numeric (Integer in PHP).
Set UserFirstName to get its value from the FORM.FirstName form element as Text.
Set UserLastName to get its value from the FORM.LastName form element as Text.
Set UserName to get its value from the FORM.UserName form element as Text.
Set UserPassword to get its value from the FORM.Password form element as Text.
Set UserEmail to get its value from the FORM.EmailAddress form element as Text.
Make sure UserRegDate does not get a value.
7.
In the After Inserting, Go To field, enter the path to the file you want displayed after the record is updated.
In the After Inserting, Go To field, select the user_manager file for your server model.
8.
Check your entries to verify that they are correct; if so, click OK.
9.
Save your page.
Step 5: Add a Delete Command
If you wanted to add a separate page to delete a record, you could use Dreamweaver's standard Delete Record server behavior. However, if you want to combine a couple of administrative tasks, as this application doessuch as updating and deleting records on the same pageyou have to take a different approach.
One method is to create a delete command. A command is a specialized SQL statement that, among other things, can delete, insert, or update records. Once the command is created using the Dreamweaver interface, the code must be moved above the Update Record behavior. The final step is to wrap the delete code in a conditional blockalso called an if statementso that it executes only when the Delete button is clicked.
For ASP
1.
From the Bindings panel, choose Add (+) and select Command.
The Command dialog displays [r1-24].
r1-24.
2.
Enter an appropriate name for the command.
Enter DeleteCommand in the Name field.
3.
Choose your data source connection from the list.
Select Recipes from the Connections list.
4.
Choose Delete for the Command Type.
5.
Enter the following code in the SQL field:
DELETE FROM Users WHERE UserID = IDParam
6.
In the Variables section, select Add (+) and enter IDParam under the Name column.
7.
In the Run-Time Value column, enter Request.Form("MM_recordId") and click OK when you're done.
Inserting the command creates two code blocks. We'll need to move one of them so that the delete command is processed before the update command.
8.
In Code view, find the code block for the delete command. It will look like this:
Cut the code block and move it to the top of the page, below the connection include code that starts with <!--#include.
Now all that remains to complete the delete command is to make sure it is executed only when the user clicks the delete button on the form. If this step is not taken, the delete command will run when this page is loadednot a desirable circumstance. To avoid such a disastrous situation, we'll wrap the two delete command code blocks within an if statement.
1.
In Code view, locate the two adjacent delete command code blocks.
Select the two code blocks to prepare for inserting the snippet.
2.
Insert the following code:
From the Snippets panel, open the Recipes > UserLogin > Custom Code folder for your server model and insert the Delete Button - If Statement snippet.
Before:
<% if (cStr(Request.Form("Delete"))<>"") then %>
After:
<% Response.Redirect(:user_manager.asp") end if %>
Before:
<% if (String(Request("Delete"))!="undefined") { %>
After:
<% Response.Redirect("user_manager.asp"); } %>
You can, of course, change the page to redirect to after the delete is completed, if your page is named something other than user_manager.asp.
For ColdFusion
1.
From the Bindings panel, choose Add (+) and select Recordset (Query).
Unlike with the ASP server models, there is no separate user interface in ColdFusion for Commands, and the Recordset dialog is used.
2.
If the simple view is displayed, select Advanced.
3.
In the Name field, enter an appropriate name.
Enter DeleteOperation 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 user name and password in their respective fields.
6.
Enter the following code in the SQL field:
DELETE FROM Users WHERE UserID = #FORM.UserID#
7.
In the Page Parameters section, select Add (+) to display the Add Parameter dialog.
8.
In the Add Parameter dialog, enter FORM.UserID 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 is executed only when the user clicks the delete button on the form. To do this, we'll wrap the two delete command code blocks within an If statement.
11.
In Code view, find and cut the code block for the delete command. It will look like this:
<cfquery name="DeleteCommand" datasource="Recipes"> DELETE FROM Users WHERE USERID = #FORM.UserID# </cfquery>
12.
Locate the corresponding <cfparam> statement near the top of the file:
<cfparam name="FORM.UserID" default="0">
13.
Select the two code blocks to prepare for inserting the snippet, both the <cfparam> and <cfquery> statements.
14.
Insert the following code:
From the Snippets panel, insert the Recipes > UserLogin > Custom Code-CF > Delete Button - If Statement snippet.
You can, of course, change the page to redirect to after the delete is completed, if your page is named something other than user_manager.cfm.
For PHP
Adding a delete button in PHP is simpler than in other server models and requires the addition of a single function.
1.
Switch to Code view and, if you've inserted the Restrict Access to Page server behavior, place your cursor after that code block located by selecting that entry in the Server Behaviors panel - and press Enter (Return). Otherwise, position your cursor at the end of the opening line that begins <?php require_once, and press Enter (Return).
2.
Enter the following code:
Insert the Recipes > UserLogin > Custom Code-PHP > Edit User - Delete User snippet.
[View full width]
<?php if ((isset($_POST['Delete'])) && ($_POST['Delete']=="Delete")) { mysql_select_db($database_Recipes, $Recipes-); $deleteSQL = "DELETE FROM users WHERE User0" width="14" height="9" align="left" src="/books/2/710/1/html/2/images/ccc.gif" />.$_POST['UserID']; $deleteRS = mysql_query($deleteSQL,$Recipes); header("Location: user_manager.php"); } ?>
3.
Save the page.
Step 6: Cancel Editing Process
As we did on the Update Record page, we'll use some JavaScript code to implement the cancel button.
1.
Select the Cancel form button.
2.
From the Behaviors panel, select Add (+) and choose Call JavaScript.
3.
In the Call JavaScript dialog, enter the following code:
history.back();
4.
When you're done, click OK to close the dialog.
5.
Save the file.
Step 7: Validate the User Name
One final aspect still needs to be addressed. Since this page can potentially alter the user name, we must make sure that the new name chosen is unique. Unfortunately, we cannot just apply the Check User Name server behavior as we did in the register_user page, because this particular server behavior requires that a variable, MM_flag, be set to MM_insert and, on the update page, the MM_flag be set to MM_update.
The solution is to copy the Macromedia code used on the register_user page and adapt it to our needs. In all, three changes need to be made. While these modifications will give us the functionality we need, it will also prevent further changes to the Update Record server behavior through the dialog. This is indicated by a red exclamation point next to the server behavior in the Server Behaviors panel.
1.
Open the register_user page in Code view and copy the following code block:
[View full width]
<% ' *** Redirect if username exists MM_flag="MM_insert" If (CStr(Request(MM_flag)) <> "") Then MM_dupKeyRedirect="register_user.asp?repeat=true" MM_rsKeyConnection=MM_Recipes_VB_STRING MM_dupKeyUsernameValue = CStr(Request.Form ("UserName")) MM_dupKeySQL="SELECT UserName FROM Users WHERE UserName='" & MM_dupKeyUsernameValue & "'" MM_adodbRecordset="ADODB.Recordset" set MM_rsKey=Server.CreateObject(MM_adodbRecordset) MM_rsKey.ActiveConnection=MM_rsKeyConnection MM_rsKey.Source=MM_dupKeySQL MM_rsKey.CursorType=0 MM_rsKey.CursorLocation=2 MM_rsKey.LockType=3 MM_rsKey.Open If Not MM_rsKey.EOF Or Not MM_rsKey.BOF Then ' the username was found - can not add the requested username MM_qsChar = "?" If (InStr(1,MM_dupKeyRedirect,"?") >= 1) Then MM_qsChar = "&" MM_dupKeyRedirect = MM_dupKeyRedirect & MM_qsChar & "requsername=" & MM_dupKeyUsernameValue Response.Redirect(MM_dupKeyRedirect) End If MM_rsKey.Close End If %>
[View full width]
<% // *** Redirect if username exists var MM_flag="MM_insert"; if (String(Request(MM_flag)) != "undefined") { var MM_dupKeyRedirect="register_user .asp?repeat=true"; var MM_rsKeyConnection=MM_Recipes_STRING; var MM_dupKeyUsernameValue = String(Request.Form ("UserName")); var MM_dupKeySQL = "SELECT UserName FROM Users WHERE UserName='" + MM_dupKeyUsernameValue + "'" var MM_adodbRecordset = "ADODB.Recordset"; var MM_rsKey = Server.CreateObject (MM_adodbRecordset); MM_rsKey.ActiveConnection = MM_rsKeyConnection; MM_rsKey.Source = MM_dupKeySQL; MM_rsKey.CursorType=0; MM_rsKey.CursorLocation=2; MM_rsKey.LockType=3; MM_rsKey.Open(); if (!MM_rsKey.EOF || !MM_rsKey.BOF) { // the username was found - can not add the requested username var MM_qsChar = "?"; if (MM_dupKeyRedirect.indexOf("?") >= 0) MM_qsChar = "&"; MM_dupKeyRedirect = MM_dupKeyRedirect + MM_qsChar + "requsername=" + MM_dupKeyUsernameValue; Response.Redirect(MM_dupKeyRedirect); } MM_rsKey.Close(); } %>
<?php // *** Redirect if username exists $MM_flag="MM_insert"; if (isset($_POST[$MM_flag])) { $MM_dupKeyRedirect="register_user.php?repeat=true"; $loginUsername = $_POST['UserName']; $LoginRS__query = "SELECT UserName FROM users WHERE UserName='"; . $loginUsername . "'"; mysql_select_db($database_Recipes, $Recipes); $LoginRS=mysql_query($LoginRS__query, $Recipes) or die(mysql_error()); $loginFoundUser = mysql_num_rows($LoginRS); //if there is a row in the database, the username was found - can not add the requested username if($loginFoundUser){ $MM_qsChar = "?"; //append the username to the redirect page if (substr_count($MM_dupKeyRedirect,"?") >=1) $MM_qsChar = "&"; $MM_dupKeyRedirect = $MM_dupKeyRedirect . $MM_qsChar . "requsername=".$loginUsername; header ("Location: $MM_dupKeyRedirect"); exit; } }
PHP users should note that you're not copying the entire code block, but stopping after the two closing curly braces and before the code that starts: function GetSQLValueString. The closing PHP code bracket (?>) will need to be added a bit later in these steps.
2.
Open the edit_user page and paste the code after the complete delete command sequence.
Make sure you paste the code after the close of the if statement where the redirection occurs.
3.
Different code changes are required by the different server models.
To allow the server behavior to work with a record update, change the following code line, found near the top of the pasted code block, from:
MM_flag=MM_insert
to:
MM_flag=MM_update
To keep the current user from being listed, change the SQL statement from:
SELECT UserName FROM Users WHERE UserName='#FORM.UserName#
to:
SELECT UserName FROM Users WHERE UserName='#FORM.UserName#' AND UserID <> #FORM.UserID#
The first task for PHP users is to add a closing PHP closing bracket at the end of the pasted code block, following the two closing curly braces, so that the last lines change from:
} }
to:
} } ?>
Next, to allow the server behavior to work with a record update, change the following code line, found near the top of the pasted code block, from:
$MM_flag=MM_insert
to:
$MM_flag=MM_update
4.
Make the second code change to send the page to the proper location if a duplicate name is found:
Change:
MM_dupKeyRedirect="register_user.asp?repeat=true"
to:
MM_dupKeyRedirect="edit_user.asp?repeat=true"
Change:
var MM_dupKeyRedirect="register_user.asp?repeat=true";
to:
var MM_dupKeyRedirect="edit_user.asp?repeat=true";
Locate the line that starts with the following code:
MM_dupKeySQL
var MM_dupKeySQL
$LoginRS_query
6.
ASP users should append the following to the end of the code line:
& " AND UserID <> " & Request("MM_recordId")
+ " AND UserID <> " + Request("MM_recordId")
7.
PHP users should remove the final four characters on the line (double quote, single quote, double quote, and semicolon) and add the following:
Change:
"'";
to:
"' AND UserID != ".$_POST['UserID'];
With this change, the SQL statement ignores the current record. This action is necessary in case the administrator made changes to the record other than the user name. Without this modification, the SQL statement would identify the current record as the duplicate and the update would not take place.
8.
Additionally, PHP users need to locate the code line that ends as follows:
"requsername=".$loginUsername;
Remove the semicolon at the end and append the following:
."&25" valign="top">
9.
To receive an alert when a duplicate user name is encountered, we'll include the same code previously used on the Register User page. From your Snippets panel, open the Recipes > UserLogin > CustomCode folder for your server model and insert the Duplicate User Name - Display Text snippet in the empty cell below the EDIT USER label.
In the UserLogin folder, locate the folder for your server model and open the 
ColdFusion and PHP developers should drag a Hidden Field object from the Forms category of the Insert bar and name it UserID.
.Command") DeleteCommand.ActiveConnection = MM_Recipes_VB_STRING DeleteCommand.CommandText = "DELETE FROM Users 