Building a Simple Back-End

In the real world of dynamic web page development, one cannot simply download a database and edit it. Doing so could cause corruption of data or inoperability of either the database or the web application. Therefore, it is best to leave the database where it is and use ColdFusion MX to do the editing. This is sometimes known as the back-end .

In the case of the Oakbridge site, we will build a simple back-end for the tour area. To do this, you will only need the four basic database functions: add, search, update, and delete. What makes this so interesting is that there is no need for any sort of complex SQL code. It can easily be done with server behaviors. Here's how:

1. If you have not created an Oakbridge directory within your ColdFusion MX directory or your web server, do so now. Your location will vary depending on your platform. If you are using J2EE, your cfusion directory is located within your webapps folder of your JSP server. If you are on a Macintosh, the folder is located in computer_name/Users/Libraryname/Sites. PC users would place the directory in C:\CFusionMX\ wwwroot .

2. Within the Oakbridge directory, create a subdirectory and name it Admin . This will be the final location of the back-end.

3. Launch Dreamweaver MX 2004. If you have not created a site, do so now. The testing server should point to your Oakbridge directory within our server. Select RDS from the Access pop-down of the Remote Info Category. If the testing server is your local machine, select Local/Network from the Access pop-down. The Testing Server Folder area should show the path to the Oakbridge directory (see Figure 8.7). If it doesn't, click the Browse button and navigate to the folder. When finished, click OK to close the Site Definitions, and click the Done button to close the Manage Sites dialog box.

   Figure 8.7. Identify the testing server and the directory before you build the back-end in Dreamweaver.

   

Avoiding a ColdFusion MX "Gotcha"

If there is one aspect of defining a dynamic site through Dreamweaver that drives developers up a wall, across the ceiling, and down the other wall, it is the placement of the root directory for the dynamic site on the PC.

Though it is standard operating procedure to use the wwwroot folder in the InetPub folder of the PC for the site, this isn't the case with ColdFusion MX. The site's root folder must be placed in the wwwroot folder of the CFusion MX folder on the PC's hard drive. It doesn't go in the inetpub folder. Do that, and you will get an error from Dreamweaver telling you the URL isn't valid when you try to log in using an RDS password.

To avoid this "gotcha," always place the root folder for a site that utilizes ColdFusion MX in the ColdFusion MX wwwroot folder.

Another source of confusion is the URL prefix area of the Testing Server Category of the Define Site window. If you get this URL wrong, you are going to get the error message. The easiest way of addressing this issue is to click the Basic tab of the Site Definition dialog box that opens the Site Definition wizard.

The wizard walks you through the process in a non-technical manner. Here are the answers to the wizard screens used in this exercise:

Screen 1 Site name: Oakbridge.

Screen 2 Yes, I want to use a server technology. Which server: ColdFusion.

Screen 3 Edit and test locally (my testing server is on this computer). Where do you want to store your files: C:\CFusionMX\wwwroot\Oakbridge\.

Screen 4 What URL would you like to use to browse to the root of your site: This should be completed for you. The value for us was http://localhost:8500/oakbridge/ . Click the test button to see if it is valid.

Screen 5 No, I don't want to use a remote server.

Screen 6 A summary of your answers.

Creating Pages to Display Your Data

With the site defined, you can start to build the page. This will require you to create a ColdFusion MX template. To create the template, follow these steps:

1. Open a new Dreamweaver page. When the New Page dialog box opens, select Dynamic Page from the Categories listing. From the list in the Dynamic Page category, select ColdFusion (not ColdFusion Component). Click the Create button to close the dialog box.

2. Save the file as listfacilities in your Admin folder. You don't have to add the .cfm extension to the file name. Dreamweaver will do this for you.

3. If you have not yet entered your RDS login, click on the Server Behavior tab, click on the RDS login, and enter your password when the RDS password dialog box opens. If you created your data source earlier, your Oakbridge database will appear in the Database tab, along with any other database that ColdFusion MX may be connected to.

4. Click on Server Behavior tab in the Application panel. Click + to add a new server behavior from the pop-down menu. Select Recordset (Query) from the list of choices in the pop-down menu. When the New Recordset dialog box opens, enter the following values:

   • Name: listfacilities

   • Data Source: oakbridge

   • Table: facilities

   • Columns : All

   • Filters: none

   • Sort: none

   If your Recordset dialog box resembles Figure 8.8, click OK.

   Figure 8.8. The Recordset is defined in Dreamweaver using the table from the MySQL database created earlier.

   

5. Create a new table that is 2 columns wide by 2 rows high as shown in Figure 8.9. Don't worry about formatting. The top row is just for the heading. In this case, we can put something like "List of Facilities" as the heading and may even want to merge the columns in the top row into one column.

   Figure 8.9. A table is created on the page by selecting Insert Table from the Layout tools and using the settings shown in the dialog box.

   

6. Select the Bindings tab in the Application menu and click Recordset (listfacilities) to select it. Click the + to open it and reveal the fields that will be bound to the web page.

7. Click on the facility field in the Bindings tab and drag it to the lower-left table cell on the page. Drag the description to the lower-right table cell .

8. Select the bottom row of cells . Click the Server Behavior tab and select Repeat Region from the pop-down menu. This will open the Repeat Region dialog box shown in Figure 8.10. Select listfacilities from the Recordset pop-downthe + signand select All Records. Click OK.

   Figure 8.10. The repeat regions are added to the table.

   

That's it for this section. Keep the page open, as we will use it in the next section.

Creating a Simple Data Entry Page

The next step in the process is to create a page for data entry. This page will gather the information and send it to the database. Here's how:

1. Create a new dynamic page and save it as addfacility.cfm in the Admin folder.

2. Select Insert, Form to add a form to the page. Name the form insertForm in the Property Inspector.

3. Click once inside the form. Add a single line text field named facility , a multiline text field named description , a single line text field named imageURL , and a Submit button. Each item is available from the Insert, Form menu option. Click once in front of the Submit button and press Return/Enter. When finished, the form should resemble Figure 8.11.

   Figure 8.11. The table that will be used to pass values to the database is created.

   

4. Select the Server Behaviors tab from the Application panel. Click the + and select Insert Record from the pop-down list of server behaviors. When the Insert Record dialog box opens, add the following values:

   • Form: select insertForm

   • Data Source: oakbridge

   • Insert Into Table: Facilities

   • After Inserting, Go To: listfacilities.cfm

   Click OK if your Insert Record dialog box resembles Figure 8.12.

   Figure 8.12. The data from the text fields is "hooked" into the database using the Insert Record Server behavior.

   

5. Click the listFacilities.cfm tab at the top of the Dreamweaver document window. Click once at the bottom of the page and enter Add a facility? Select the text you just input, and in the Property Inspector, add a link to the addfacility.cfm page. You should test the page in a browser by selecting File, Preview in Browser or by pressing the F12 key.

Creating the Capability to Modify and Delete Your Entered Data

Being firm believers in letting the software do the work and not reinventing the wheel, adding the capability to modify or delete records from the database is easier than it sounds. In fact, it is similar to the last page created. The only difference is the addition of a hidden field to the form.

Creating the Edit Page

To get yourself started, ensure the addfacility.cfm page is open. To copy it, simply select File, Save As and rename the page editfacility.cfm .

Finally, open the Application panel and remove the Insert Record behavior from the addfacility page. This is accomplished by selecting the Insert Record server behavior on the Application panel and clicking the sign. Having done that, follow these steps to create a page that lets you edit data:

1. In the Application panel, click the + to add a new server behavior from the pop-down menu. When the menu opens, select Recordset (Query). When the Recordset dialog box opens, enter these values:

   • Name: listfacilities

   • Data Source: oakbridge

   • Table: facilities

   • Columns: All

   • Filters: facilityID = URL Parameter facilityID

   • Sort: none

2. Having identified the Recordset being used in the table, you are going to get a bit more specific and tell Dreamweaver exactly what dynamic data goes where. This is done through the Property Inspector. Select the facility text box and click the lightning bolt beside the Init Value input box on the Property Inspector. This will open the Dynamic Data dialog box shown in Figure 8.13.

   Figure 8.13. The Dynamic Data dialog box identifies which fields in a form will utilize dynamic content.

   

3. Open the recordset by clicking the + sign, select facility, and click OK. The dialog box closes and the field turns bluish.

4. Select the Description field, click the lightning bolt, and select description. Click OK to close the Dynamic Data dialog box.

5. Select the imageURL field, click the lightning bolt, and select imageURL. Click OK to close the Dynamic Data dialog box.

6. Click once inside the form. Select Insert, Form, Hidden field. When the field anchor appears in the form, select it and name the hidden field facilityID in the Property Inspector.

7. Select the hidden field, click the lightning bolt, and select facilityID. Click OK to close the Dynamic Data dialog box.

8. Having changed the form with the addition of a hidden field, the recordset will have to be updated. Click the + in the Server Behaviors panel to open the Server behaviors. Select Update Record to open the Update Record dialog box.

9. Enter these values, as shown in Figure 8.14, into the Update Record dialog box:

   • Data Source: oakbridge

   • Update Table: facilities

   • Value: FORM.facilityID

   • Submit As: Numeric (check Primary Key checkbox)

   • After Updating, Go To: listfacilities.cfm

   Figure 8.14. The Update Record dialog box allows users to modify a database by changing the information on the web page and clicking the submit button.

   

   Click OK.

10. When the information hits the database, it is going to have to be changed elsewhere. Open the listfacilities.cfm page by either selecting File, Open, or if it is still open, by clicking the document's tab at the top of the Document window.

    Tip

    If you are like us and hate mousing through menus to open documents, the Document Tabs at the top of the Document window are a godsend. Use them.

11. When the document opens, select the { listfacilities.facility} binding in the bottom left cell of the table, or click once on the Dynamic Text { listfacilities.facility} server behavior in the Application panel.

12. The record will be linked to the editfacility.cfm page. To do this, click the folder beside the link area of the Property Inspector. When the Select File dialog box opens, click the Parameters button at the bottom of the dialog box to open the Parameters dialog box (see Figure 8.15).

    Figure 8.15. You can use the Link area of the Property Inspector not only to link documents, but also to link dynamic data to documents.

    

13. Enter facilityID in the Name text box. Next, click the lightning bolt to open the Dynamic Data dialog box and select facilityID from the list. Click OK to return to the Parameters dialog box. Click OK to return to the Select File dialog box. Select the editfacility.cfm document and click OK.

Creating the Delete Page

This is quite similar to the edit page just created. In fact, you will be using a copy of the same form with the addition of a hidden field.

To get started, ensure the editfacility.cfm page is open. To copy it, simply select File, Save As and rename the page deletefacility.cfm . Finally, open the Application panel and remove the Update Record behavior from the deletefacility page by selecting the Update Record server behavior on the Application panel and clicking the sign. Having done that, follow these steps to create a page that lets you delete data:

1. From the server behaviors, select the Delete Record server behavior. When the Delete Record dialog box opens (see Figure 8.16), enter the following values:

   • Data Source: oakbridge. (Select this from the pop-down list.)

   • Table: facilities.

   • Primary Key Column: facilityID.

   • Numeric: Selected.

   • Primary Key Value: Form Variable (from the pop-down list) - facilityID.

   • After Deleting, Go To: listfacilities.cfm.

   Figure 8.16. Data can be deleted from records by using the Delete Record server behavior.

   

   Click OK to close the dialog box and return to your page.

2. Open the listfacilities.cfm document and select the { listfacilities.description} binding.

3. Click the Split Cell button on the Property Inspector to add another cell. When the Split Cell dialog box opens, simply click OK because the default values are acceptable for what we want to do. This will put an empty cell under the { listfacilities.description} cell.

4. Click once inside the empty cell and enter the word Delete . You will use this word to link to the deletefacility.cfm page.

5. Highlight the text you just entered and click the Link folder in the Property Inspector to open the Select File dialog box.

6. Click the Parameters button, and when the parameters dialog box opens, enter these values:

   • Name: facilityID.

   • Value: Click on lightning bolt to open the parameters list.

   Select facilityID and click OK. Link to the listFacility.cfm page. Click OK.