Creating Dynamic Search Screens

There is one final improvement to be made to your application. The list of ratings used in the search form has been hard-coded (refer to Listing 12.17). Remember that you're creating data-driven applications. Everything in your application should be data-driven. You don't want to have to manually enter data, not even in list boxes. Rather, you want the list box to be driven by the data in the FilmsRatings table. This way, you can acquire changes automatically when ratings are added or when a rating name changes.

Listing 12.19 is identical to Listing 12.17, with the exception of the addition of a new <cfquery> and a <cfoutput> block to process its contents.

Listing 12.19. `search4.cfm`Data-Driven Forms

 <!--- Name:        search4.cfm Author:      Ben Forta (ben@forta.com) Description: Creating search screens Created:     12/20/04 ---> <!--- Get ratings ---> <cfquery datasource="ows" name="ratings"> SELECT RatingID, Rating FROM FilmsRatings ORDER BY RatingID </cfquery> <html> <head>  <title>Orange Whip Studios - Movies</title> </head> <body> <!--- Page header ---> <cfinclude template="header.cfm"> <!--- Search form ---> <form action="results3.cfm" method="POST"> <table align="center" border="1">  <tr>   <td>   Movie:   </td>   <td>   <input type="text" name="MovieTitle">   </td>  </tr>  <tr>   <td>   Tag line:   </td>   <td>   <input type="text" name="PitchText">   </td>  </tr>  <tr>   <td>   Rating:   </td>   <td>   <select name="RatingID">    <option value=""></option>    <cfoutput query="ratings">     <option value="#RatingID#">#Rating#</option>    </cfoutput>   </select>   </td>  </tr>  <tr>   <td colspan="2" align="center">   <input type="submit" value="Search">   </td>  </tr> </table> </form> </body> </html>

The code in Listing 12.19 demonstrates a data-driven form. The <cfquery> at the top of the template should be familiar to you by now. It creates a result set called ratings, which contains the ID and name of each rating in the database.

The drop-down list box also has been changed. The <select> tag creates the list box, and it is terminated with the </select> tag, as before. The individual entries in the list box are specified with the <option> tag, but here that tag is within a <cfoutput> block. This block is executed once for each row retrieved by the <cfquery>, creating an <OPTION> entry for each one.

As it loops through the ratings resultset, the <cfquery> block creates the individual options, using the RatingID field as the value and Rating as the description. So when ColdFusion processes RatingID 1 (General), the code generated is:

 <option value="1">General</option>

The end result is exactly the same as the screen shown previously in Figure 12.16, but this time it is populated by a database query (instead of being hard-coded).

Also notice that a blank <option> line is included in the list box. Remember that list boxes always must have a selection, so if you want to allow your users to not select any option, you need to give them a no option option (the blank option).

And there you have it: dynamic data-driven forms used to perform dynamic data-driven searches using dynamic data-driven SQL.

Listing 12.19. search4.cfmData-Driven Forms

Listing 12.19. `search4.cfm`Data-Driven Forms