Building Truly Dynamic Statements
| No sooner do you roll out your movie search screen at Orange Whip Studios, but you immediately find yourself inundated with requests. "Searching by title is great, but what about searching by tag line or rating?" your users ask. Now that you have introduced the ability to search for data, your users want to be able to search on several fields. Adding fields to your search screen is simple enough. Add two fields: one for tag line and one for rating. The code for the updated search screen is shown in Listing 12.15. Listing 12.15. search2.cfmMovie Search Screen<!--- Name: search2.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="results2.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> <input type="text" name="RatingID"> (1-6) </td> </tr> <tr> <td colspan="2" align="center"> <input type="submit" value="Search"> </td> </tr> </table> </form> </body> </html> This form lets users specify text in one of three fields, as shown in Figure 12.14. Figure 12.14. The movie search screen now allows searching by three fields. You must create a search template before you can actually perform a search. The complete search code is shown in Listing 12.16; save this file as results2.cfm. Listing 12.16. results2.cfmBuilding SQL Statements Dynamically<!--- Name: results2.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 <!--- Search by movie title ---> <cfif FORM.MovieTitle IS NOT ""> WHERE MovieTitle LIKE '%#FORM.MovieTitle#%' </cfif> <!--- Search by tag line ---> <cfif FORM.PitchText IS NOT ""> WHERE PitchText LIKE '%#FORM.PitchText#%' </cfif> <!--- Search by rating ---> <cfif FORM.RatingID IS NOT ""> WHERE RatingID = #FORM.RatingID# </cfif> 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> <cfoutput> <th colspan="2"> <font size="+3">Movie List (#Movies.RecordCount# movies)</font> </TH> </cfoutput> </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> Understanding Dynamic SQLBefore you actually perform a search, take a closer look at the template in Listing 12.16. The <cfquery> tag is similar to the one you used in the previous search template, but in this one the SQL SELECT statement in the SQL attribute is incomplete. It doesn't specify a WHERE clause with which to perform a search, nor does it specify a search order. No WHERE clause is specified because the search screen has to support not one, but four search types, as follows:
How can a single search template handle all these search conditions? The answer is dynamic SQL. When you're creating dynamic SQL statements, you break the statement into separate common SQL and specific SQL. The common SQL is the part of the SQL statement you always want. The sample SQL statement has two common parts: SELECT MovieTitle, PitchText, Summary, DateInTheaters FROM Films and ORDER BY MovieTitle The common text is all the SQL statement you need if no search criteria is provided. If, however, search text is specified, the number of possible WHERE clauses is endless. Take another look at Listing 12.16 to understand the process of creating dynamic SQL statements. The code <cfif FORM.MovieTitle IS NOT ""> checks to see that the MovieTitle form field isn't empty. This condition fails if no text is entered into the MovieTitle field in the search form, in which case any code until the </CFIF> is ignored.
If a value does appear in the MovieTitle field, the code WHERE MovieTitle LIKE '#FORM.Movie Title#%' is processed and appended to the SQL statement. #FORM.MovieTitle# is a field and is replaced with whatever text is entered in the MovieTitle field. If the is specified as the text for which to search, this statement translates to WHERE MovieTitle LIKE '%the%'. This text is appended to the previous SQL statement, which now becomes the following: SELECT MovieTitle, PitchText, Summary, DateInTheaters FROM Films WHERE MovieTitle LIKE '%the%' All you need now is the ORDER BY clause. Even though ORDER BY is fixed and doesn't change with different searches, it must be built dynamically because the ORDER BY clause must come after the WHERE clause, if one exists. After ColdFusion processes the code ORDER BY MovieTitle, the finished SQL statement reads as follows: SELECT MovieTitle, PitchText, Summary, DateInTheaters FROM Films WHERE MovieTitle LIKE '%the%' ORDER BY MovieTitle NOTE You may not use double quotation marks in a SQL statement. When ColdFusion encounters a double quotation mark, it thinks it has reached the end of the SQL statement. It then generates an error message because extra text appears where ColdFusion thinks there should be none. To include text strings with the SQL statement, use only single quotation marks. Similarly, if a RatingID is specified (for example, the value 2) as the search text, the complete SQL statement reads as follows: SELECT MovieTitle, PitchText, Summary, DateInTheaters FROM Films WHERE RatingID = 2 ORDER BY MovieTitle The code <cfif FORM.MovieTitle IS NOT ""> evaluates to FALSE because FORM.MovieTitle is actually empty; ColdFusion therefore checks the next condition, which is also FALSE, and so on. Because RatingID was specified, the third <CFIF> condition is trUE and the previous SELECT statement is generated. NOTE You may have noticed that there are single quotation marks around FORM.MovieTitle and FORM.PitchText but not FORM.RatingID. Why? Because MovieTitle and PitchText have text data types in the database table, whereas RatingID is numeric. SQL is not typeless, and it will require that you specify quotes where needed to create strings if that is what is expected. So, one template is capable of generating four different sets of SQL SELECT statements, of which the values can be dynamic. Try performing various searches, but for now, use only one form field at a time. Concatenating SQL ClausesNow try entering text in two search fields, or all three of them. What happens? You probably generated an error like the one in Figure 12.15. Figure 12.15. Dynamic SQL must be generated carefully to avoid building invalid SQL. Why did this happen? Well, suppose the was specified as the MovieTitle and 2 as the RatingID. Walk through the <cfif> statements to work out what the generated SQL would look like. The first condition will be TRUE, the second will be FALSE, and the third will be trUE. The SELECT statement would therefore look like this: SELECT MovieTitle, PitchText, Summary, DateInTheaters FROM Films WHERE MovieTitle LIKE '%the%' WHERE RatingID = 2 ORDER BY MovieTitle Obviously, this is not a valid SELECT statementonly one WHERE clause is allowed. The correct syntax for this statement is SELECT MovieTitle, PitchText, Summary, DateInTheaters FROM Films WHERE MovieTitle LIKE '%the%' AND RatingID = 2 ORDER BY MovieTitle So how would you generate this code? You couldn't hard-code any condition with a WHERE or an AND, because you wouldn't know whether it was the first clause. The MovieTitle clause, if used, will always be the first, but it might not always be used. One obvious solution (which I suggest you avoid at all costs) is to use embedded <cfif> statements to intelligently include WHERE or AND as necessary. However, this type of code is very complex and error prone. A better solution would be to never need WHERE at allonly use AND. How can you do this? Look at the following SQL statement: SELECT MovieTitle, PitchText, Summary, DateInTheaters FROM Films WHERE 0=0 AND MovieTitle LIKE '%the%' AND RatingID = 2 ORDER BY MovieTitle WHERE 0=0 is a dummy clause. Obviously 0 is equal to 0, so WHERE 0=0 retrieves every row in the table. For each row the database checks to see whether 0 is 0, which of course it always is. This is a legal WHERE clause, but it does nothing because it is always trUE. So why use it? Simple. Now that there is a WHERE clause, you can safely use AND for every dynamic condition. If no other condition exists, then only the WHERE 0=0 will be evaluated. But if additional conditions do exist, no matter how many, they can all be appended using AND. NOTE There is nothing magical about WHERE 0=0. You can use any condition that will always be trUE: WHERE 'A'='A', WHERE primary key = primary key (using the table's primary key), and just about anything else you want. Listing 12.17 contains a revised search page (this time using a drop-down list box for the rating); save it as search3.cfm. Figure 12.16 shows the new and improved search screen. Figure 12.16. Drop-down list boxes are well suited for selections of one of a set of finite options. Listing 12.18 contains the revised results page; save it as results3.cfm. Listing 12.17. search3.cfmRevised Movie Search Screen<!--- Name: search3.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="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> <option value="1">General</option> <option value="2">Kids</option> <option value="3">Accompanied Minors</option> <option value="4">Teens</option> <option value="5">Adults</option> <option value="6">Mature Audiences</option> </select> </td> </tr> <tr> <td colspan="2" align="center"> <input type="submit" value="Search"> </td> </tr> </table> </form> </body> </html> The only change in Listing 12.17 is the drop-down list box for the RatingID. Manually entering 1 to 6 isn't intuitive, and is highly error prone. For finite lists such as this drop-down list, boxes are a better option. This doesn't change the form field processing, though. Either way, RatingID is sent to the action page, shown in Listing 12.18. Listing 12.18. results3.cfmConcatenating SQL Clauses<!--- Name: results3.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 0=0 <!--- Search by movie title ---> <cfif FORM.MovieTitle IS NOT ""> AND MovieTitle LIKE '%#FORM.MovieTitle#%' </cfif> <!--- Search by tag line ---> <cfif FORM.PitchText IS NOT ""> AND PitchText LIKE '%#FORM.PitchText#%' </cfif> <!--- Search by rating ---> <cfif FORM.RatingID IS NOT ""> AND RatingID = #FORM.RatingID# </cfif> 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 <cfquery> in Listing 12.18 now contains a dummy clause and then three optional AND clauses, each within a <cfif> statement. So what will this do?
In other words, a single template can now generate eight different combinations of WHERE clauses, and each can have an unlimited number of values. All that in less than 20 lines of codeit doesn't get much more powerful than that. After you create the template, use your browser to perform various combinations of searches. You'll find that this new search template is both powerful and flexible. Indeed, this technique for creating truly dynamic SQL SELECT statements will likely be the basis for some sophisticated database interaction in real-world applications. TIP Debugging dynamic SQL statement creation can be tricky, and troubleshooting requires that you know exactly what SQL your ColdFusion code created. To do this, use the techniques described in Chapter 10 (debug output and obtaining a result from <cfquery>). |
See Chapter 9 for details on using <CFIF>.
EAN: N/A
Pages: 282