User manual MACROMEDIA COLDFUSION MX 61-GETTING STARTED BUILDING COLDFUSION MX APPLICATIONS

[. . . ] Getting Started Building ColdFusion MX Applications Trademarks Afterburner, AppletAce, Attain, Attain Enterprise Learning System, Attain Essentials, Attain Objects for Dreamweaver, Authorware, Authorware Attain, Authorware Interactive Studio, Authorware Star, Authorware Synergy, Backstage, Backstage Designer, Backstage Desktop Studio, Backstage Enterprise Studio, Backstage Internet Studio, ColdFusion, Design in Motion, Director, Director Multimedia Studio, Doc Around the Clock, Dreamweaver, Dreamweaver Attain, Drumbeat, Drumbeat 2000, Extreme 3D, Fireworks, Flash, Fontographer, FreeHand, FreeHand Graphics Studio, Generator, Generator Developer's Studio, Generator Dynamic Graphics Server, JRun, Knowledge Objects, Knowledge Stream, Knowledge Track, Lingo, Live Effects, Macromedia, Macromedia M Logo & Design, Macromedia Flash, Macromedia Xres, Macromind, Macromind Action, MAGIC, Mediamaker, Object Authoring, Power Applets, Priority Access, Roundtrip HTML, Scriptlets, SoundEdit, ShockRave, Shockmachine, Shockwave, Shockwave Remote, Shockwave Internet Studio, Showcase, Tools to Power Your Ideas, Universal Media, Virtuoso, Web Design 101, Whirlwind and Xtra are trademarks of Macromedia, Inc. and may be registered in the United States or in other jurisdictions including internationally. Other product names, logos, designs, titles, words or phrases mentioned within this publication may be trademarks, servicemarks, or tradenames of Macromedia, Inc. or other entities and may be registered in certain jurisdictions including internationally. [. . . ] The search action page uses a SQL SELECT statement to display an HTML table with the results of the user query using the cfoutput block. Building the WHERE Clause with the cfif and cfset The WHERE clause in a SQL SELECT is a string. You use the CFML cfset and cfif tags to conditionally build the WHERE clause depending on values passed to the search action page. The cfset statement creates a new variable or changes the value of an existing variable. For example, to create a variable named color and initialize its value to red, you use the following statement: <cfset color = "red"> The cfif tag instructs the program to branch to different parts of the code depending on whether a test evaluates to True or False. For example, to have some code execute if the color variable is equal to red, and other code execute if it is not, you use the following pseudocode: <cfif color EQ "red"> . . . statements for other than red </cfif> Building a SQL WHERE clause in code is largely an exercise in string concatenation. For example, the following code snippet: <cfset FirstName = "Dylan"> <cfset LastName = "Smith"> <cfset FullName = FirstName & " " & LastName> <cfoutput>My name is #FullName#. </cfoutput> results in the following text: My name is Dylan Smith. 70 Chapter 6: Lesson 2: Writing Your First ColdFusion Application For each search criterion on the Trip Search form, the code within the Trip Search Results page must do the following: · Verify that the user entered data in the search criterion's value field using the cfif tag; for example <cfif Form. tripLocationValue GT ""> · If data was entered, construct a WHERE subclause by concatenating the following: The SQL keyword AND The corresponding SQL column name (in the Trip Search example, tripLocation) for the search criterion. The SQL operator equivalent of the search query operator The test value entered by the user The following code shows the creation of the WHERE subclause: <cfif Form. tripLocationOperator EQ "EQUALS"> <cfset WhereClause = WhereClause & " AND tripLocation = '" & form. tripLocationValue & "'" > <cfelse> <cfset WhereClause = WhereClause & " AND tripLocation like '" & form. tripLocationValue & "%'" > </cfif> When you test for a string column within the WHERE clause of the SQL SELECT statement, you must enclose the test value in quotation marks. When you use a variable to construct a WHERE clause you must preserve the quotation marks so that the database server does not return an error. To preserve the quotation marks, you must use the ColdFusion PreserveSingleQuotes function. The PreserveSingleQuotes function prevents ColdFusion from automatically escaping single quotation marks contained in the variable string passed to the function. Note: The cfqueryparam tag also escapes single quotation marks. For more information, see CFML Reference. Constructing the initial Trip Search Results page The following code shows how to construct the tripLocation SQL WHERE subclause. Specifically, it uses a dynamic SQL SELECT statement built from parameters from the Trip Search page to display the search results. <!--- Create Where clause for query from data entered thru search form ---> <cfset WhereClause = " 0=0 "> <!--- Build subclause for trip location ---> <cfif Form. tripLocationValue GT ""> <cfif Form. tripLocationOperator EQ "EQUALS"> <cfset WhereClause = WhereClause & " and tripLocation = '" & form. tripLocationValue & "'" > <cfelse> <cfset WhereClause = WhereClause & " and tripLocation like '" & form. tripLocationValue & "%'" > </cfif> </cfif> <!--- Query returning search results ---> <cfquery name="TripResult" datasource="compasstravel"> SELECT tripName, tripLocation, departureDate, returnDate, price, tripID FROM trips WHERE #PreserveSingleQuotes(WhereClause)# Developing a search capability 71 </cfquery> <html> <head> <title>Trip Maintenance - Search Results</title> </head> <body> <img src="images/tripsearchresults. gif"> <table border="0" cellpadding="3" cellspacing="0"> <tr bgcolor="Gray"> <td>Trip Name </td> <td>Location </td> <td>Departure Date </td> <td>Return Date </td> <td>Price </td> </tr> <cfoutput query="TripResult"> <tr> <td>#tripName# </td> <td>#tripLocation# </td> <td>#departureDate# </td> <td>#returnDate# </td> <td>#price# </td> </tr> </cfoutput> </table> </body Reviewing the code The following table describes the code used to build the tripLocation WHERE subclause: Code <cfset WhereClause = " 0=0 "> Explanation The cfset tag initializes the WhereClause variable to hold the WHERE clause to be constructed. The initial value is set to "0=0", so that the WHERE clause has at least one subclause in case the user enters no search criteria. The cfif tag tests to see if user entered anything in the Value input box for tripLocation criterion. PreserveSingleQuotes ColdFusion function ensures that quotation marks will passed to the database server as intended. <cfif Form. tripLocationValue GT ""> SELECT tripName, tripLocation, departureDate, returnDate, price, tripID FROM trips WHERE #PreserveSingleQuotes(WhereClause)# Note that the preceding code only builds the tripLocation subclause. In the following exercise you will add code for the other two queryable columns, departureDate and price. 72 Chapter 6: Lesson 2: Writing Your First ColdFusion Application Completing the Trip Search Results page In the following exercises you will test and modify tripsearchresults. cfm. In the first exercise, you will test the Trip Search Results page by entering criteria on the Trip Search form and inspecting the results. In the second exercise, you will finish the code to construct the complete WHERE clause for all three queryable columns from the Trip Search form. Exercise: testing the Trip Search Results page Follow these steps to test the Trip Search Results page: 1 Copy the tripsearch. cfm and tripsearchresult. cfm files from the solutions directory to the my_app directory. 2 View the tripsearch. cfm from the my_app directory in your browser and do the following: a In the Trip Location drop-down list box select the Begins With option, and enter the value C in the text box. The Trip Results page displays several entries, as follows: c Notice in the Trip Results page that only one trip has a trip location of China. [. . . ] </cfif> Exercise: linking the Add and Edit buttons In this exercise you will link the Add and Edit buttons on the Trip Detail page with the Trip Edit page. To link the add and update buttons on the Trip Detail page: 1 Open maintenanceaction. cfm in the my_app directory in your editor. 3 Insert the following code just before the last line: <!--- EDIT BUTTON ---> <cfelseif IsDefined("Form. btnEdit")> <cflocation url="tripedit. cfm?ID=#Form. RecordID#"> <!--- ADD BUTTON ---> <cfelseif IsDefined("Form. btnAdd")> <cflocation url="tripedit. cfm"> 4 Save maintenanceaction. cfm. 7 Copy the contents of the initvariables. txt and paste it before the <HTML> tag in the tripedit4. cfm page. 8 Save the file as tripedit. cfm in the my_app directory. Completing the Trip Maintenance application 127 9 Test update logic by opening the tripdetail. cfm page in your browser and doing the following tasks: a Click the Edit button. [. . . ]