Creating Dynamic SQL Statements
| NOTE This section uses <cfquery> tags for data access, and the example here should use ColdFusion Components as was described in the last chapter. However, to keep the examples simpler I will violate the rules I just taught you. I guess I'm saying that every rule has exceptions. Now that you're familiar with forms and how ColdFusion processes them, you can return to creating a movie search screen. The first screen enables visitors to search for a movie by title. Because this requires text input, you will need an <input> field of type text. The field name can be anything you want, but using the same name as the table column to which you're comparing the value is generally a good idea. TIP When you're creating search screens, you can give your form fields any descriptive name you want. When you're creating insert and update forms, however, the field name must match the table column names so ColdFusion knows which field to save with each column. For this reason, you should get into the habit of always naming form fields with the appropriate table column name. The code in Listing 12.13 contains a simple HTML form not unlike the test forms you created earlier in this chapter. The form contains a single text field called MovieTitle and a submit button. Listing 12.13. search1.cfmCode Listing for Movie Search Screen<!--- Name: search1.cfm Author: Ben Forta (ben@forta.com) Description: Creating search screens Created: 12/20/04 ---> <html> <head> <title>Orange Whip Studios - Movies</title> </head> <body> <!--- Page header ---> <cfinclude template="header.cfm"> <!--- Search form ---> <form action="results1.cfm" method="POST"> <table align="center" border="1"> <tr> <td> Movie: </td> <td> <input type="text" name="MovieTitle"> </td> </tr> <tr> <td colspan="2" align="center"> <input type="submit" value="Search"> </td> </tr> </table> </form> </body> </html> Save this form as search1.cfm, then execute it to display a screen like the one in Figure 12.12. Figure 12.12. The movie search screen enables users to search by movie title. Listing 12.13 starts off with a comment block, followed by the standard HTML headers and <body> tag. Then a <cfinclude> tag is used to include a common header, file header.cfm (which puts the logo and title at the top of the page).
The form itself is placed inside an HTML table. This is a very popular technique that can be used to better control form field placement. The form contains a single field, MovieTitle, and a submit button. The <form> action attribute specifies which ColdFusion template should be used to process this search. The code action="results1.cfm" instructs ColdFusion to use the template results1.cfm, which is shown in Listing 12.14. Create this template and save it as results1.cfm. Listing 12.14. results1.cfmPassed Form Field in a SQL WHERE Clause<!--- Name: results1.cfm Author: Ben Forta (ben@forta.com) Description: Creating search screens Created: 12/20/04 ---> <!--- Get movie list from database ---> <cfquery name="movies" datasource="ows"> SELECT MovieTitle, PitchText, Summary, DateInTheaters FROM Films WHERE MovieTitle LIKE '%#FORM.MovieTitle#%' ORDER BY MovieTitle </cfquery> <!--- Create HTML page ---> <html> <head> <title>Orange Whip Studios - Movies</title> </head> <body> <!--- Page header ---> <cfinclude template="header.cfm"> <!--- Display movie list ---> <table> <tr> <th colspan="2"> <cfoutput> <font size="+3">Movie List (#Movies.RecordCount# movies)</font> </cfoutput> </th> </tr> <cfoutput query="movies"> <tr> <td> <font size="+2"><strong>#CurrentRow#: #MovieTitle#</strong></font><br> <font size="+1"><em>#PitchText#</em></font> </td> <td>Released: #DateFormat(DateInTheaters)#</td> </tr> <tr> <td colspan="2">#Summary#</td> </tr> </cfoutput> </table> </body> </html> The code in Listing 12.14 is based on the movie lists created in the last chapter, so most of the code should be very familiar. The only big change here is in the <cfquery> tag. The WHERE clause in Listing 12.14 contains a ColdFusion field rather than a static value. You will recall that when ColdFusion parses templates, it replaces field names with the values contained within the field. So, look at the following WHERE clause: WHERE MovieTitle LIKE '%#FORM.MovieTitle#%' #FORM.MovieTitle# is replaced with whatever was entered in the MovieTitle form field. If the word her was entered then the WHERE clause becomes WHERE MovieTitle LIKE '%her%' which will find all movies with the text her anywhere in the MovieTitle. If you search for all movies containing C, the code WHERE MovieTitle LIKE '%#FORM.MovieTitle#%' would become WHERE MovieTitle LIKE '%C%', and so on. You can do this with any clauses, not just the LIKE operator. NOTE If no search text is specified at all, the clause becomes WHERE MovieTitle LIKE '%%'a wildcard search that finds all records.
You use a LIKE clause to enable users to enter partial text. The clause WHERE MovieTitle = 'her' finds only movies with a title of her; movies with her in the name along with other text are not retrieved. Using a wildcard, as in WHERE MovieTitle LIKE '%her%', enables users to also search on partial names. Try experimenting with different search strings. The sample output should look like the output shown in Figure 12.13. Depending on the search criteria you specify, you'll see different search results, of course. Figure 12.13. By building WHERE clauses dynamically, you can create different search conditions on the fly. To complete the application, try copying the movie detail page (created in Chapter 10) and modify results1.cfm so that it enables the drill-down of the displayed search results. You'll then have a complete drill-down application. |
See Chapter 9 for information on using the <cfinclude> tag.
EAN: N/A
Pages: 282