The last step in creating pages for the purposes of updating records is to build the update page. Using an HTML form on an update page allows users to update either all the fields in the database table or only some fields. Usually you set up the form so that only some fields can be updated by the user. For instance, you generally would not want the user to be able to change a primary key field.
In the first task in this section, you'll create the update page and define a recordset with a filter to retrieve the record that's associated with the link on the master page. Then you'll insert a dynamic form on the update page that includes any field(s) you want to make available for update. In the last task, you'll add the Update Record server behavior to complete the update page.
In the next section of this chapter, you'll learn how to more easily and quickly update records using an application object.
To create an update page and a filtered recordset:
Open a new PHP page in Dreamweaver by choosing File > New > Dynamic page > PHP. Save the page as update.php.
In the Application panel group, click the Bindings tab to access the Bindings panel.
Click the plus button, and from the contextual menu choose Recordset (Query) (Figure 12.20).
Figure 12.20. Choose Recordset (Query) from the Bindings menu to create a new recordset.
The Recordset dialog appears.
In the Name field, enter a name for the recordset (Figure 12.21). Because this recordset is being created for an update page, we named our recordset update.
Figure 12.21. The update Recordset includes the id and email fields from the members table in the travelclub database.
From the Connection drop-down list, select the name of your connection to the travelclub database. Our connection is named connTravelClub.
From the Table drop-down list, select members.
In the Columns section, select the All radio button.
This adds all the fields (id, first_name, last_name, and email) in the members table to the recordset.
In the Filter section, choose id from the drop-down list on the left (Figure 12.22). Then, choose the equals sign (=) from the drop-down list on the right.
Figure 12.22. In the Filter section, use the URL Parameter from the master page to specify a unique record in the update page.
In this step, you're specifying which field should be used (id) and how the data in the field should be evaluated. In this case, we want the id to be equal to a certain value, so we use the equals sign (=).
From the drop-down list in the lower left of the Filter section, choose URL Parameter. In the field to the right, enter record.
Now the filter is complete. It specifies that you want to include records where the value for id is equal to the value of the URL parameter named record, which is included in the link on the master page. Because this is a unique value for each record, there will be only one record in the update page recordset that's associated with each link that displays on the master page.
Click the Test button to see the contents of the recordset. On the master page, the URL parameter named record is included in the link to the update page, so you will need to enter a value for record in the Please Provide a Test Value dialog to test the recordset (Figure 12.23). Because the value for record is equal to the value for id, enter any value that corresponds to a value in the id field of the members table, such as 35976.
Figure 12.23. Enter a record value in the Please Provide a Test Value dialog to test the recordset. The record value is the same as the id value.
Click OK to close the dialog and display the Test SQL Statement dialog (Figure 12.24).
Figure 12.24. The unique record with the id value you entered is displayed in the Test SQL Statement dialog.
Click OK to close the Test SQL Statement dialog.
Click OK in the Recordset dialog to save the recordset and close the dialog.
Save the page.
To add a dynamic update form:
Open the update.php page from the preceding task.
We'll insert a form and add a text field so that users can update their e-mail addresses.
From the Insert menu, select Form > Form to add a form to your page (Figure 12.25).
Figure 12.25. Use the Insert menu to insert a form on your page.
In Design view, select the form. In the Property inspector, type a name for the form in the Form Name box (Figure 12.26). Because the purpose of this form is to update e-mail information, we named the form email.
Figure 12.26. Type a name for the form in the Form Name box in the Property inspector.
You don't need to specify an action or a method for the formthese will be set automatically when you add the Update Record server behavior.
From the Insert menu, select Form > Text Field to add a text field to the form.
In Design view, select the text field.
In the Property inspector, type a name for the field in the TextField name box (Figure 12.27). Because we're using the text field to update a travel club member's e-mail address, we named the text field emailUpdate.
Figure 12.27. Enter a name for the text field and a value for the width and maximum characters of the text field in the Property inspector.
In the Char Width and Max Chars fields, type 30 to limit the size of the field to 30 characters.
The email field in the members table has a size of 30, so we used this same size for the text field.
In the Type section, select the Single Line radio button.
The next step is to make this field a dynamic text field so that the current value for email displays in the text field when the update page is opened.
Click the lightning bolt button next to the Init Val field.
The Init Val field contains the code for binding a recordset field to the text field (this code will be added in Step 11).
The Dynamic Data dialog appears.
Click the plus (+) button to expand Recordset (update) and display the field names (Figure 12.28). Select the email field to bind this field in the recordset to the text field in the form.
Figure 12.28. Select a recordset field in the Dynamic Data dialog to bind dynamic data to the text field.
Dreamweaver automatically creates the PHP code for the binding, which then appears in the Code field of the Dynamic Data dialog.
In the Dynamic Data dialog, click OK to close the dialog and insert the binding code in your page.
A placeholder with the table and field name appears in the text field in Design view, and the dynamic text field also appears on the Server Behaviors panel. When you preview the select page in a browser and click a link to open an update page, text from the database will be automatically inserted into the email text field on the update page (Figure 12.29).
Figure 12.29. The value for the email field in the database is automatically inserted in the dynamic text field when the page is viewed in a browser.
From the Insert menu, select Form > Hidden Field to add a hidden field to the form.
The Tag Editor appears.
In the Name field, type id (Figure 12.30). Leave the Value field blank. Click OK to close the dialog and insert the hidden field.
Figure 12.30. Add a hidden field in the Tag Editor dialog.
The id field is the primary key field in the members table. You need to include it in the form to uniquely identify the record in the Update Record server behavior that you'll add in the next task. To prevent the user from changing the id value, you included it in the form as a hidden field.
From the Insert menu, select Form > Button to insert a Submit button.
Save the page.
The form to update the e-mail address is now complete. In the next exercise, you'll add the Update Record server behavior.
You can update more than one field on an update page. Just add a form object for each field you want to update.
In addition to form objects, you can add dynamic text fields to the update page. For example, you can add a welcome to the update page that displays the user's name and id number. To create a dynamic-text placeholder, open the Bindings panel, expand the recordset, and drag a field name onto the page in Design view.
To add an Update Record server behavior:
Open the update.php page from the preceding task.
In the Application panel group, click the Server Behaviors tab to access the Server Behaviors panel.
Click the plus button, and from the contextual menu choose Update Record (Figure 12.31).
Figure 12.31. Choose Update Record from the Server Behaviors menu to add an Update Record server behavior.
The Update Record dialog appears (Figure 12.32).
Figure 12.32. Choose a form, a connection, and a database table in the Update Record dialog.
From the Submit Values From dropdown list, select the email form. From the Connection drop-down list, select the connection to the travelclub database. Our connection is named connTravelClub. From the Update Table drop-down list, select members.
You've specified that you want to use the email form to update values in the members table of the travelclub database.
In the Columns section, select the first item on the list (id). From the Value drop-down list, select FORM.id. From the Submit As drop-down list, select Integer. Check the Primary Key box.
Using the id field enables the server to identify the unique record to be updated by the primary key for this record.
In the Columns section, select the last item on the list (email). From the Value drop-down list, select FORM.emailUpdate. From the Submit As drop-down list, select Text. Leave the Primary Key box unchecked, since this is not a primary key field.
The email field will be updated when the user clicks the Submit Change button on the update page.
In the After Updating, Go To field, type a name for the page to display after the user has submitted the form. We used the name confirmation_email.php (Figure 12.33). Click OK to insert the behavior and close the dialog.
Figure 12.33. Enter a name for the confirmation page in the Update Record dialog.
Save the page.
Create a new PHP page, and name it con firmation_email.php. Add a message to the page such as "Thank you for updating your email address!" Save the page.
The confirmation page opens when the user submits the form. Now it's time to test your select and update pages in a browser.
Open the select.php page in Dreamweaver, and then choose File > Preview in Browser.
Click a link for a record to update (Figure 12.34), and then enter a new e-mail address on the update page (Figure 12.35).
Figure 12.34. Choose a record to update on the select page.
Figure 12.35. Update the e-mail address on the update page, and click the Submit Change button to submit the change to the server.
The confirmation page appears (Figure 12.36).
Figure 12.36. A confirmation page opens after the Submit Change button is clicked on the update page.
You've created a set of pages for updating a record: a select page in which a user can select a record to update, and an update page in which a user can enter a new e-mail address in the form text field. When the user clicks the Submit Change button on the update page, a confirmation page opens.
Usually, you restrict access to any pages that allow users to insert, update, or delete database records. For more information on restricting access by adding pages where users log in with a user name and password, see Chapter 13, "Restricting Access."