Working with Complex Recordsets

[ LiB ]

SQL is a powerful language that can collect data in all sorts of ways. To help you with this, the Recordset dialog box can operate in simple or advanced mode. Simple mode is quick and easy for collecting, filtering, and sorting basic recordsets. But for table joins, grouping, using calculated fields, and other more sophisticated tasks , only advanced mode will do (see Figure 23.12). In this mode, you can write your own SQL, use the Data Tree to help create complex statements, and incorporate variables (parameters) into your SQL.

Figure 23.12. The Recordset dialog box in Advanced mode.

Writing SQL Queries

Welcome to the wonderful world of SQL! SQL is a straightforward language on the surface, with an amazing amount of complexity hidden in its depths. It's not hard to get into, though. A good way to get used to the syntax for simple statements is to create a recordset in Simple mode and then peek into Advanced mode to see what code created the recordset. SQL is not case-sensitive, but it's customary to use all caps for SQL keywords. SQL statements can also be written on multiple lines for easier reading.

For online SQL help, check out the Dreamweaver Reference panel. From the Book drop-down menu, choose O'Reilly SQL Reference.

The Basics

The kinds of recordsets created in Simple mode use selecting, filtering, and ordering to collect their data:

Selecting Data is collected with SELECT statements. The statement must specify which fields are being collected. An asterisk indicates that all fields should be collected. There must also be a FROM clause, specifying which table to use:
```
 SELECT * FROM stockitems SELECT itemname, price FROM stockitems 
```
Other data-collection refinements all build on the SELECT statement by adding clauses to it.
Filtering Data is filtered by adding a WHERE clause to the SELECT statement:
```
 SELECT * FROM stockitems WHERE price < 300 SELECT * FROM stockitems WHERE category = 'clocks' 
```
Note that string values (text) are placed in single quotes within a SQL statement, but numeric values are not. WHERE clauses can also specify multiple search criteria using logical operators such as AND and OR:
```
 SELECT * FROM stockitems WHERE price < 300 AND category =  'clocks' 
```
Sorting Records are sorted by adding an ORDER BY clause. Records can be ordered by any field and can be ordered in ascending (ASC) or descending (DESC) order:

 SELECT * FROM stockitems ORDER BY itemname ASC

The ORDER BY clause must be the last item in the SELECT statement. If you omit the ASC/DESC keyword, the records are sorted in ascending order by default.

Getting Fancier

Beyond these basics, a whole world of complexity awaits. Data can be collected from multiple tables at once, have calculations performed on it during collection, and more.

Collecting One Instance Only

You can collect only one instance of every different value in a particular field by adding the DISTINCT keyword to the statement's SELECT clause:

 SELECT DISTINCT category FROM stockitems

Calculations

You can perform calculations on the data as it's being retrieved from the database by using various calculation keywords and operators, creating calculated fields. Calculations can be used to summarize information across multiple records or to process record information as it's being retrieved. Calculations can be used with the AS keyword to assign the calculated value a descriptive name , which then shows up in the recordset. Tables 23.1 and 23.2 show the SQL syntax for some common calculations.

Table 23.1. Calculations for Summarizing Data

Calculation	Description	Example
`SUM()`	Adds the values of all records for that column	SELECT SUM(price) AS total price FROM stockitems
`COUNT()`	Counts the number of records	SELECT COUNT(*) AS totalitems FROM stockitems
`AVG()`	Returns the average value of all record values for the specified field	SELECT AVG(price) AS avgprice FROM stockitems
`MAX()`	Returns the highest value for all records in the specified field	SELECT MAX(price) AS highprice FROM stockitems
`MIN()`	Returns the lowest value for all records in the specified field	SELECT MIN(price) AS lowprice FROM stockitems

Table 23.2. Calculations That Process Data from Each Record Before Collecting It

Calculation	Description	Example
Concatenation	Collects text values in multiple fields as one value	SELECT fname + ' ' + lname AS fullname FROM customers
Mathematical calculation	Performs mathematical calculations on numeric field values before returning them	SELECT price/2 AS saleprice SELECT price - discount AS saleprice

Joining

Databases are made up of multiple tables, and sometimes you need to collect pieces of data from several tables. You do this by temporarily joining the tables with a join statement. There are different kinds of joins and different requirements for different database systems. A simple join just collects fields from multiple tables, specifying the table name as well as the field name where needed:

 SELECT stockitems.itemname, stockitems.price, customers.* FROM  stockitems, customers

This statement collects the itemname and price fields from the stock-items table, as well as all fields from the customers table.

The more complex your database (the more it relies on interrelated tables) the more important joins are. For instance, look again at the table structure of the more_antiques database (see Figure 23.5), used in this chapter's exercises. Each customer's favorite category of antiques is identified by an ID, which matches an item in the categories table. Finding out the name of a particular customer's favorite category requires accessing both tables:

 SELECT categories.id, categories.name, customers.favorite FROM categories, customers WHERE customers.id = 2 and customers.favorite = categories.id

Using the Database Items Tree to Write SQL

At the bottom of the Advanced Recordset window, the database items tree area lets you choose items from the database structure, along with SQL keywords, to construct SQL statements without actually writing them yourself. This is a handy way to avoid all that typing and get a little help with the syntax. But you still need to know basically what the syntax should be to make this method work.

To create a SELECT statement using the data tree, do the following:

Look through the data tree and select the first field you want. Click the SELECT button to the right of the tree. A SELECT statement gets added to the SQL window, as shown in Figure 23.13. Note that this method always identifies each field along with the table it belongs to, which is correct although not necessary when you don't use joins.

Figure 23.13. Using the data tree to select the id field from the customers table of a database.
To select additional fields from the same table or a different table, repeat step 1. Dreamweaver doesn't add a new SELECT statement; instead, it adds the new field to the existing statement.

To add a WHERE clause, do the following:

From the data tree, select the field you want to filter by, and click the WHERE button. This inserts the first part of the WHERE statement but not the rest of the comparison statement.

It's up to you to enter a comparison operator into the SQL area where the statement is being constructed . Table 23.3 lists common comparison operators.

Table 23.3. Common Comparison Operators in SQL

Operator	Meaning	Example
=	Equal to	`username = "Fred"`
<>	Not equal to	`username <> "Fred"`
!=	Not equal to	`username != "Fred"`
>	Greater than	`price > 50`
>=	Greater than or equal to	`price >= 50`
!>	Not greater than	`price !> 50`
<	Less than	`price < 200`
<=	Less than or equal to	`price <= 200`
!<	Not less than	`price !< 200`
BETWEEN	Between two values	`price BETWEEN 50 AND 200`

If the clause is filtering according to a hard value (such as less than 3, or equals "Fred"), you have to type that in as well. If the clause is comparing two fields, type the comparison operator yourself, select the field to compare to, and click the WHERE button again.

To add an ORDER BY clause, do the same thing: Select a field, and click the ORDER BY button to add it to the code. Dreamweaver always adds the clauses in the proper order, so the ORDER BY clause is at the end of the query.

Using Parameters in SQL Statements

Parameters, or variables, are pieces of the SQL query that are determined at runtime. For instance, a URL parameter, passed form variable, cookie, or session variable may determine filtering, sorting, or other elements that go into the SQL query. When you filter a recordset using the Filter options in the Simple Recordset dialog box, parameters are added to your SQL query behind the scenes. Other uses of parameters require using the parameters area of the Advanced Recordset dialog box.

To see how a valid parameter should be entered into the parameters area, examine a recordset that uses Filter options, and take a look at it in Advanced mode.

The parameters area of the Advanced Recordset dialog box looks different, and behaves differently, depending on which server model you're using. They're best discussed separately.

Parameters for PHP

Figure 23.14 shows the parameters area of the dialog box for PHP.

Figure 23.14. The parameters area of the Advanced Recordset dialog box for PHP pages.

To create and use a parameter in a PHP page, follow these steps:

With the Advanced Recordset dialog box open , click the + button in the parameters area to create a new parameter.

Give the parameter a one-word name that you'll use in the SQL query to reference it. You can pick any name except reserved SQL keywords. Descriptive names make it easier to keep track of what you're doing. (Table 23.4 lists common reserved keywords.) For instance, if the parameter will determine sort order, sortOrder or sortby would be descriptive.

Table 23.4. Reserved SQL Keywords You Might Be Tempted to Use

Keyword	Keyword
ABSOLUTE	ACTION
AUTHORIZATION	AUTO
BACKUP	BULK
CATALOG	CHARACTER
CHECK	CHECKPOINT
COMMENT	COMPUTED
CONFIRM	CONNECTION
COUNT	CUBE
CURRENT	DATABASE
DATE	DATETIME
DAY	DESCRIBE
DOMAIN	DOUBLE
DUMMY	DUMP
ESCAPE	EXIT
EXTERNAL	FILE
FLOAT	FLOPPY
IDENTITY	INDEX
LANGUAGE	MONTH
MINUTE	MODULE
NATIONAL	NATURAL
OPTION	ORDER
OUTPUT	OVERFLOW
PAD	PAGE
PAGES	PASSWORD
PERCENT	PERMANENT
POSITION	PROCEDURE
REFERENCES	RELATIVE
SEGMENT	SEQUENCE
SHADOW	SHARED
SIZE	SNAPSHOT
SPACE	STATISTICS
TEMPORARY	TIME
TRANSACTION	TRANSLATE
TRIGGER	TRIM
UPDATE	USER
VOLUME	WHEN
WHERE	WORK
YEAR	ZONE

Supply a default value for the parameter. This makes it possible to test your query before runtime, but more importantly, it keeps the page from generating an error if for some reason the parameter doesn't get supplied at runtime. (For instance, if the parameter will come from a URL parameter, what if the page gets called with no URL parameter?) If the parameter is supposed to determine sort order, a default value would be the name of a field to sort by.

Supply a runtime value. This is the dynamic value that is actually used when the page is run. It must be a valid PHP expression calling up a URL parameter, session variable, and so on. Table 23.5 lists the required syntax for different kinds of runtime values.

Table 23.5. Syntax for the Runtime Values of PHP Parameters

Type of Parameter	Syntax
URL parameter (or form value passed using GET)	$_GET['myParam'] $HTTP_GET_VARS['myParam']
Form variable passed using POST	$_POST['myParam'] $HTTP_POST_VARS['myParam']
Cookie collected from the user's computer	$_COOKIE['id'] $HTTP_COOKIE_VARS['myParam']
Session variable set for this user's browsing session	$_SESSION['myParam'] $HTTP_ENV_VARS['myParam']
Environment variables	$_ENV['myParam'] $HTTP_ENV_VARS['myParam']
Server variables, storing information about settings on the server	$_SERVER['myParam'] $HTTP_SERVER['myParam']

The Syntax column of Table 23.5 lists two syntax examples for each parameter. Either syntax is correct. Dreamweaver uses the first.

Finally, incorporate the parameter's name into the SQL query. You have to do this by typing, so you have to know enough SQL syntax to know how to do that. Just substitute the name where the value should go. For example, if the parameter determines sort order, and its name is sortby, you would want the following in the SQL area:

 SELECT * FROM stockitems ORDER BY  sortby

Parameters for ColdFusion

Figure 23.15 shows the parameters area of the Advanced Recordset dialog box for ColdFusion.

Figure 23.15. The Parameters area of the Advanced Recordset dialog box for ColdFusion pages.

To create and use a parameter in a ColdFusion page, do the following:

With the Advanced Recordset dialog box open, click the + button in the Page Parameters area to create a new parameter.

When the Edit Parameter dialog box opens, enter a name indicating what kind of parameter it is (URL, form variable, and so on) and its name. Table 23.6 lists the kinds of parameters available in ColdFusion and their syntax; your entry in the Edit Parameter dialog box should match the Name column in this table. If the parameter will be a URL parameter called sortby, for instance, your entry should be URL.sortby , or just sortby . (Either is correct.)

Table 23.6. Syntax for Defining and Using ColdFusion Parameters

Type of Parameter	Name	Usage in SQL Query
URL parameter (or form value passed using GET)	URL.myParam	#URL.myParam# #myParam#
Form variable passed using POST	FORM.myParam	#FORM.myParam# #myParam#
Cookie collected from the user's computer	COOKIE.myParam	#COOKIE.myParam# #myParam#
Session variable set for this user's browsing session	SESSION.myParam	#SESSION.myParam# #myParam#
Application variable	APPLICATION.myParam	#APPLICATION.myParam# #myParam#

Still in the Edit Parameter dialog box, enter a default value. This value makes it possible to test your query before runtime, but more importantly, it keeps the page from generating an error if for some reason the parameter doesn't get supplied at runtime. (For instance, if the parameter will come from a URL parameter, what if the page gets called with no URL parameter?) If the parameter is supposed to determine sort order, a default value would be the name of a field to sort by.
Click OK to close the Edit Parameter dialog box.
Finally, incorporate the parameter's name into the SQL query. You have to do this by typing, so you have to know enough SQL syntax to know how to do that. Substitute the name, surrounded by pound signs (#), where the value should go. If the parameter is a URL parameter that determines sort order, and its name is sortby, you would want one of the following in the SQL area:

 SELECT * FROM stockitems ORDER BY  #URL.sortby#  SELECT * FROM stockitems ORDER BY  #sortby#

(This corresponds to the information in the Usage column in Table 23.6.)

The Syntax column of Table 23.6 lists two syntax examples for each parameter. Either syntax is correct. Dreamweaver uses the first.

Parameters for ASP.NET

Figure 23.16 shows the parameters area of the Advanced Recordset dialog box for ASP.NET.

Figure 23.16. The parameters area of the Advanced Recordset dialog box for ASP.NET pages.

To create and use a parameter in a .NET page, follow these steps:

With the Advanced DataSet dialog box open, click the + button in the parameters area to create a new parameter.
When the Edit Parameter dialog box opens, click the + button to add a new parameter.
When the Add Parameter dialog box opens, name your parameter. The name is what you will refer to in the SQL query, so make it descriptive. In ASP.NET, you want to name the variable with an @ symbol. If you want to filter the query on the id field, name the Parameter @id .
In the Type drop-down menu, pick the variable's type. This can be complicated if you don't know what the type of the field is in the database. For numbers , it usually is Integer. For text, it usually is WChar. For a complete description of each type, check an ASP.NET reference.

You can add the ASP.NET code to the value field by hand, but it is easier to click the Build button.

In the Build Value dialog box, shown in Figure 23.17, the name defaults to the name in the Add Parameter dialog. In this case, it is @id . Leave the name unchanged.

Figure 23.17. The Build Value dialog box set for a variable named @id .

In the Source drop-down, you can specify where the variable is coming from: a URL Parameter, Session Variable, Application Variable, Cookie, or Form Variable. Select URL Parameter.

In the Default Value field, you can set a default value if you want a result to be returned even if no value is passed in. You can leave this blank.

Click OK to exit the Build Value dialog box.
In the Add Parameter dialog box, shown in Figure 23.18, the Value field is now filled with the ASP.NET code to test for the existence of a form.

Figure 23.18. The Add Parameter dialog box.
Click OK to return to the DataSet dialog box.
Now that the parameter is defined, you need to add it to your query. If your parameter is designed to be used as a filter in a WHERE clause to limit the query to finding only records that have a specific ID, you would put the following in the SQL area:
```
 SELECT basename, id FROM stockitems WHERE id = ? 
```
Click the Test button to make sure your syntax is correct. Then click OK to close the dialog box.

Table 23.7 lists the syntax for defining and using ASP.NET parameters.

Table 23.7. Syntax for the Runtime Values of ASP.NET Parameters

Type of Parameter	Name	Value
URL parameter (or form value passed using GET)	`@id`	IIf((Request.QueryString("id") <> Nothing), Request.QueryString("id"), "")
Form variable passed using POST	`@id`	IIf((Request.Form("id") <> Nothing), Request.Form("id"), "")
Cookie collected from the user's computer	`@id`	IIf((Not Request.Cookies("id") Is Nothing), Request.Cookies(IIf((Not Request.Cookies("id") Is Nothing), "id", 0)).Value, "")
Session variable set for this user's browsing session	`@id`	IIf((Not Session("id") Is Nothing), Session("id"), "")
Application variable for long- term storage of information	`@id`	IIf((Not Application("id") Is Nothing), Application("id"), "")

Exercise 23.3. Collecting a Complex Recordset to Create a Dynamically Sortable Display (ColdFusion, PHP, and ASP.NET)

A dynamic page presents recordsets that have been sorted based on one of the recordset fields. But what if the page visitor needs the information sorted some other way? A popular interface feature is to let visitors click the different headers of a dynamic table to re-sort the contents according to that header. Click the Date column to sort by date, click the Author column to sort by author, and so on (see Figure 23.19).

Figure 23.19. The Macromedia Exchange displays information in tables that can be dynamically sorted by Date, Software, and so on.

The mechanism works like this: Each column header links back to the current page, passing it a URL parameter that must be used to determine the recordset's sort order. This is similar to how a search-and-results page works, but it has to be created differently in Dreamweaver.

In this exercise, you'll add dynamic sorting links to the column headers in the Antiques Barn pricelist table.

Working within the Chapter 23 site, create a new file based on the main template. (Choose File > New to bring the Templates tab to the front, and choose Main.) Save it as pricelist (plus your filename extension).
You need to create a recordset that includes each item's name and price and the name of its category. These records reside in fields across two tables. The stockitems table includes itemname and price. It contains a category field as well, but the field contains only a category ID number, not the category's name. For each record, you need to collect the name field from the categories table, and you need to compare the stockitems table's category field to the categories table's ID field to determine which category name goes with which stock item. Whew! You need to use a join (joining fields from the category and stockitems tables) with a WHERE clause to construct this set of data.

Create a recordset by using either the Bindings panel or the Recordset object in the Insert bar. When the Recordset dialog box opens, go to Advanced mode.

Using the Database Tree, expand the Tables hierarchy until you see the fields in the stockitems table. Select itemname, and click the SELECT button. Select price from the stockitems table, and click the SELECT button. Then expand the categories table and use the same method to add the name to your SQL query. In the SQL area, place the insertion point immediately following categories.name and type the following:
```
 AS category 
```
Next , you need a WHERE clause to specify which category names should be collected. In the data tree, expand the stockitems table, and select the category field. Click the WHERE button to begin the clause. Go to the SQL area, make sure the insertion point is at the end of the text that's there, and type in the rest of the WHERE clause:
```
 = categories.id 
```
From the data tree, select the itemname field of the stockitems table. Click the ORDER BY button to sort the records. At this point, your query should look like the one shown in Figure 23.20. Click the Test button to make sure your SQL query has no errors. Then click OK as many times as you need to to close all the dialog boxes.

Figure 23.20. The recordset for the Antiques Barn pricelist page.
For ColdFusion and PHP, insert a dynamic table into the maincontent editable region. Set the table to display all records, with border and cellspacing of 0 and cellpadding of 10 (see Figure 23.21). For the purposes of this exercise, you know there aren't very many entries in the database, so there's no need to deal with recordset paging.

Figure 23.21. Inserting a dynamic table into the pricelist page.

For ASP.NET, insert a table into the maincontent editable region with two rows and three columns . Set the table border and cellspacing to 0 and the cellpadding to 10. In the first row of the table, add the headers for each column: itemname, price, and name. From the Bindings panel, expand the DataSet you just created, and drag the itemname binding to the table cell below the itemname label you just entered. Drag the price and name bindings from the DataSet to the corresponding cells in the table. Select only the row containing the bound data, and apply a Repeat Region server behavior. In the Repeat Region Server Behavior dialog box, select the DataSet you created, and set it to show all records.
When the table is in place, assign it the CSS class .pricetable . Also select the top row of cells and convert the cells to header cells (for accessibility and styling).
Before previewing, remember to upload your entire site to the testing server. Figure 23.22 shows the price table as it should look now.

Figure 23.22. The finished pricelist page, previewed in a browser.
Now it's time to add dynamic sorting. Each of the headers must link back to the current page, but with a URL parameter indicating which field of the recordset to sort by.

Select the itemname column header. In the Property Inspector's Link field, click the Browse button to open the Select File dialog box. Browse to the pricelist page, and select it. Click the Parameters button to open the Parameters dialog box. For the parameter name, enter sortby . For its value, enter itemname , as shown in Figure 23.23. Close all the dialog boxes by clicking OK.

Figure 23.23. Linking the itemname column header back to the current page, but with a URL parameter to indicate sorting order.
Repeat step 6 for each of the other column headers, each time assigning a URL parameter with the name sortby and the value matching the column header's name (price, category).
You need to change the recordset so that it uses the sortby parameter. In the Server Behaviors panel, open the recordset by double-clicking it. The Recordset dialog box should open in Advanced mode.
What you do next depends on which server model you're using:

In PHP, in the Parameters area, click the + button to add a new parameter. Name it sortby , give it a default value of itemname, and enter the following for its runtime value:
```
 $_GET['sortby'] 
```
This collects the value of a URL parameter whose name is sortby.

In the SQL area, find the ORDER BY clause, and change it to look like this:
```
 ORDER BY sortby 
```
Click Test to make sure that you have no errors. Then close all the dialog boxes by clicking OK.

In ColdFusion, in the Page Parameters area, click the + button to add a new parameter. In the Edit Parameters dialog box, name it URL.sortby , and give it a default value of itemname .

In the SQL area, find the ORDER BY clause, and change it to look like this:
```
 ORDER BY #URL.sortby# 
```
Click Test to make sure you have no errors. Then close all the dialog boxes by clicking OK.

In ASP.NET, in the Parameters area, click the + button to add a new parameter. Name it sortby , set the type to Wchar, and click the Build button.

In the Build Value dialog box, set the Source to URL Parameter, and set the Default value to itemname. Click OK to go back to the Add Parameter dialog box. At this point, you need to do a little more work. Dreamweaver's ASP.NET DataSet accepts parameters in the WHERE clause, but not in the ORDER BY clause. To make this parameter work, you have to hand-code a little bit in the Advanced DataSet dialog using the values created here in the Add Parameter dialog box. In the Value area of the Add Parameter dialog box, select the entire contents and copy them by pressing Ctrl+C. Click Cancel to close the dialog box. You don't want the parameter to actually be added; you just want the code it would have added.

At the beginning of the SQL statement in the SQL area, add a double quote (").

Find the ORDER BY clause, delete the itemname, and replace it with another double quote ("). After the double quote you just added, add a space, an ampersand (&), and another space. Then paste the code you copied from the Add Parameter dialog box.

Your code should look like this:
```
 "SELECT stockitems.category, stockitems.itemname,  stockitems.price,  categories.name FROM stockitems, categories WHERE stockitems.category = categories.id ORDER BY " & IIf((Request.QueryString("sortby") <> Nothing),  Request.QueryString("sortby"), "itemname") 
```
You can check most queries by clicking the Test button to make sure you have no errors. In this case, because the query you have built is not what Dreamweaver expects to see, the test function doesn't work properly. Close the DataSet dialog box by clicking OK.
Finally! Preview the result in the browser. Clicking each of the column headers reloads the page, with the contents sorted by that header.