Macromedia Dreamweaver MX 2004 Demystified

As soon as one or more connection scripts have been written, individual pages throughout the site can query the database. The most common kind of query is to create a recordset (a group of records that meet certain selection criteria).

The Basics of Recordsets

To create the recordset, open a dynamic document and follow these steps:

1. Open the Bindings panel, click the + button, and choose Recordset (Query); or go to the Insert bar, choose Application objects, and choose the Recordset object, as shown in Figure 22.1; or open the Server Behaviors panel, click the + button, and choose Recordset (Query).

   Figure 22.1. Creating a database recordset.

2. In the Recordset dialog box that opens, choose whichever connection, database, and specific database elements (usually table columns , which translate into record fields) you want to collect.

3. (Optional) To test the information you'll collect, click the Test button (see Figure 22.1).

4. When you're done, click OK to close the dialog box.

After you've created the recordset, the collected columns appear in the Bindings panel, as shown in Figure 22.2. Expand it to see all database fields that have been collected. Also, because Dreamweaver has to write a script to create the recordset, technically speaking, the collection action is a server-side behavior, and the recordset appears in the Server Behaviors panel (see Figure 22.3).

To edit a recordset you've already created so that it collects different data or organizes it differently, open the Server Behaviors panel and double-click the Recordset behavior there. The Recordset dialog box opens again, and you can change any of your settings. If you change the recordset's name after you've created it, Dreamweaver warns you that it's about to use Find and Replace to replace all occurrences of the recordset name throughout the document. Click OK to close this warning. The Find and Replace dialog box appears. To cancel the recordset name change, click Cancel in the Find and Replace dialog box. To change all occurrences at once, click Replace All. To change occurrences one at a time so that you can check that each one is correct, click Replace as many times as needed to fix the entire document.

What does the code for a recordset look like? Obviously, its appearance depends on what server technology and language you're working with. But at the heart of every recordset is a SQL query that looks something like one of these:

SELECT * FROM userTable SELECT username,password FROM userTable

The first example collects all fields from all records in the userTable of your database; the second collects only the username and password fields from that table. Although it's not crucial for you to know what your recordset's SQL query looks like, in database development, as in all HTML, knowledge is power, and the more you know about what's happening behind the scenes, the better off you'll be.

Getting More Sophisticated with Your Recordsets

If all you want to do is collect all records from a database table, creating the recordset is just a matter of the steps outlined here. But all too often, your needs are more complex than that. The more you know about collecting more-complex recordsets, the easier it will be for you to collect exactly the information you need from the database.

Sorting

Unless you specify otherwise , the server collects the records from your database in whatever order they happen to be in. To put the records in any other order, you must include a sorting clause in your query. In the Recordset (Query) dialog box, use the Sort by options to do this. You can sort by any field in the table you're collecting from. Sorting is always done in alphanumeric order (0 to 9, A to Z). Choose Ascending or Descending to sort forward or backward (Z to A, 9 to 0). Figure 22.4 shows a sorted recordset being collected and viewed . The SQL query behind a sorted query uses the SORT BY clause and looks something like this:

SELECT * FROM userTable ORDER BY username

Filtering

Use filtering when you don't want to collect all the records in the databaseonly those that meet certain criteria. You might want only users whose last names begin with A, for instance, or only people who live in the state of Maine.

To create a filtered recordset, open the Recordset (Query) dialog box and set the Filter options. This involves three drop-down menus :

• Filter by Choose any field in your recordset.

• Comparison Choose any comparison operator (=, >, <, >=, <>, begins with, ends with, contains).

• Required value Choose from one of the options here, or enter a value.

To find people who live in Maine, for example, your database table must have a field for state of residence. You can then choose the state field from the Filter by list, choose the equal sign for the second list, choose Entered Value for the type of filter, and type Maine in the input area.

Use the URL Parameter option if your page will be called from a form passing its variables using the GET method, or if the page will be called from a regular anchor tag that includes a URL parameter, like this:

<a href="dynamicpage.cfm?state=Maine">

Use the Form Variable choice if the page will be called from a form that passes its variables using the POST method. Passing form variables and URL parameters is discussed later in this chapter in the section "Creating a Search Page." Cookies, session variables, and application variables are covered in the next chapter.

The SQL behind a filtered query uses the WHERE clause. It looks something like this:

SELECT * FROM users WHERE state = "Maine"

Creating Advanced Recordsets with SQL

There are many more ways you might want to refine your data collection. The SQL language can accommodate just about all of them if you know how to structure your queries. Creating recordsets based on complex queries that involve joins, grouping, or other refinements requires that you work in Advanced mode and that you construct the SQL statement yourself.

To create an advanced recordset, open the Recordset (Query) dialog box and click the Advanced button. The resulting window, shown in Figure 22.5, includes a text area for typing SQL queries, a parameter definition area, and a database explorer area from which you can construct SQL queries without hand-coding .

Collecting Distinct Records

Suppose you have a list of 200 people in your database, of which 25 are from Maine, 25 are from California, and 50 are from New York. You want to create a recordset that indicates which states are represented, so you collect the state field from the userTable. But you don't want to collect 25 instances of Maine, 25 instances of California, and so on. You want to collect one instance of Maine, one of California, and so forth. The DISTINCT keyword in SQL queries lets you do this:

SELECT DISTINCT state FROM userTable ORDER BY state

To create a statement like this in Dreamweaver, do the following:

1. Open the Recordset (Query) dialog box. In Simple mode, create a query that selects only the field you want to group by. (For example, select only the state column from the userTable.) If you like, set the Sort by order here as well.

2. Click the Advanced button. In the SQL text area, add the word DISTINCT immediately before name of the field you want one instance of (see Figure 22.6). Before closing the dialog box, click the Test button to make sure you don't have any syntax errors in your SQL query.

   Figure 22.6. Creating a recordset of distinct instances in the Recordset (Query) dialog box