The next step in developing a database-driven site is to build a connection to the database. All database connections are managed via the Databases panel, shown in Figure 29.3.

Figure 29.3. The Databases panel is where all connections are stored.

If the proper steps have not been taken to facilitate the creation of a database connection, Dreamweaver lets you know the steps that you need to take before you can proceed. If you have already created the ODBC connection to the database in the ColdFusion Administrator in Chapter 21, “Putting It All Together: Configuring ColdFusion for Database Connectivity,” your Databases tab should look like Figure 29.4 and you are ready to build a connection.

Figure 29.4. All the requisite steps have been completed and Dreamweaver can create a connection to the database.

Because the Retro’s Cycles site is using ColdFusion, Dreamweaver automatically generates a list of data source names (DSNs) that were built with the ColdFusion Administrator. As you can see in Figure 29.4, the dsnRetrosCycles DSN is already in the list, so we would be ready to move on to the next step.

If, however, you are using a server platform such as ASP or PHP, you need to create either an ODBC DSN on your machine or a custom connection string by clicking on the plus sign in the Databases panel and choosing the appropriate connection method.

Although search forms aren’t necessarily required for displaying dynamic data, they are frequently used to pass criteria to a Results page, so it’s worth taking some time to look over and understand them before you jump into the dynamic pages. Figure 29.5 shows a basic form with a single text field and a Submit button.

Figure 29.5. A simple search form.

By selecting the form and taking a look at the Property inspector (see Figure 29.6), you can see that the form is named fmSearch and, when submitted, passes its content to the search_results.cfm page, using the GET method.

Figure 29.6. The form’s properties.

In addition, clicking on the text field and taking a look at the Property inspector tells you that the text field is named tfSearch (see Figure 29.7).

Figure 29.7. The text field’s properties.

This means that when the user types Honda in the text field and clicks the Search button, the contents of the form are passed to the search_results.cfm page via the querystring (because you are using the GET method). To accomplish this, the search form constructs a URL that contains the form data and looks something like this:

http://www.retroscycles.com/searchresults.cfm?tfSearch=Honda

This URL now presents you with the opportunity to extract data from the database that matches the search criteria and display it on your pages.

After you have your database connection created and tested, the next step is to learn how to retrieve content from the database. Each relational database uses a derivative of the Structured Query Language (known as SQL and pronounced “see-quel” or “ess-cue-el,” depending on who you are talking to).

SQL can be used to specify what data should be retrieved from a database and even perform actions such as inserting, updating, or deleting data.

In the case of retrieving data, SQL enables you to specify limitations on the types of data that you want retrieved. For instance, a query that you might perform might ask the application to retrieve all the database fields from the tbInventory table where the Make field is equal to the value that has been submitted via the search form. After the query is executed, all the records that match the criteria are returned in the form of a recordset. Think of a recordset as a subset of the entire database that contains only the specific information that was requested.

Luckily, Dreamweaver makes creating recordsets extremely easy. To build a new recordset, open the Server Behaviors tab on the Application panel by choosing Window, Server Behaviors. Click the plus sign and choose Recordset from the menu. As shown in Figure 29.8, the Recordset dialog box provides an easy-to-use interface for gathering data from the database.

Figure 29.8. The Recordset dialog box enables you to extract data from your database.

The first step in creating the recordset is to give it a name. As with other page objects that we have discussed, it’s a good idea to give your recordsets names that mean something. So a recordset that retrieves all the search results might be named rsSearchResults.

The next step is to choose the data source from which you want to extract data. This drop-down contains all the database connections found in the Databases panel. Because a successful connection was created to the Retro’s Cycles database, you could choose the dsnRetrosCycles database. If the database were secured with a username and password, they would need to be entered so that the recordset could access the data.

Next, you need to specify from which table the data should come. Because you want to search the inventory, the tbInventory table would be selected.

Finally, you can apply any filters to ensure that only the data you want is retrieved. In this case you would choose to select all the columns in the table, and set the Filter field so that Make is equal to the URL parameter tfSearch (see Figure 29.9). This tells the recordset to extract the value of tfSearch from the URL (also called the “querystring”) and compare it to the data stored in the Make field.

Figure 29.9. The recordset has been created and is ready to test.

To see whether or not the recordset is retrieving only the data that you have asked for, click the Test button and Dreamweaver asks you to enter a test value. If you type Honda and click OK, you can see that Dreamweaver displays the first 25 records in the recordset that have the Make field set to Honda. As you can see in Figure 29.10, all the motorcycles in the results are Hondas.

Figure 29.10. The recordset contains the correct data.

If you are proficient in using SQL and want to expand on your SQL queries or use some of the advanced functions within SQL, you can click the Advanced button and customize the query to meet your needs by using the Advanced view (see Figure 29.11).

Figure 29.11. The Advanced view of the recordset enables you to build your own custom SQL queries.

To finish building the recordset, click OK and the recordset is added to the Server Behaviors panel and the Bindings panel.

After you have created a recordset that contains the appropriate results, the next step is to add data bindings to your page. Data bindings are placeholder blocks of code that, when rendered in the browser, are replaced by the actual data from the database. The available data bindings are located in the Bindings panel, shown in Figure 29.12.

Figure 29.12. The Bindings panel shows all the available recordsets and their corresponding data bindings.

To add a data binding to your page, expand the recordset from which you want to choose data by clicking the plus sign and then dragging the placeholder you want from the panel to the page. After the placeholder is in place, Dreamweaver indicates that it is dynamic by highlighting it (see Figure 29.13).

Figure 29.13. The data binding has been added to the page.

When using dynamic data, one of the most common mistakes developers make is to forget to account for the rare occurrence that a recordset does not contain any records. This might occur because of a typo in the SQL query, or because the type of data requested just doesn’t exist in the database.

For instance, suppose you were building an events calendar for your company. The calendar contained hyperlinked dates that, when clicked, displayed all events that were scheduled to occur on that date. Sounds great, right?

But what happens when a user clicks on a date when nothing is scheduled? You might automatically think that nothing would display, right? Unfortunately, that’s wrong. What happens, instead, is that the application server tries to display something that doesn’t exist and it gets confused. As a result, your browser throws a nasty error like the one shown in Figure 29.14.

Figure 29.14. This browser error occurred because the requested data didn’t exist in the database.

To avoid errors such as this one, the best thing to do is to define two regions in the page. The first region displays the contents of the recordset if it is not empty. The second region displays a block of text that lets the user know that there are no results.

The tools used to accomplish this are called server behaviors and are found on the Server Behaviors panel, shown in Figure 29.15.

Figure 29.15. The Server Behaviors panel.

To display a region only when the recordset contains data, highlight the data or table region that you want displayed, and click the plus sign on the Server Behaviors panel. From the menu, choose Show Region, If Recordset Is Not Empty. From the Show Region If Recordset Is Not Empty dialog box (see Figure 29.16), choose the correct recordset and click OK.

Figure 29.16. Select the recordset that governs the server behavior.

When the server behavior is applied, Dreamweaver places a border around it and an indicator tab to show which region is affected (see Figure 29.17).

Figure 29.17. The server behavior has been applied to the region.

Next, you need to create the area of the page that displays only if the recordset contains no data. To do this, add the appropriate text to the page that lets the user know that no data was found and then select the text or table row. Next, click the plus sign on the Server Behaviors panel and choose Show Region, If Recordset Is Empty from the menu. From the Show Region If Recordset Is Empty dialog box (see Figure 29.18), choose the correct recordset and click OK.

Figure 29.18. Again, select the recordset that governs the server behavior.

With these regions in place, you can be sure that your visitors see only your message, as opposed to the messy browser error message.

After you have added your data bindings to the page, you can see what they would look like when rendered in the browser by clicking the Live Data View button on the Document toolbar (see Figure 29.19).

Figure 29.19. The Live Data View enables you to see what your data would look like when rendered. Because no search term was passed to the recordset, it is empty and the appropriate data is visible.

The data displayed in the Live Data View is the first record found in the recordset and is meant to give you an idea as to whether or not your design is functioning as expected. To fully test the page, be sure to preview it in your browser.

After the recordset has been tested, it’s likely that you’ll want to display the results somewhere. The easiest way to do this is to create a Results page that displays the contents of the recordset in a table. The easiest way to display the data is to create a dynamic data table that extracts the information from the recordset and displays it on the page.

To create a dynamic data table, click Insert, Application Objects, Dynamic Data from the menu bar. In the Dynamic Table dialog box, shown in Figure 29.20, choose the appropriate recordset and determine how many records should be displayed on the page at a time. For this example, we show 10 records at a time. You can also specify table settings by entering a value for the border, cell padding, and cell spacing.

Figure 29.20. The Dynamic Table dialog box helps you extract data from your recordset to display on your page.

Clicking OK places a table in the page, complete with data bindings (see Figure 29.21). Note that Dreamweaver assumed that you wanted to display all the columns in your recordset, but you can remove any columns that you don’t want to display.

Figure 29.21. Dreamweaver adds the dynamic data table to your page.

Because the user probably doesn’t want to review every detail of every record that meets the search criteria, you can delete all the columns from the data table except InventoryID, Year, Make, Model, and Color. This should give users enough information to choose which motorcycle they might want to look into further (see Figure 29.22).

Figure 29.22. Columns have been removed from the table to streamline the user’s view.

After users determine which motorcycles they want to see more information about, they need to be able to click a link to open the Details page, which then displays the full informational record on that specific motorcycle. To create this link, select the InventoryID data binding and type details.cfm in the Link field of the Property inspector.

Next, you need to pass the InventoryID field through the URL so that the Details page will know which record to display. To set this up, click the folder icon next to the Link field in the Property inspector and click the Parameters button. In the Parameters dialog box, shown in Figure 29.23, type InventoryID as the name for the parameter.

Figure 29.23. The Parameters dialog box enables you to pass custom parameters through the querystring.

Next, click the lightning bolt icon next to the Value field and choose inventoryid from the list of data bindings in the rsInventory recordset (see Figure 29.24).

Figure 29.24. Choose the inventoryid to be passed as the value.

Click OK to close the Dynamic Data dialog box, click OK again to close the Parameters dialog box, and click OK a third time to close the Select File dialog box. After it is created, the inventoryid now becomes a hyperlink that, when clicked, passes the inventoryid of that specific record to the details.cfm page.

A Details page usually contains the information stored in the database that relates to a specific record. This page enables users to get detailed information about an item after they have determined from the Results page that it might meet their needs.

In the case of Retro’s Cycles, clicking the link on the Results page passes an inventoryid to the Details page, so you need to build a Details page that reads that value, extracts the appropriate record from the database, and displays it on the page. To do this, you need to create a new page and save it as details.cfm.

Next, a recordset needs to be built that looks for the URL parameter called inventoryid and finds the record whose inventoryid matches that value. The recordset would look like the one shown in Figure 29.25.

Figure 29.25. The Details page recordset.

After the details recordset is completed, you can construct the page however you want to display the full details of a specific motorcycle. Figure 29.26 shows a sample configuration of the data bindings.

Figure 29.26. A sample Details page.

Because the database for the Retro’s Cycles site already had data stored in it, recordsets and data bindings could be used to extract information from it. But what if the database is empty and you need to populate it with data? Or what if we wanted to add additional records beyond those that are already stored in the database? The answer is to create a page that inserts new records into the database. Dreamweaver makes the creation of an insert page easy through the use of the Record Insertion Form Wizard.

To activate the wizard, choose Insert, Application Objects, Insert Record, Record Insertion Form Wizard. The wizard, shown in Figure 29.27, enables you to specify the data source that connects to the database, any username/password information required, the table to which you want to add, and a page to which the user is redirected after the record is added.

Figure 29.27. The Record Insertion Form dialog box enables you to add a form to your page that inserts data into the database.

The really nice thing about this wizard is that it lets you set up your form and the form elements in one handy interface. For instance, suppose that you wanted the year field to be a menu instead of a text field. You could select the year field in the Form Fields panel and choose Menu in the Display As field (see Figure 29.28).

Figure 29.28. The year form field has been changed to a menu item.

To give users values from which to choose in the menu, click the Menu Properties button to add the various years and values (see Figure 29.29).

Figure 29.29. Custom values can be added to the menu.

When the OK button is clicked, Dreamweaver does all the work and adds the records insertion form to your page (see Figure 29.30). You can then customize the form and the table layout to match your site’s design.

Figure 29.30. The record insertion form is added to the page.

Updating information that is stored in the database is nearly as easy as inserting it. To update a record, you can create a page similar to the page that adds a record. The update page, however, requires a recordset that determines which records to update, and the existing data that matches that record is displayed in an editable form. Upon submission of the form, the content stored in the form replaces the content stored in the database record.

To accommodate the creation of an update page, Dreamweaver has added another handy wizard called the Record Update Form Wizard, which you can access by choosing Insert, Application Objects, Update Record, Record Update Form Wizard from the menu bar.

The wizard, shown in Figure 29.31, is very similar to the Record Insertion Form Wizard and enables you to customize the form to meet your needs. This wizard does, however, have one additional field named Unique Key Column, which indicates which field in the database serves as the primary key and ensures that the record is unique.

Figure 29.31. The Record Update Form Wizard helps you create a page that makes updates to existing records.

In the case of the tbInventory table, the autonumber inventoryid field serves this purpose, and choosing this field ensures that the form makes the update to the correct field.

After the wizard is completed, the table and editable fields are added to the page and are customizable to meet your page’s design.

The last of the common dynamic page types is the oh-so-dangerous Delete Record page. This page enables you to permanently remove a record from your database. Did I mention that the deletion of the record is permanent? If I didn’t mention that, let me state clearly that the deletion of the data done with a Delete Record form is permanent. If it sounds like I’m beating a dead horse, it’s because on numerous occasions I have heard the question “Can you get that back for me?” after a user deleted a record from the database. Although the Delete Record page can certainly be a valuable tool in the right hands, it can be just as dangerous in the wrong hands.

Unlike the Update Record page, this page type doesn’t rely on a recordset to identify which record should be deleted. Instead, the page traditionally accepts a variable via the URL or from a form that indicates the unique record ID that should be deleted. Then, when the page is loaded, the record with the corresponding record ID is deleted from the database.

To create a Delete Record page, simply create a new dynamic page and then apply the Delete Record Application behavior by choosing Insert, Application Objects, Delete Record from the menu bar. The Delete Record dialog box (see Figure 29.32) then asks you to specify the data source, authentication information, table, primary key column, and the source of the record ID that matches the primary key. In addition, you need to specify where the user should be redirected to after the record is successfully deleted.

Figure 29.32. The Delete Record dialog box helps you create pages that remove data from your database.

Before you venture out into the world with your new found dynamic tools, let me suggest that you take a moment to read the Best Practices section of this chapter. As you have already probably guessed, I am not a huge proponent of putting delete capabilities into the hands of end users, so I have detailed another option in the Best Practices section.

After you have read that section, go forth, apply your new dynamic data skills, and build powerful, portable, productive websites that knock the socks off your friends and co-workers.