Creating Simple Recordsets


Dreamweaver creates recordsets based on information you enter in the Recordset dialog (Figure 6.1). You choose a table from a specific database, and then use selected fields or all the fields in the table. A recordset temporarily stores the data returned from a database query. The recordset is stored in the application server's memory so that the data can be retrieved quickly and discarded when it is no longer needed.

Figure 6.1. You can automatically create a SQL query using the Recordset dialog.


If you want to include only some of the data, you can add a filter when you create the recordset. For example, if your database table includes a Name field, you first add the Name field to the recordset, and then add a filter to select only those records in which the data in the Name field starts with, say, an A. If you want the recordset results to be in a particular order, you can add a sort order when you create the recordset. For example, if your database table includes a City field, once you add the City field to the recordset, you can add a sort order so that the recordset records are sorted in alphabetical order based on the City. Otherwise, recordset results are displayed in the order in which the records were added to the database table.

Using the Macromedia Sample Databases

The cafetownsend database is included with Dreamweaver 8, and comes in two forms, an Access database (tutorial.mdb) that you can use with ASP or ColdFusion, and a SQL file (insert.sql) that you can use to create a MySQL database to use with PHP. To find the sample database file on Windows, from the Start menu, choose My Computer > Local Disk (C) > Program Files > Macromedia > Dreamweaver 8 > Tutorial_assets > cafe_townsend > data > tutorial.mdb. You can use this database with ASP or ColdFusion. If you use PHP with Windows or if you're using PHP with Mac OS X, you can use the MySQL database that you must create from the SQL file (insert.sql) included with Dreamweaver 8. For more information, see "Creating the MySQL Database" in Dreamweaver Help (F1). In addition, Chapter 5 includes details on creating a MySQL database.

The CompassTravel database is included with ColdFusion MX 7. For more information on creating a connection to the CompassTravel database, see the section "To connect to a data source using ColdFusion" in Chapter 4.


When you are finished creating your recordset, Dreamweaver saves the recordset so that you can use it more than once on the same page.

The following exercises in this section use the Locations table from the sample Access database (tutorial.mdb) that is included with Dreamweaver 8. If you're running Windows, from the Start menu, choose My Computer > Local Disk (C) > Program Files > Macro media > Dreamweaver 8 > Tutorial_assets > cafe_townsend > data > tutorial.mdb to open it. You can use this database with ASP or ColdFusion. If you're using a Mac, or you prefer to use PHP, you can use the Locations table in the MySQL database that you create from the SQL file (insert.sql) included with Dreamweaver 8. For more information, see "Creating the MySQL Database" in Dreamweaver Help (F1). In addition, Chapter 5 in this book includes details on creating a MySQL database.

Whether you use ASP, PHP, or ColdFusion, you need to create a connection to the sample database before you start the tasks in this section. See Chapter 4 for more details on creating a database connection.

In this section, we'll create a simple ASP, PHP, or ColdFusion recordset, filter a ColdFusion recordset, and sort an ASP recordset. The basic process for defining a simple recordset and filtering or sorting the recordset is the same for ASP, PHP, ColdFusion, JSP, and ASP.NET. There are a few differences, however, in the Databases panel, the Bindings panel, and the Recordset dialog for ColdFusion, so a separate exercise is included for creating the recordset in ColdFusion.

To create an ASP or PHP recordset from the Bindings panel:

1.

Open an ASP or PHP page in Dreamweaver.

You need to have an ASP or PHP page open in the Document window to use the Bindings panel. You won't be making any changes to the page itself, so it doesn't matter if it's an existing page or a new one. If you don't already have an ASP or PHP page, choose File > New > Dynamic Page, and then select ASP JavaScript, ASP VBScript, or PHP to create one.

2.

In the Application panel group, click the Bindings tab to access the Bindings panel (Figure 6.2).

Figure 6.2. Open the Bindings panel to add a new recordset.


3.

Click the plus (+) button, and from the contextual menu, choose Recordset (Query) (Figure 6.3).

Figure 6.3. To open the Recordset dialog, on the Bindings panel click the plus button and choose Recordset (Query).


The Recordset dialog appears (Figure 6.1).

4.

In the Name field, enter a name for the recordset. Choose a name that will help you identify the recordset later when you need it. For this exercise, we're using the Locations table, so we're naming our recordset locations.

5.

In the Connection field, select the name for your connection to the sample database from the drop-down list. This will be the name you gave the connection when you created it.

(If you haven't already created a database connection, see Chapter 4.)

6.

In the Table field, choose the Locations table.

The Table field names appear in the box below the All and Selected radio buttons (Figure 6.4). All the cafetownsend locations included in the cafetownsend database are airports. The fields in the Locations table contain data for the location code, name, address, city, state, country, and region.

Figure 6.4. Once you select a table in the Table field, the names of all the fields in that table display in a list.


7.

In the Columns section, choose the Selected radio button. Select each field you want to include from the box below the radio buttons. For the purposes of this exercise, those fields are LOCATION_NAME, CITY, and STATE_COUNTRY (Figure 6.5). To make multiple selections from the list, hold down the Ctrl key (Windows) or the Shift key (Mac).

Figure 6.5. If you choose the Selected radio button in the Columns section, you need to select each column name from the list.


8.

Click the Test button to see the contents of the recordset. The data for the location name, city, and state or country for each airport in the Locations table is displayed in the Test SQL Statement dialog (Figure 6.6). Click OK to return to the Recordset dialog.

Figure 6.6. View the entire ASP recordset in the Test SQL Statement dialog.


9.

If you'd like to view the SQL statement generated by Dreamweaver to create the recordset, click the Advanced button to display the advanced Recordset dialog (Figure 6.7). The SQL statement appears in the SQL field. Click the Simple button to return to the Recordset dialog, and then click OK to save the recordset and close the dialog.

Figure 6.7. You can view the actual SQL statement in the advanced Recordset dialog.


The recordset name appears on the Bindings panel (Figure 6.8).

Figure 6.8. The name of the new ASP recordset appears on the Bindings panel.


To create an ASP or PHP recordset from the Server Behaviors panel:

1.

On the Application panel, click the Server Behaviors tab to access the Server Behaviors panel.

2.

Click the plus (+) button, and from the contextual menu, select Recordset (Query) (Figure 6.9).

Figure 6.9. To open the Recordset dialog, on the Server Behaviors panel, click the plus button and choose Recordset (Query).


The Recordset dialog appears.

3.

Follow Steps 4 through 9 of the previous task to create a recordset.

Tips

  • You can see the ASP (or PHP) code generated by Dreamweaver to create the recordset in Code view (Figure 6.10).

    Figure 6.10. In Code view, you can see the ASP code that creates the Locations recordset.

  • If you are using PHP with Apache on Windows, you must turn on the Apache service and the MySQL service before you can use Dreamweaver to access a PHP connection to a MySQL database. If you're using PHP on Mac OS X, you must turn on the MySQL Monitor before you can use Dreamweaver to access a PHP connection to a MySQL database. See Chapter 5 for more details on using the MySQL Monitor on a Mac.


To create a ColdFusion recordset:

1.

Open a ColdFusion page in Dreamweaver.

You need to have a ColdFusion page open in the Document window to use the Databases panel and the Bindings panel. You won't be making any changes to the page itself, so it doesn't matter if it's an existing page or a new one. If you don't already have a ColdFusion page, choose File > New > Dynamic Page > ColdFusion to create one.

2.

In the Application panel group, click the Databases tab to access the Databases panel (Figure 6.11).

Figure 6.11. Click the Databases tab in the Application panel group to access the ColdFusion Databases panel.


3.

Click the RDS Login link.

The Login to ColdFusion Remote Development Services (RDS) password dialog appears (Figure 6.12).

Figure 6.12. You'll need to log in to RDS to create a recordset in ColdFusion.


4.

Enter your RDS password and click OK.

A list of your ColdFusion data sources appears on the Databases panel (Figure 6.13).

Figure 6.13. A list of ColdFusion data sources displays on the Databases panel.


ColdFusion uses the term data source instead of database.

5.

Click the Bindings tab to open the Bindings panel. Click the plus button, and from the contextual menu select Recordset (Figure 6.14).

Figure 6.14. On the Bindings panel, click the plus button and choose Recordset (Query) to open the Recordset dialog for ColdFusion.


The Recordset dialog appears.

6.

In the Name field, enter a name for the recordset (Figure 6.15). Choose a name that will help you to identify the recordset later. For this exercise, we're using the trips table from the sample Compass Travel database included with ColdFusion MX 7 (CompassTravel.mdb), so we're naming our recordset trips.

Figure 6.15. Create a ColdFusion recordset by using the Recordset dialog.


The Compass Travel database includes several tables containing information about the employees and about the trips offered by Compass Travel. The trips table includes fields with data about the trips, including trip ID, name, type, description, location, departure and return dates, price, leader, photo, base cost, number of people, and deposit.

(We already created the Compass Travel data source for ColdFusion in Chapter 4. See "To connect to a data source using ColdFusion" in Chapter 4 if you haven't done this.)

7.

From the Data Source drop-down list, choose CompassTravel.

8.

In the User Name and Password fields, enter a user name and password if required by your database administrator. Otherwise, leave these two fields blank.

9.

From the Table drop-down list, choose trips.

10.

In the Columns section, choose the Selected radio button, and then select the tripName, eventType, tripDescription, and tripLocation fields that appear in the box below the radio button.

11.

Click the Test button to see the contents of the recordset.

The Test SQL Statement window appears, listing the contents of the tripName, event-Type, tripDescription, and tripLocation fields (Figure 6.16). Click OK to return to the Recordset dialog.

Figure 6.16. View the entire ColdFusion recordset in the Test SQL Statement window.


12.

Click OK in the Recordset dialog to save the recordset and close the dialog.

The recordset name appears on the Bindings panel (Figure 6.17)

Figure 6.17. The name of the new ColdFusion recordset appears on the Bindings panel.


Tips

  • If you have difficulty connecting to the ColdFusion server, make sure that the appropriate services are started on your computer. If you're running ColdFusion with Apache, choose Start > Control Panel > Administrative Tools > Services, and check that the Apache service is started. If you have both Apache and IIS on your computer, you will need to stop the Apache service when you want to use ColdFusion with IIS. If you're using IIS, you also need to make sure that the Default Web Site is running in the Internet Information Services window (Start > Control Panel > Administrative Tools > Internet Information Services). For more details, see "To verify IIS installation status" in Chapter 1.

  • You can see in Code view the ColdFusion markup code generated by Dreamweaver to create the recordset (Figure 6.18).

    Figure 6.18. In Code view, you can see the ColdFusion code that creates the trips recordset.

  • The ColdFusion Recordset dialog includes a CFC Query button, which can be used to create a ColdFusion component (CFC). A CFC is a reuseable block of custom ColdFusion code. For more information on ColdFusion components, see "Using ColdFusion" (Help > Using ColdFusion).


To filter data in a ColdFusion recordset:

1.

If you haven't already created the trips recordset, follow Steps 1 through 10 of the previous task.

We'll add a filter to this recordset so that it includes only those trips that have a value of 1 for eventType. EventType 1 corresponds to the Surfing field in the eventtypes table of the CompassTravel database.

2.

In the Recordset dialog, choose eventType from the Filter drop-down list, which includes the name of every field in the trips table. To make eventType equal to a certain value, choose the equals sign (=) from the drop-down list on the right (Figure 6.19).

Figure 6.19. Start creating the filter by selecting eventType from the first menu in the Filters section, and the equals sign from the menu on the right.


We're specifying which field should be used (eventType) for the filter and how the data in the field should be evaluated.

3.

From the drop-down list in the lower left of the Filter section, choose Entered Value (Figure 6.20). In the field on the right, enter 1.

Figure 6.20. Complete the filter by selecting Entered Value from the drop-down list in the lower left of the Filter section, and then enter 1 in the text field to the right.


Entered value indicates a static value. In our case, we're specifying that we want to include records in which the value for eventType is equal to 1. The other options in the list use data that's obtained dynamically from the server. (For information on URL parameters and form variables, see Chapter 8. For more details on using cookies, see Dreamweaver Help).

The filter is now complete.

4.

Click the Test button to see the contents of the recordset.

The Test SQL Statement window appears and displays the contents of the recordset (Figure 6.21). In our case, the tripName, tripDescription, and tripLocation fields of all travel packages that pertain to surfing are displayed.

Figure 6.21. View the filtered recordset in the Test SQL Statement window.


5.

Click OK to return to the Recordset dialog.

6.

Click OK to save the recordset and close the Recordset dialog.

The trips recordset data is now filtered and includes only a subset of the trips recordset (only the records for surfing trips).

Tips

  • The process for adding a filter to an ASP or PHP recordset is the same as it is for a ColdFusion recordset.

  • If you want to view the SQL statement that creates the recordset and the filter, open the Recordset dialog and click the Advanced button. The SQL statement appears in the SQL field in this dialog (Figure 6.22).

    Figure 6.22. You can view the SQL statement that creates the recordset and the filter in the advanced Recordset dialog.


To sort data in an ASP recordset:

1.

On the Bindings panel, double-click the locations recordset you created in "To create an ASP or PHP recordset from the Bindings panel," earlier in this chapter.

The Recordset dialog appears (Figure 6.23). We're going to sort the Locations recordset data alphabetically by the value in each record for LOCATION_NAME.

Figure 6.23. Open an existing recordset to add a sort feature.


2.

In the Sort section, choose LOCATION_NAME from the first drop-down list and Ascending from the second drop-down list (Figure 6.24).

Figure 6.24. In the Sort section, choose a field name and a sort order.


3.

Click the Test button.

The Test SQL Statement window appears and displays a list of airports sorted alphabetically by their location name (Figure 6.25).

Figure 6.25. View the results of the sort in the Test SQL Statement window.


4.

Click OK to close the Test SQL Statement window, and then click OK in the Recordset dialog to save the new query and close the dialog.

Tip

  • To sort data in a ColdFusion or PHP recordset, follow the same procedure as for ASP in this task.





Macromedia Dreamweaver 8 Advanced for Windows and Macintosh. Visual Quickpro Guide
Macromedia Dreamweaver 8 Advanced for Windows and Macintosh: Visual QuickPro Guide
ISBN: 0321384024
EAN: 2147483647
Year: 2004
Pages: 129
Authors: Lucinda Dykes

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net