You can update database records in the application you used to create the database, but if you want yourself or other users to be able to easily update the records, you should create an update record page from which to update them.
Updating records is more complicated than inserting new records because you have to provide a way for the user to select the record to update. There are two methods:
- Create a search page so that a user may search for a particular record based on the criteria you specify on the search page.
- Create a master page so that a user may select a record from a list of records.
Since the travelclub members table includes only a small number of records, we'll create a master page with a list of records rather than creating a search page.
If you have a large number of records in the database table, you can add a results page or a detail page to the search page or the master page, but it's not necessary if the database table includes only a small number of records.
In the first task in this section, you'll create the master page and define a recordset. In the second task, you'll insert a dynamic table on the master page to list all the records in the recordset by ID and name.
In the final task, you'll add a link and URL parameter to connect the master page to the update page that you'll create in the next section. The URL parameter uses a field in the database table that uniquely identifies a record, such as a primary key field, to link to a specific record. That record displays on the update page when the link is clicked. URL parameters add data to the end of a URL and send the data to the server using the HTTP GET method. When the server receives a page request that includes URL parameters, it passes the parameters to the page before it's sent to the browser. For more information on URL parameters, see "Using URL and Form Parameters," in Chapter 8.
To create a master page for selecting a record:
Open a new PHP page in Dreamweaver by choosing File > New > Dynamic page > PHP. Save the page as select.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.13).
Figure 12.13. To open the Recordset dialog, on the Bindings panel click the plus button and choose Recordset (Query).
The Recordset dialog appears.
In the Name field, enter a name for the recordset (Figure 12.14). Choose a name that will help you to identify the recordset later.
Figure 12.14. The updateMembers recordset includes the id, first_name, and last_name fields from the members table in the travelclub database.
Because this recordset is being used to create a master page so that we can update a record in the members table of the travelclub database later, we named it updateMembers.
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, choose the Selected radio button, and then select the id, first_name, and last_name fields.
This adds the id, first_name, and last_name fields from the members table to the recordset.
In the Sort section, choose the last_name field from the first drop-down list and choose Ascending from the second dropdown list.
This sorts the recordset in ascending order by the value in the last_name field.
Click OK to close the dialog and save the recordset. Save the page.
You've created a master page with a recordset that includes the id, first_name, and last_name fields from the members table. In the next task, you'll add a dynamic table to display the first and last names.
To add a dynamic table:
Open the select.php page from the preceding task.
You'll add a table to display the list of records in the members table by first and last name.
From the main Dreamweaver menu, choose Insert > Application Objects > Dynamic Data > Dynamic Table (Figure 12.15).
Figure 12.15. Choose Insert > Application Objects > Dynamic Data > Dynamic Table to insert a table and add a Repeat Region server behavior and dynamic text fields.
The Dynamic Table dialog appears.
From the Recordset drop-down list, choose updateMembers (Figure 12.16). Click OK to close the dialog and insert the table with the default settings (show 10 records at a time, use a 1-pixel border width, and use 2 pixels of cell padding). You've inserted a dynamic table that includes the three fields from the updateMembers recordset.
Figure 12.16. Choose a recordset and set table properties in the Dynamic Table dialog.
Use the Design view window to view the table. Field names are displayed as column headers, and dynamic-text placeholders are displayed in the id, first_name, and last_name fields (Figure 12.17).
Figure 12.17. The dynamic table includes recordset field names as column headers and dynamic-text placeholders for the field values.
Save the page.
You've added a dynamic table to display the list of records in the updateMembers recordset. In the next task, you'll add links and URL parameters to connect the master page to the update page.
You can edit the names of the column headings to make the table easier for page visitors to use, and change any design features of the table, such as background color, size, or border type.
To add links and URL parameters:
Open the select.php page from the preceding task.
You'll add links and URL parameters to connect the list of records in the dynamic table on the master page to the update page.
In Design view, select the dynamic-text placeholder for the id field.
In the Property inspector, enter a name for the update page in the Link field (Figure 12.18). We named our page update.php.
Figure 12.18. Enter a name for the update page in the Link field of the Property inspector.
Now that you've added a link to the update page, you can add a URL parameter to that link.
Add a URL parameter to the end of the link name, in either the Link field in the Property inspector or Code view (Figure 12.19).
Figure 12.19. The URL parameter is added to the end of the link name.
The format of the URL parameter varies depending on the application server you're using, so choose one of the following:
?record=<?php echo $row_updateMembers['id]; ?>
Give the URL parameter a name (record) and add the recordset name (updateMembers) and the field name for the unique identifier of the record (id).
Save the page.
In the next section, you'll create the update page.