Section 21.1. Retrieving Information

21.1. Retrieving Information

Since databases can contain lots of information, you need a way to find just the data you need. Even though your company keeps information about its products, customers, suppliers, and so on in one database, you may be interested only in, say, an alphabetical list of all your customers. After securing that list, you might want to look at a particular customer's contact information, or perhaps the list of products that person bought.

21.1.1. Understanding Recordsets

To retrieve specific information from a database, you start by creating what's called a recordset. A recordset also called a database query is a command issued to a database asking for particular information: "Hey Database, show me all the customers listed in the Customers table." It's the heart of many database operations you'll perform in Dreamweaver (and a piece of jargon you can't escape in the dynamic Web page business).

Recordsets let you retrieve specified columns in a database. They can also sort records alphabetically or in numerical order, as when viewing a list of products from least to most expensive. In addition, a recordset can zero in on a specific record based on information submitted by a visitor to the site or on information provided in a URL. In essence, recordsets let you winnow down massive amounts of database information in a fraction of a seconda powerful benefit, indeed.

Note: ASP.NET note : Dreamweaver uses the term DataSet instead of recordset to refer to database queries in ASP.NET.

21.1.2. Creating Recordsets

Querying a database can be quite simple or extremely complex. Dreamweaver provides tools to get the novice database developer up and running quickly, while also supplying what's necessary to create more advanced recordsets. Whatever your level of expertise, you start by opening the Recordset dialog box using one of these methods (each of which assumes you've set up a server model, as described in Chapter 20):

Click the Recordset button on the Application tab of the Insert bar (see Figure 21-1).
Choose Insert Application Objects Recordset.
Click the + sign button on either the Bindings or Server Behaviors panels in the Application panel group (see Figure 20-9), and then select Recordset from the menu that appears.

Figure 21-1. The Application tab of the Insert bar (shown here split in half to fit on the page) provides one-click access to many powerful application objects, which automate common dynamic Web page-building tasks . (The Insert, Update, and Delete records buttons are discussed in the next chapter; the User Authentication features are discussed in Chapter 23; and the last option, XSL Transformation, is presented in Chapter 24.)

Whichever technique you choose, the Recordset dialog box opens (Figure 21-2). This box lets you create a database query or recordset, and provides both simple and advanced modes of operation.

Figure 21-2. The Recordset window lets you retrieve data from a database. The main window (pictured here) lets beginners search and sort databases for specific information. Advanced options let even seasoned database programmers take advantage of Dreamweaver's dynamic page-building abilities .

To create a simple query, make sure you're in the Simple mode. (If a button labeled Simple appears at the right edge of the dialog box, click it to make it say Advanced. Now you're in Simple mode.)

In the Name field, type a name for the recordset .

You can use any name you want, as long as it doesn't start with a number and doesn't contain any characters other than letters , numbers , and underscores (_).

Tip: A common technique is to begin the name with rs ( rsProducts , for example). The rs helps identify the recordset when you're working in Code view.
From the Connection menu, select a database connection .

The menu lists all of the database connections you've defined for the site. If you haven't yet created a connection, you can do so now by clicking Define and following the instructions for creating database connections on Section 20.5.
From the Table menu, select the table that'll supply the data .

Information in a relational database is usually distributed among different tables, each of which holds information about a particular type of item, such as customer data or product data (see Section 20.3.2). For example, to get a list of customers from a database, you'd select the Customers table (or whatever its name happens to be).

Note: To retrieve data from more than one table at a time, you need to create an advanced recordset (see Section 21.1.6.1).
To select columns from which you want to extract data, click the All or Selected button. If you choose Selected, then click the columns you wish to select .

By default, Dreamweaver highlights the All button, but you may not want to get data from all columns. For example, suppose your table contains lots of detailed information for each product your company sells. You may want to create a basic index of all your products that simply lists names , prices, and descriptions. For this index, you don't need all of the details like SKU number, sizes, inventory status, and so on. Therefore, just select the three columns you're interested in.

To select multiple columns, Ctrl-click ( -click) their names in the list in the Recordset dialog box.

It's always best to limit your recordset to just those columns whose information you need. The more data you retrieve, the more you make the application and database servers work, and the more you slow down your site, especially when the database is large.
Choose a Filter option, if you like .

In many cases, you don't want to retrieve every record in a table. For example, if you were looking for a phone number of a particular customer in your database, you wouldn't want the details on every one of your customers. Filters let you limit the records retrieved by a recordset. (Details on Section 21.1.3.)
Choose a Sort option, if desired .

Data from a database may not appear in any particular order. Dreamweaver's sort options let you sort information based on a particular column. For example, maybe you're creating a recordset that gathers the title and release date for every CD you own. You might want to sort the results in alphabetical order by the title of the album, or chronologically by the date they were released.

To sort database records, choose a column to sort by from the first Sort menu (Figure 21-2). Then select the sort order: either Ascending (AZ, 010, earliest to latest) or Descending (ZA, 100, latest to earliest).

The simple recordset mode lets you sort by only one column. So, continuing with the above example, if you want to sort records by date (so the most recent CDs appear first) and then by name (so CDs with the same date are then listed in alphabetical order), you have to use the advanced mode (see Section 21.1.6.1).

To view the results of the recordset, click Test to open the Test SQL Statement window, which contains all records that match that query. If there are more than 25 matches, you can see the next group of results by clicking Next 25 at the bottom of the window. When you're done looking at the test results, click OK to return to the Recordset window.

If the test results look right, click OK to close the Recordset window and add the code into the currently opened page.

Note: Unlike a database connection, which is listed in the Databases panel and is available to every page on the site, a recordset is specific to a particular page. (See Section 21.1.7 to learn how to reuse recordsets on other pages.)

21.1.3. Filtering Information

Although you may have selected a limited number of columns when creating a basic recordset, the final results of the recordset still include all of the records within the table. That's fine when you want a list of all items in a database, like when you're creating an index of all your company's products. But it's not so useful when you want a particular subset of those records, like a list of just the red toupees your company sells, or when you want details on a single recordthe "Flaming Inferno 78B toupee," for example.

To search a database table for specific records, use the Filter option in the Recordset window (see Figure 21-3). A filter lets you compare the information in one database column with a particular value and then select records that match. Suppose, for example, that your products database table contains a column named prodPrice that contains a product's price. To find all products that cost less than $35, you could create a filter that looks for all records where the price column holds a value of less than 35.

Figure 21-3. Filters let you limit the number of records retrieved by a recordset using information supplied in a URL, in a form submitted by a visitor to the site, or simply based on what you type into the recordset. Using a filter, a recordset can identify and retrieve data for a single record in the database.

Using the Filter feature in the Recordset dialog box takes only a few steps:

Create a recordset as described on Section 21.1.2 .

To create a filter, you must fill out the four form fields of the Recordset window's Filter optionsthree menus and one text field.
From the first Filter menu, select a column name .

This is the column that Dreamweaver compares to a particular value. In the example above, you would select "prodPrice" from the menu to indicate the table's price column (see Figure 21-3).
From the next menu, choose a comparison operator (< or >, for example) .

To find products whose prices are less than $35, for example, you would use the < (less than) operator. To find an exact value (all products that are exactly $35), use the = sign. Comparison operators are described below.
Using the third Filter pop-up menu, select a source for the comparison value .

A filter compares the information in a table column against some other value. There are many different sources for such a comparison value. For example, on a Search page, you could create a form that allows visitors to type in a search term and click a Search button. In this case, the comparison value would come from a form. To set up this arrangement, you, the designer, would select Form Variable from this menu.

For complete information on selecting a source for a comparison value, see the section "Getting Comparison Values" on the opposite page.
Into the lower-right Filter box, type a name or value .

The value for this field depends on the source you selected in the last step; type in the name of the form variable, cookie, session variable, or whatever. The one exception: If you selected Entered Value in the previous step, type a specific value in this field. For instance, to compare the "prodPrice" column to a specific value, you would select Entered Value and then type a number into the text field. The Recordset window would then look like Figure 21-3.
Complete the Recordset window by choosing a sort option (if desired) and then clicking OK .

You can test the recordset and filter by clicking Test. If you selected anything other than Entered Value from the source menu, a message prompts you to type in a test value for the sourceURL parameter, form variable, and so on.

21.1.4. Comparison Operators for Filters

Dreamweaver provides many different ways to compare information from a database column with information from another source, such as a form, cookie, or simply a value you type into the Recordset window. The type of comparison you choose also depends on the type of data you're comparing: text or numbers.

21.1.4.1. Comparing text values

You'll often want to create recordsets that find database matches to particular words. For example, a recordset could filter a list of products to find only those records whose descriptions contain the word "green," or you could filter a database of clients to search for a record for "Craig McCord." Dreamweaver provides the following types of text comparisons:

Equality . To check whether the information in a column is exactly the same as another value, select the = sign from the comparison menu.
Inequality . To find records that don't match a particular piece of text, select the <> (doesn't match) operator from the menu. You would use this, say, if you wanted to search a database of clothing for items that do not match a particular phrase (like "winter" in the Season column).
Begins With, Ends With, and Contains . The last three comparison operators are ideal for locating one or more words within text. For example, a database of movies might have a column containing a short review of each movie. To locate reviews that included the words "horrible acting," you could choose the Contains option, which will find any movie that includes the phrase "horrible acting" anywhere in its review.

The Begins With and Ends With options are more selective. The former finds records only when the text at the very beginning of a particular record matches; the latter works only when the text appears at the end. You probably won't use these options very often, but they could come in handy if you wanted to search a database for people whose names are Bob or Bobby, but not Joe-Bob. In this example, you'd use the "Begins With" option, and use Bob as the comparison value.

The other comparison operators (<, >, <=, >=) aren't very useful for searching text in a database. They're intended for comparing numbers, as described next.

21.1.4.2. Comparing numbers

Filters are particularly useful for numbers: finding products that cost less than $35, albums that were released in 1967, products with more than 3,000 items in stock. If you've taken basic algebra, these options for comparing numbers should be familiar: = (equal to), <> (not equal to), < (less than), > (greater than), <= (less than or equal to), or >= (greater than or equal to).

21.1.5. Getting Comparison Values

By now it should be clear that the Filter option of the Recordset window lets you compare data from one column with some other value. But you're probably wondering where this "some other value" comes from. It depends on which option you selected from the third drop-down menuthe Comparison Value Source menu (see Figure 21-3).

The most straightforward option is the last item in the menu: Entered Value. After selecting it, you simply type the value into the field to the right of the menu. This could be a number, a letter, or one or more words. So, to create a recordset that will find a product whose price is more than $50, you'd select the price column, the > (greater than) comparison symbol, and the Entered Value source option, and then type 50 into the value field.

Unfortunately, this kind of recordset is rather limited. The comparison value you specify (50) is hardwired into the recordset, making it very inflexible . What if a visitor wanted to see products that cost more than $15, $30, or $100? No joy. This recordset is limited to what you, the designer, entered as a value.

You're better off creating the filter on the fly from information you get when the visitor's Web browser requests the recordset. In this way, you can create very flexible recordsets that are capable of searching databases for a variety of different pieces of information, not just the one value selected by a programmer. (After all, how good a search engine would Yahoo! be if the programmers determined what the search criteria were? No matter what you searched for Web design, Used cars it would always find Web sites about Java, Burning Man, and Diet Coke.)

Dreamweaver can also draw a filter value from a form, cookie, or even a link's URL. The process is always the same: From the filter's Comparison Value Source menu (Figure 21-3), select the source you want, and then type the name of the appropriate source item. For example, if you select Form Variable from the source menu, type the name of the form field in the box to the right.

In most cases, you must depend on an additional Web page to provide the source of these values. For example, a search function on a Web site usually requires more than a single page: one (or more) pages containing a Search field and a Submit button to send the information, and another that displays the results of the search. In this example, the form on one page sends information (the search terms) to another page (the results page), which uses the form information to search the database. In essence, Dreamweaver uses the words typed into the search form on one page to create the recordset on another page.

The two most common ways to pass information from one page to another are forms and URLs. (Three advanced sourcescookies, session variables , and application variablesare discussed on Section 23.2.1.)

21.1.5.1. Form variables

A form variable is simply the information that a visitor types into a form field (or the value of a selected radio button, menu item, or checkbox). Forms are discussed in depth in Chapter 10, but their use in recordset filters is straightforward:

Create a form page .

It can include a text field, pop-up menu, or some other form element. Make sure you name the form element. For use in a simple recordset filter, you're limited to a single form variable. Using an advanced recordset (see Section 21.1.6.1), you can use information from more than one form field to filter the data in a recordset.

If you wanted to give your site's visitors a chance to look at differently priced products, for example, you could create a menu that included the values 10, 50, 100, 500, and so on. People could then choose one of those options to look at products below the selected price. (Also be sure to give the menu a name, such as "price," as described on Section 10.1.1.)
Set the Action property of the form (Section 10.2) .

You'll want it to point to the results page.

Note: For these steps to work, the form's method must be set to Post in the Property inspector (see Section 10.2). If Get is selected, the form information appears in the URL when the form is submitted. What's more, form information isn't sent as a form variable. (You can, however, use the Get method in conjunction with the URL parameters option discussed next.)
Open (or create) the results page .

This page displays the results of the recordset that's created using information from the form. This page needs to be a dynamic page using the server model you've chosenASP, PHP, and so on. (See Section 20.4 for information on how to create a new dynamic page.)
Add a recordset to the page, using the directions on Section 21.1.2 .

You'll also create a filter using a form variable.
From the Filter menu, select a database column. Then choose a type of comparison, as described on Section 21.1.4 .

All of this is the standard filter-creation routine.
From the source pop-up menu, select Form Variable. In the box to the right of the source menu, type the name of the form field that contains the value for comparison .

In keeping with the above example, you would type prodPrice into the box, since that's the name of the menu on the form page.
Add a sort option, if you like, and then click OK to create the recordset .

Remember that this kind of recordset's results depend upon information sent from a form. If a visitor just stumbles across the results page without using a form, the recordset most likely produces no results. That's why you should link to this kind of page only by using a form's Action property (see Section 10.2).

21.1.5.2. URL parameters

In your Web travels , you've probably encountered URLs that look kind of strange , along the lines of www.nationalExasperator.com/shopping.asp?prodID=34&quan-tity=4. Everything up to the ? looks fine, but you're probably wondering what the ?prodID=34&quantity=4 means.

Forms aren't the only way to pass information to a dynamic Web page; URLs can do it, too, thanks to information tidbits called parameters . Dynamic Web sites can read parameters and use them to create a recordset, among other things. (In fact, using the Get method for a form puts the form's information into the URL.)

By using the Recordset window's Filter option, you can use one of these parameters to search a database. To identify a single record in a database, for instance, the URL could contain a number identifying the record's primary key (see Section 20.3.2 for a definition). You'll find an example of this trick in the tutorial on Section 21.8.5.

The steps for using URL parameters to filter recordsets are similar to those for form variables. You need two pages, one with a link containing the URL parameter and another containing the recordset.

TROUBLESHOOTING MOMENT
The Default Value for a Filter Source

There's a problem with using a variable source of information for a filter. If the filter requires information from a form or URL parameter, what happens if someone comes to the page without first filling out the form or clicking a link with a URL parameter? In most cases, the recordset's empty, and the page displays no records. You can, however, set a default value for the form variable or URL parameter, so that at least some records always appear.

Using the steps outlined on Section 21.1.2, create a basic recordset; include a filter using a form variable or URL parameter. Then click the Advanced button in the Recordset window.

Now you get a more complex view of the recordset. In the Variables list, there's a single entry: the selected filter source (Form or URL). Click in the Default Value column and change the 1 to something that matches records in your database.

You could type a value that matches all the records in the database. For example, if the recordset's used to find products under a certain price, type a value (price) that's larger than the most expensive product in the database. This way, the recordset retrieves all items under that pricein other words, all the products. (This trick also works for the other sources discussed on Section 23.2.1: cookies, application variables, and session variables.)

One last word of warning. If you switch back to the basic recordset view by clicking the Simple button, Dreamweaver resets the recordset variable to the default value of 1. In other words, if you change the default value in the advanced view, don't switch back to the basic recordset view.

Note: It's possible to add a link with a URL parameter on the same page as the recordset. For example, you could have several text links like "Products under $10" and "Products under $100" that link to the same page but have different URL parameters.

21.1.5.3. Creating a link with a URL parameter

Dreamweaver provides several ways to create a link that contains a URL parameter. The simplest way is to highlight the item you wish to turn into a linkusually text or a graphic. Then, in the Property inspector's link box, type the link followed by a ?, a parameter name, an =, and the value (for example: products.asp?category=7 ).

However, you'll probably find it easier to browse for the file and let Dreamweaver write all the complex stuff. To do so, proceed as follows :

Highlight the item you wish to turn into a link .

In other words, select a graphic or text on the page.
Click the folder icon (browse button) on the Property inspector .

The Select File window appears. (For more on creating links, see Chapter 4.)
Browse to and select the page containing the recordset .

This is the page that displays the results of the database search.
Click the Parameters box in the lower-right corner of the Select File window .

The Parameters window appears (see Figure 21-4).

Figure 21-4. The Parameters window lets you add URL parameters to a link. Recordsets can use these pieces of information to filter a database query, as discussed on Section 21.1.3.
Click in the space below the Name column and type the name of the URL parameter .

Avoid spaces and any punctuation characters for the name, since you're likely to run into troubles when you try to use such a name in the recordset filter.
Click in the space below the Value column and type the value for the URL parameter .

This is the value that the filter in the recordset uses to match records in the database.

Usually this is a simple value like 17, blue , or yes . But you can also use spaces and punctuation marks in the valuefor example, Bob Jones , in order to search for "Bob Jones" in the database. However, you need to make sure Dreamweaver's Preferences are set accordingly : choose Edit Preferences (Dreamweaver Preferences); click the Code Rewriting category and make sure one of the "Encode special characters buttons is selected (the &# option supports a wider range of characters and is the choice suggested by Dreamweaver). Either of these options rewrites invalid characters in a form that works in a URL. For example, a space is converted to %20 .

Note: Forms using the Post method don't suffer from any of these problems and can accept all types of punctuation and space characters.
Click OK to close the Parameters window. Click OK to close the Select File window and apply the link .

21.1.5.4. Creating the recordset for the Results page

Once you've created the link, you need to create an appropriate recordset for the results page. Here's how:

Open (or create) the results page .

This page displays the results of the recordset created using information from the form.
Add a recordset to the page, using the directions on Section 21.1.2 .

You'll also create a filter using a URL parameter.
From the Filter menu, select a database column. Choose a type of comparison, as described on Section 21.1.4. From the source menu, select URL Parameter. In the box to the right of the source menu, type the name of the URL parameter .

This is the name supplied in step 5 of the previous instructions.
Add a sort option, if you like; click OK to create the recordset .

Like form variables, this recordset depends on information included in the URL of a link. If a visitor just stumbles across the results page without using a link with a URL parameter, the recordset most probably produces no results. Because of this, make sure you link to this kind of page only via a link with a parameter. Otherwise, modify the default value for the URL parameter in the recordset, as described in the box on Section 21.1.5.2.

Tip: Using URL parameters (as opposed to form variables) for retrieving records has an added benefit: since the parameter is embedded in the URLwww.nationalexasperator.com/products.php?category=5, for exam-pleyou can bookmark or email a link that matches a particular results page. In this way, you could bookmark, say, a page displaying all the products under $50. Then when you want to see if any new products (under $50) have been added to a site, you don't have to search the site again; merely revisit the bookmarked page.

21.1.6. Advanced Recordsets and SQL

Sometimes you'll need more power than Dreamweaver's simple recordset tool gives you. For example, say you're building an online classified ads system. On one page, you want to present various pieces of information: the name of the sale item, its price, who's selling it, and how to contact the seller, for example. In order to store this kind of information, your database has two tablesone for products and one for sellers.

To present all this information, you must simultaneously access both tables. In addition, you need to connect the records of those two tables so that each product is associated with the correct sellerJohn Smith is selling the Whirligig 2003, for example. There's only one way to create this kind of complex query: using the advanced options of the Recordset window.

To display these options, insert a recordset using the steps described on Section 21.1.2. Then, in the Recordset window, click the Advanced button. The Advanced Recordset window should appear (see Figure 21-5). (If you see a Simple button, then you're in the advanced options.)

Figure 21-5. The Recordset window's advanced options aren't for the uninitiated. You need to have a good grasp of SQLthe standard database program languageto make complex recordsets.

Unfortunately, putting together advanced database queries is not as easy as most other operations in Dreamweaver. The Advanced Recordset window is basically just a way of typing in commands, using a database programming language called SQL (Structured Query Language, pronounced "ess-cue-ell"). SQL is a standard language that many database servers use to access, update, delete, and add information to a database.

To create an advanced recordset, type an SQL statement in the window's SQL box.

21.1.6.1. SQL: The very basics

SQL lets you communicate with a database in order to add, update, and delete records. SQL even lets you do more advanced database work, such as adding new tables to a database and even deleting tables and databases. In the context of the Advanced Recordset window, you need to understand only how SQL retrieves information. After all, a recordset is just a selection of data pulled from the database.

To make an SQL query (called an SQL statement), you must first specify:

Which columns of data you want to retrieve . For example, product prices, product names, seller name, and seller contact information.
Which tables will supply this data . In the earlier example, the information is stored in two tables: Ads and Sellers.
How the search should be limited . You might just want products that are less than $10 or whose seller is Zachariah Smith.
The sort order . You could sort items using the Price column to view a list of products from least to most expensive, for example.

Only the first two pieces of information are absolutely necessary. A very basic SQL statement would look like this:

 SELECT prodPrice, prodName FROM products

SELECT is an SQL keyword that specifies columns of data for retrieval; FROM indicates which database table contains them. This statement instructs the database server to look inside the Products table and retrieve the price and name of each product listed. The result's a list of the price and the name of each product in the database.

UP TO SPEED
Getting Your Feet Wet in SQL

SQL isn't difficult to pick up. While you can create very complex SQL queries, a basic SQL statement is straightforward. Once you've reached the limits of Dreamweaver's basic recordset, you may want to expand your skills beyond this simple tool.

A great place to start learning how to write SQL statements is in Dreamweaver itself. After you create a simple recordset (see Section 21.1.2), click the Advanced button. The SQL statement for the simple query appears in the SQL box.

This chapter introduces the very basics of SQL. For a more complete introduction, check out SQLCourse.com (www.sqlcourse.com). Or pick up a book like SQL Queries for Mere Mortals (Addison-Wesley Professional) by Michael Hernandez or SQL in a Nutshell, 2nd Edition (O'Reilly) by Kevin Kline.

Note: SQL keywords are usually written in all capsSELECT, for example. This is just a convention, not a hard-and-fast rule. "select" would also work. But since it's easier to identify the keywords if they're capitalized, it's best to stick with this convention.

Of course, you may not always want every record in a table. You may want to limit the search to a select number of items, such as products under $10. The WHERE keyword lets you do just that:

 SELECT prodPrice, prodName FROM products WHERE prodPrice < 10

Now the SQL statement retrieves only the price and the name of products that cost less than $10. Finally, SQL can sort records into order. In this example, you could also sort all the results from least to most expensive, like this:

 SELECT prodPrice, prodName FROM products WHERE prodPrice < 10 ORDER BY prodPrice ASC

The ORDER BY keywords indicate which column Dreamweaver should use to sort the records. Specifying the prodPrice column sorts the items by price. ASC is short for ascending , meaning that the records appear in low-to-high price order. (DESC sorts records into descending order, ZA, or high-to-low.) You can even sort by multiple columns. If, for example, you wanted a list of all products sorted by price and then alphabetically by product name, you would simply change the above ORDER BY keyword to read like this:

 ORDER BY prodPrice ASC, prodName ASC

In this way, all the products that were the same price (for example, $10) would then be presented in alphabetical order (AZ).

21.1.6.2. Using the Data Tree view

Although you need to know SQL to use the Recordset window's advanced options, you can get a little help from the data tree in the "Database items" list at the bottom of the window (see Figure 21-5). This area of the window functions just like the Databases panel and lets you view the tables, columns, views, and stored procedures in the database (see Section 20.5.1).

Click the + (arrow) button next to the word Tables to see a list of all tables in the database. Click the + (arrow) next to a table name to see all the columns within that table. This technique's very helpful when you're building an SQL statement, because you may not remember the exact names of every table and column in your database.

To build an SQL statement, you can select a column name and click one of the three buttonsSELECT, WHERE, or ORDER BY. The SQL command and column name then appear in the SQL box.

Suppose, for example, you wanted to create the following SQL statement:

 SELECT prodPrice, prodName FROM products

To build this statement using the data tree, click the + button next to the table named Products, which expands to show a list of all columns. Then click the column named prodPrice and click SELECT. Next, click the prodName column, and then click SELECT again.

Although these buttons can save you time, they don't check whether the SQL statement is valid. Unless you've got some grasp of SQL, you can easily create a statement that generates errors when the test server runs it.

Tip: Be careful when using multiple database tables that contain the same column name. For example, it's possible to have two tables products and sellers , for examplethat contain columns with the same name, such as sellerID. Dreamweaver doesn't make any distinction when inserting columns with the same name using the Database Items tools (though it does pop up a warning if you attempt to test your SQL statement). To correctly differentiate like-named columns in the Advanced Recordset's SQL box, you must begin the column name with the name of the table, like this: products.sellerID or sellers.sellerID .

21.1.6.3. Creating variables for filtering data

Variables let you filter data using information from sources such as forms, URLs, cookies, session variables, and application variables. If you use the filtering option in the basic Recordset window, Dreamweaver creates the variables for youbut in the Advanced Recordset window, you must create them yourself.

To add a variable for use in an SQL query, follow these steps:

In the Recordset window, click the + button above the Variables box (see Figure 21-5). Click the empty space below the Name column and type a name for the variable .

The name shouldn't include spaces or other punctuation marks.

Tip: As with database connections and recordsets, it's a good idea to prefix the name of the variable so you can more easily identify it in the code. For example, you could begin the variable name with var varPrice, for instancejust as you'd begin a recordset name with rs (rsProducts, for example).
Press Tab to jump to the Default Value column. Type a default value for the variable .

A default value comes in handy when the form, URL, cookie, session variable, or application variable's empty. The recordset uses the default value to filter the database records.
Press Tab to jump to the Run-time Value column; type the appropriate code .

The exact code depends on the server model you selected. For example, in ASPspeak, you'd type Request.Form("price") to retrieve the value of a form field named price . To do the same thing in PHP-land, you'd type $_POST['price'] . The best way to learn how to create variables is to use Dreamweaver's filter tool in the Recordset window (see instructions on Section 21.1.3), and then switch to the Advanced Recordset window. The proper code for collecting information from forms, URLs, cookies, and so on appears in the variables' Run-time Value column.

Note: Keep in mind that if you add more than one SQL variable in the Advanced Recordset window, you can't switch back to the simple view.

Once you create a variable, you can include it in your SQL statement. Since variables help filter information, you'll often add them to the SQL WHERE keyword. For example, if you create a variable named FormPrice that retrieves information from a form, you can add it to the SQL statement like this:

 SELECT prodPrice, prodName FROM products WHERE prodPrice < formPrice

In this example, whatever information is passed from the form is stored in the FormPrice variable and compared to the price stored in the prodPrice column of the database.

21.1.7. Reusing Recordsets

Recordsets are created on a page-by-page basis. In other words, when you create a recordset, it's added only to the current document. If you create another Web page that requires the same recordset, you must add the proper code to the new page. You can do this either by recreating the recordseta potentially laborious processor by simply copying the recordset from one page and pasting it into another.

Here's how:

Open the Server Behaviors panel by choosing Window Server Behaviors .

Ctrl+F9 ( -F9) also works. You can also copy and paste from the Bindings Panel.
Right-click (Control-click) the name of the recordset you wish to copy; choose Copy from the shortcut menu that appears .

In the Server Behaviors panel, recordsets appear like this: Recordset (rsName) , with the name of the recordset inside the parentheses.

Now switch to the document that'll receive the pasted recordset. Right-click (Control-click) in the Server Behaviors (or Bindings) panel, and then choose Paste from the shortcut menu.

Tip: If you need a recordset that's similar to a recordset you've already createdbut not identicalyou can copy the original recordset, paste it into a new document, and then edit it, following the instructions in the next section.

21.1.8. Editing Recordsets

What if you have to edit a recordset? Maybe you forgot an important column of information when you originally created the recordset, or perhaps you want to modify a recordset you copied from another page. The process is easy: simply open either the Bindings panel (Ctrl+F10 [ -F10]) or Server Behaviors panel (Ctrl+F9 [ -F9]) and double-click the name of the recordset you wish to edit.

The Recordset window appears, looking just as it did when you first created the recordset (see Figure 21-2). Make any changes to the recordset, and then click OK.

Tip: If you change the name of a recordset while editing it, Dreamweaver displays a message indicating that you need to use Find and Replace (Section 18.3) to locate and update every instance of the recordset's name. Dreamweaver opens the Find and Replace window for you when you click OK, but it's up to you to make sure the changes are correct.This is another reason why beginning a recordset with "rs" ( rsProducts , for example) is a good idea. If you've named a recordset simply "products," you could end up finding and replacing not only the name of the recordset, but also any other cases where the word "products" appears in the page.The safest (although slowest) way to change a recordset's name is to recreate it. Of course, that's extra efforta good argument for making sure you're satisfied with a recordset's name when you first create it.

21.1.9. Deleting Recordsets

If you add a recordset to a page and later realize that the page isn't using any of the information retrieved by the recordset, you should delete it. Each recordset forces the database server to do some work. Unnecessary recordsets only make your Web pages work harder and more slowly.

You can delete a recordset using either the Bindings or Server Behaviors panel. Just select the name of the recordset in either panel and click the minus sign (-) button at the top of the panel (pressing Delete on your keyboard has the same effect).