| A results page uses a recordset to gather the data requested from the search page. The recordset uses a filter based on the search parameters from the search page to find any database records that match the search criteria. In the first task in this section, you'll build a results page for the single-parameter search page you built earlier in this chapter. You'll create a dynamic page and define a recordset that uses a URL parameter as a filter. With a single search parameter, you can use the Recordset dialog, and Dreamweaver automatically creates the SQL code for the query. We'll use ASP, but the process is the same for PHP and ColdFusion. In the second task, you'll build a results page for the multiple-parameter search page you also built in the last section. You'll create a dynamic page and define a recordset using the advanced Recordset dialog so that you can include multiple URL parameters in the filter. Unlike what you do for a single parameter, however, using multiple parameters requires that you create the SQL code for this more complex query. We'll use ColdFusion for the second task, but the process is the same for ASP and PHP. If you haven't already created a connection to the sample databases, you need to do so before you start the tasks in this section. See the sidebar "Using the Macromedia Sample Databases," earlier in this chapter. To create a result recordset for one search parameter: 1. | Open a new ASP page in Dreamweaver by choosing File > New > Dynamic page > ASP JavaScript. Save the page as results_simple.asp.
| 2. | In the Application panel group, click the Bindings tab to access the Bindings panel.
| 3. | Click the plus (+) button, and from the menu, choose Recordset (Query) (Figure 11.13).
Figure 11.13. Create a new recordset by clicking the plus button on the Bindings panel and selecting Recordset (Query) from the menu. 
The Recordset dialog appears.
| 4. | In the Name field, enter a name for the recordset (Figure 11.14). Choose a name that will help you to identify the recordset later.
Figure 11.14. Use the Recordset dialog to define a recordset for the results of a search using a single search parameter. 
For this exercise, we're using the LOCATIONS table from the cafetownsend database, so we named our recordset locations.
| 5. | From the Connection drop-down list, select the name for your connection to the sample cafetownsend database. Our connection is named connTownsend.
If you're using ColdFusion, this field will be called Data Source instead of Connection.
| 6. | From the Table drop-down list, choose LOCATIONS.
| 7. | In the Columns section, choose the All radio button.
You're choosing all the fields in this table so that you can include them when you display the search results. In the next two steps, we'll set up the filter.
| 8. | In the Filter section, choose REGION_ID from the drop-down list (Figure 11.15). Then, from the drop-down list to the right, choose the equals sign (=).
Figure 11.15. Match the REGION_ID value from the LOCATIONS table with the ID from the search parameter on the search page to create a filter. 
These actions specify that the REGION_ID field will be the field used in the recordset and that it will be equal to a certain value.
| 9. | From the drop-down list at the lower left of the Filter section, choose URL Parameter. In the field to the right, enter ID.
These two actions specify that the value for the REGION_ID field is equal to the value of the URL parameter named ID from the search page. The filter is now complete.
| 10. | From the Sort drop-down list on the left, choose LOCATION_NAME. Then, from the drop-down list on the right, choose Ascending.
The records will be sorted in ascending order by the value for LOCATION_NAME.
| 11. | Click the Test button to see the contents of the recordset. Because the user has to select a region from the menu on the search page, you will need to enter a value in the Please Provide a Test Value dialog to test the recordset (Figure 11.16). Enter any value from 1 to 6 to correspond to values from the menu-item list on the search page. Click OK to close the dialog and display the Test SQL Statement dialog (Figure 11.17).
Figure 11.16. Enter a value in the Test Value field to test the recordset results based on a value from the search-page menu. 
Figure 11.17. The recordset for the test value is shown in the Test SQL Statement dialog. 
| 12. | Click OK to close the Test SQL Statement dialog. Click OK in the Recordset dialog to save the recordset and close the dialog. Save the page.
In "Displaying Search Results," later in this chapter, you'll complete the results page you've just created by inserting a dynamic table to display the search results.
In the next task, you'll use the advanced Recordset dialog to define a recordset for multiple search parameters.
|
To create a results recordset for multiple search parameters: 1. | Open a new ColdFusion page in Dreamweaver by choosing File > New > Dynamic page > ColdFusion. Save the page as results_advanced.cfm.
| 2. | Follow Steps 2 and 3 in the preceding task. In the Recordset dialog, click the Advanced tab to open the advanced Recordset dialog.
| 3. | In the Name field, enter a name for the recordset (Figure 11.18). Choose a name that will help you to identify the recordset later.
Figure 11.18. Use the advanced Recordset dialog to create a complex query involving multiple search parameters. 
For this exercise, we're using the event-types and trips tables from the Compass-Travel database, so we named our recordset eventTrips.
| 4. | From the Data Source drop-down list, select the name for your connection to the sample CompassTravel database. Our connection is named CompassTravel.
If you're using ASP or PHP, this field will be called Connection instead of Data Source.
| 5. | 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.
| 6. | In the Database Items section, click the plus button to the left of the Tables item to expand the Tables list, and then click the plus button to the left of the event-types and trips tables to expand them (Figure 11.19).
Figure 11.19. In the Database Items section, click the plus button next to a table name to expand the table and display the field names. 
| 7. | In the event-types table list, select the eventTypeID field, and then click the SELECT button to add it to the SQL statement.
| 8. | Repeat Step 7 to add the eventType field from the event-types table, and the tripName, eventType, tripDescription, tripLocation, and price fields from the trips table.
Two fields (eventTypeID and eventType) from the event-types table and five fields (tripName, eventType, tripDescription, tripLocation, and price) from the trips table are added to the recordset (Figure 11.20).
Figure 11.20. The SQL text area in the advanced Recordset dialog displays the SQL statement you create by selecting fields in the Database Items section and clicking the SELECT button. 
| 9. | To create the filters, enter the following code in the SQL section of the advanced Recordset dialog right after the SELECT and FROM clauses that you created in Steps 7 and 8 (Figure 11.21):
Figure 11.21. Type these WHERE clauses in the SQL text area to complete the SQL statement. 
WHERE event-types.eventTypeID = trips.eventType
AND trips.eventType = #URL.event#
AND trips.price <= #URL.Price#
The first part of the WHERE clause, event-types.eventTypeID = trips.eventType, establishes the relationship between the event-types table and the trips table. The second part, trips.eventType = #URL.event#, creates the first filter. This filter selects the eventType fields from the trips table that have a value equal to the value of the URL parameter named event. The URL parameter value is obtained from the menu on the search page. The final clause, trips.price <= #URL.Price#, adds a second filter to select only the trips that have a price less than or equal to the price entered in the Price text field on the search page.
 Tip
You can use more than two search parameters. Just insert a form object for each search parameter on the search page, and then include each search parameter as a URL parameter (if the form is submitted using the HTTP GET method) or as a form parameter (if the form data is submitted using the HTTP POST method) in the SQL statement in the advanced Recordset dialog. For more information on using URL and form parameters, see "Using URL and Form Parameters," in Chapter 8.
| 10. | Click the Test button in the advanced Recordset dialog.
Because the user selects an event from the menu on the search page and enters a value for maximum price, you'll need to enter a value in two Please Provide a Test Value dialogs to test the recordseta value for the event and a value for maximum price.
| 11. | In the first Please Provide a Test Value dialog, enter any value from 1 to 7 to correspond to values from the event menu on the search page. Click OK to close the dialog. The second dialog appears.
| 12. | In the second Please Provide a Test Value dialog, enter any value between 1000 and 6000, and then click OK to close the dialog.
The Test SQL Statement window appears, displaying the recordset that results from the values you entered for the two filters, in this case 1 and 4000 (Figure 11.22).
Figure 11.22. The recordset for an event value of 1 and a price value of 4000 is shown in the Test SQL Statement window. 
| 13. | Click OK to close the Test SQL Statement window, and then click OK in the Recordset dialog to save the recordset and close the dialog. Save the page.
|
You've created a complex recordset that uses the two parameters from the form on the search page to filter a recordset that includes fields from two different tables. In the next section, you'll complete this results page by inserting a dynamic table to display the search results. |
