From FMWebschool Reference

Contents 1 Chapter 8. Editing Database Records 1.1 Simple Redirection 2 See Also

Chapter 8. Editing Database Records

Editing records is a slightly more involved process than creating new records, but the concept is generally the same. There is one slight difference that should be noted upfront. You have to know in advance, which record you are going to be editing. Therefore the flow of an edit record generally starts on a detail page.

You are viewing details about a single record and then adding a link to an edit page. The edit page will then pre-fill a form with the current values and the edit page will submit that form to the response page. The response page will edit the actual record in the database and bring back updated fields into your application.

Once that is done you can either display the updated fields or as in most cases redirect the user back to the detail page where they can click edit again and make any further adjustments that are needed.

1. Let’s jump right into this by opening the 'photo_detail.php' page that we created in Chapter 6. On the 'photo_detail.php' page there are a number of photos in a table. To start the edit, we will need to add a link to this page that will be pointing to the 'edit_photo.php' page.
2. Underneath the main form, type the word 'Edit'. Then, highlight the word. To make the word 'Edit' a Dynamic Data Link, navigate to Server Behaviors > Links > Dynamic Data Links.

Image 8 – 1: Choose Server Behaviors > Links > Dynamic Data Link – and apply this to the word ‘Edit’.

Once selected, the Dynamic Data Link dialogue window will open.

Image 8 – 2: The Dynamic Data Link dialogue window waiting to be filled

1. Just like before with the detail page, where we had a dynamic data link on the search results, this page will have a target page of ‘edit_photo.php'.
2. It will pass along the same photoID variable.
3. The variable value will come from the 'photoID' field of the current photo recordset.
   
   Image 8 – 3: Dynamic Data Link passing the photoID variable
4. Press 'OK' the text 'Edit' is now an Edit Detail Link. Save 'photo_detail.php'.

Next, we will move on to building the actual edit form. Create a new page named 'edit_photo.php'. The edit_photo' page will combine a number of different elements discussed in previous chapters. This page will use a recordset to find a single record that is going to be edited. Then we will create a filled form that will display data from that recordset that is ready to be edited.

1. First start by creating the recordset that will find the photo. Server Behaviors > Click the 'plus' sign > select Find (Recordset). The Recordset dialogue window will open.
2. Name the Recordset 'photo'.
3. The connection is the 'Photos' database.
4. The layout will be 'photos'
5. The Add Criterion will be 'photoID' and will be an exact search for the photo ID.
6. Click the 'Add' button, the Type should be 'REQUEST', the Name should be 'photoID' and the Operation should be '= ='. The last step is to disable wildcards and click 'OK'.

Image 8 – 4: Completed recordset named photo

Now that the photo recordset is in place, edit the 'edit_photo.php' page to show some details. To do this, we will need to perform the following actions.

1. First type the words 'Editing a Photo –'.
2. Next navigate to the Bindings tab under Applications. Select the 'photoName' binding and drag it onto the work area beside the text we just added to the page.
3. Type two brackets '( )' onto the page and then drag and drop the 'photoID' bindings between the two brackets as shown in the screenshot below.
4. Next highlight the text and bindings and apply a 'Heading1' format to them. To do this you select 'Format' under the 'Properties' panel.

Image 8 – 5: The text ‘Editing a Photo –‘with both bindings on the page. The last binding was placed between open brackets ( ).

The next step requires creating a filled form. There are actually many ways to create a filled form. You could build a form by scratch using Dreamweaver, and then adding text fields, naming them correctly to correspond to your database and then making sure everything else matches. We suggest using the FMStudio filled form builder.

1. Before adding the filled form to the page, make sure that your cursor is placed and blinking in the Dreamweaver work area. Once we know that the form will be created inside the work area, select Server Behaviors > Wizards > Filled Form Builder. The Filled Form Builder dialogue box will appear.

Image 8 – 6: To create a filled form, select Server Behaviors > Wizards > Filled Form Builder

The filled form builder is just like the regular form builder except you select a recordset from which the values for the inputs will be prefilled.

1. The name will be 'edit_photo' and check the Generate Table check box.
2. The Recordset will be 'photo'
3. The Action will be 'edit_photo_response.php'
4. The Method will be 'POST'
5. The Submit button text will be 'Save Changes'
6. And the fields that will be editable are: photoName, photoDescription and photoPrice. Once we’ve finished adding these fields, click 'OK'.

Image 8 – 7: Filled Form Builder with the fields – photoName, photoDescription and photoPrice added.

The form will be added to the page as shown in the screenshot below.

Image 8 – 8: Form added to the page – note that the category names need to be changed to be more presentable.

1. The next step is to clean up the text descriptions. This is a simple task consisting of highlighting and changing the text to describe the contents of the field. Change the names too: Name, Description and Price. Save the page.

Image 8 – 9: The field names have been changed

Now that the 'edit_photo' filled form is complete it is time to look at the last piece of the puzzle 'edit_photo_response.php'. Create this new page and save it in your local folder.

Before continuing further, with the edit query, let’s review what is involved in a FileMaker edit. A FileMaker edit, which is done by an edit query, requires you to have an internal recordid for the specific FileMaker record that is being edited. This is the same internal value that you would have if you used the FileMaker calculation 'Get RecordID'. However you can also retrieve this recordID from the recordset binding. When you use the filled form builder it actually sets a hidden variable that is sent along with the form for the recordID of the record that is being edited. This value is named '-recid'. We will create the edit query using that variable that was passed in from the form.

1. Under Server Behaviors click the 'plus sign' and select FileMaker Queries > Edit (Query) the 'Edit Query' dialogue window will open.
2. The name will be 'edit_photo'
3. The connection will be the 'Photos' database
4. The layout will be 'photos'
5. The RecordID field will open a window named the 'Variable Selector'. When we click select, the default that is presented is the '-recid' field. This is correct and so we can click 'OK' (Read the note below if you did not use the filled form builder)

Note: This name is appropriate if you use the filled form builder. If you were to have built your own form, then the name of your hidden id variable should go here. Do not use any operations on the recordID. It is already an exact match and if you use the '=' or '= =' comparison operators the whole edit will fail with the record not found error.

1. Just like we created previously in the new query, you need to select which fields will be updated in this edit. Let’s add the following fields to this list: 'photo_Name', 'photo_Description', and 'photo_Price'. To add these fields, select them from the drop down menu and click the 'Add' button for each one.

Image 8 – 10: Add the following fields - 'photo_Name', 'photo_Description', and 'photo_Price'.

1. Once all of the fields have been added, press 'OK'.

Now that the edit query is in place, there are a number of things that can be done with it. This page could display a confirmation number, or a number of updated fields that would have been affected by this edit. I will demonstrate an example where we will display a number of fields that have been edited.

1. On the 'edit_photo_response.php' page I will type, "Thank You, Your Changes have been saved" and then center the text on the page. I will also apply a style of heading 1 to the text.
2. I can actually use the recordID to show how many times this record has been modified. To do this, add the text, "This record has been modified" and then drag and drop the 'Modification' binding beside the text. This binding is found by clicking the '+ sign' beside 'Edit (Query) (edit_photo) Metadata properties

Image 8 – 11: Select the Modification binding and drop it beside the text ‘This record has been modified’.

1. Add the word 'times' after the bindings. The whole sentence should look like this:

'This record has been modified {edit_photo > ModificationId} times.' Once complete I will give this line of text a style of Heading 2.

1. Save this page.

Now that this page is done, it will reflect the new modification ID of the record once it has been edited. Let’s test this process out!

To make sure the pages we have created are working properly, complete the following steps.

1. Open the search.php page in the browser. When the search page opens in the browser, type the word 'girl' as the search criteria. The example database included with this book has several records with the word 'girl'. My search returns 9 results for the word ‘girl’.
2. Select the record 'Pretty girl with butterfly barrette', which is photo PHO0006.

Image 8 – 12: Record returned for ‘Smiling Girl’ pressing the ‘Edit’ link will allow you to edit this record.

1. Below the table, there is an edit link. Select the Edit link to navigate to the ‘edit_photo.php'. Here, you can edit any of the fields, Name, Description, or Price. Let’s change the price on this page to $16.99. Next, once this is complete, press the 'Save Changes' button.

Image 8 – 13: Once the changes have been made on the ‘edit_photo.php' page, press the ‘Save Changes’ button.

1. Click the 'Save Changes' button; this will take you to the 'edit_photo_response.php’ page. This page confirms that your changes have been saved and that the record has been modified 6 times.

Image 8 – 14: The 'edit_photo_response.php’ informs you that your changes have been saved and that the record has been modified 6 times.

Before we leave this chapter, there is a much more common scenario that we should explore, avoiding the confirmation page altogether. Avoiding this page will take the user back directly to the detail page once the record has been edited. One reason this is beneficial is, if your visitor is sent back to the detail page, they can see if they have made any mistakes, and quickly make the changes.

We can accomplish this with a simple redirection back to 'photo_detail.php' where the visitor can edit the record again.

Simple Redirection

1. Open the 'edit_photo_response.php' page and under Server Behaviors choose Page Actions > Redirect

Image 8 – 15: Select Server Behaviors > Page Actions > Redirect

The Page Actions Redirect Dialogue window will open.

1. The Page Action Redirect acts just like a Dynamic Link, it asks you for a redirect URL –in this case, you are redirecting your visitor back to the 'photo_detail.php' page.
2. Next we will set the Option Variable Name; and we will send the 'photoID' since 'photo_detail' is already expecting the 'photoID' as part of the request. Select the 'lightning' icon and select 'photoID'.

Image 8 – 16: Select ‘photoID’ from the list

Image 8 – 17: Once the Redirect values are filled, press OK

1. Once the redirect is in place, we can reload the detail page in the browser and try the editing process again. Instead of being taken to the "Thank you, your changes have been saved" message, we are now redirected to the detail page.

In this chapter we learned how to edit records and take the visitor to our site to a page that confirmed that the records had been saved. We also learned how to redirect the visitor back to the same record where they could re-edit the record if needed. In Chapter 9, we will discuss how to delete records from the database.

See Also

• FMStudio Complete Guide Table of Contents