Searching by Country: Filtering by Form Variable

Open tours.asp in design view, position the insertion point in the second row of the right-hand column, and insert a form with one list/menu element and a Submit button.

You should at this point be comfortable mocking up form elements on the page.

Select the <form> tag in the tag selector, and use the Property inspector to name the form frm_bycountry. Specify tour_detail.asp as the action. Specify POST as the method.

These settings apply to the form as a whole, and it is now ready to use. Of course, you still have to configure its elements, most notably, the menu element.

Use the Bindings panel to create a new recordset, using the following settings:

Name: rs_countries

Connection: conn_newland

Table: tbl_country

Columns: Selected, countryName

Filter: None

Sort: countryName, Ascending

If you press the Test button, you'll see that this recordset produces a list of all the countries in tbl_country in alphabetical order. You'll use this to build the drop-down menu in the form.

Click to select the menu, and in the Property inspector, name it tourCountry. Click the Dynamic button to open the Dynamic List/Menu dialog. Select rs_countries from the Options From Recordset menu. Leave the Values and Labels at the default, countryName, and click OK.

You've done this before, so the consequence of these steps should not be a mystery. When the page is displayed in a browser, the country names will populate the menu in alphabetical order. When the user clicks Submit, tour_detail.asp will load (that was specified in the form's action attribute), and the name-value pair tourCountry=Argentina (or whichever country the user selected) will be sent as a form variable.

If you like, you can save and test the page in a browser, just to make sure that the menu is actually populated with country names. Don't press Submit, though, or you will get an error, because tour_detail.asp is expecting a URL variable called regionid, not a form variable called countryName.

Open tour_detail.asp in code view. Find the two queries (one should be commented out). Copy the second query and paste it on the next line. Comment out the second query.

Once again, you are making it possible to revise and test the new SQL statement, the one that will process the data submitted by the form.

Change only the WHERE clause in the statement so that it reads as follows:

ASP users:

WHERE tbl_country.countryName='"& Request.Form("tourCountry") & "'

ColdFusion users:

WHERE tbl_country.countryName='#form.tourCountry#'

For the most part, this version of the query should make sense to you. You are matching the country name specified in the form with any tour that is associated with the same country name (though once again, you must go through the relationship between tbl_tours and tbl_country to access this name).

The only mysterious thing here may be the single quote (') that surrounds the dynamic content in both ASP and ColdFusion. This is a SQL issue. Any time you are specifying a text string (as opposed to an expression or a number), it must be put in single quotes. Otherwise, the database attempts to interpret the text as if it's a function or some other special database command. By putting it in single quotes, you are telling the database not to interpret, but to match it with text stored in the database.

As before, make sure there is a space between the closing single quote (') and the next clause, which begins with ORDER BY.

Save and upload the file. Select tours.asp in the Site panel, and press F12 to test it. Select a country from the menu, and press Submit.

Most countries have only one tour, so you should see that tour listed when you choose a country. If you choose a country with multiple tours (France, Japan, United Kingdom, United States), you should see all of its tours.

If you choose Taiwan, you'll see a blank page. The reason for this is that while Taiwan exists in tbl_country, it has no tours associated with it. In an ideal world, this discrepancy would never happen. However, it is possible that a tour is planned to Taiwan that is still in the works. The person responsible for creating the country profile has already inserted this information into the database, but the tour description has not yet been finalized. You can account for this situation by retrieving only those countries that have tours associated with them in the query that populates the menu in tours.asp.

Open tours.asp. In the Bindings panel, double-click Recordset (rs_countries) to edit it. Click the Advanced button in the Recordset dialog. Between the FROM tbl_country and ORDER BY countryName ASC, enter the following code:

WHERE EXISTS
(SELECT * FROM tbl_tours WHERE tbl_tours.country = tbl_country.countryID)

White space doesn't matter, so use line breaks as you like to make it more readable.

Let's talk about this SQL for a moment, because several things are going on. First, it uses a subquery, which is a query within a query, or more specifically here, a query used as a criterion within another query. The subquery is the part contained within the parentheses. That subquery looks for all the fields from tbl_tours where there is a match between tbl_tours and tbl_country. All of the countries in the database, except for Taiwan, fall into this category.

The EXISTS keyword is used to check for the existence of rows in a subquery result. The actual values are not retrieved, because it is simply checking for their existence. Because it finds that 15 countries exist in both locations (tbl_tours and tbl_country), it includes them in the main SELECT statement. Since Taiwan doesn't exist in both places, it is excluded.

Click the Test button to see the results. You should now see 15, rather than 16 countries listed. Taiwan is no longer in the group. If a tour to Taiwan is added to tbl_tours, then Taiwan will be listed in the menu once again, because it would meet the EXIST criterion.

Save and close tours.asp.