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. [. . . ] There is one text control for each queryable column. <select name="tripLocationOperator"> <option value="EQUALS">is <option value="BEGINS_WITH">begins with </select> <input type="text" name="tripLocationValue"> Building the Search Results page Based on the queryable columns identified earlier, the SQL query to display the search results would look like this: SELECT tripName, FROM trips tripLocation, departureDate, returnDate, price, tripID The purpose of the Trip Search form is to supply the data needed to build the WHERE clause to finish this SQL SELECT statement and constrain the query according to the user's input. Developing a search capability 69 When the user enters the search criteria on the Trip Search form and clicks the Search button, the form fields are then posted to the Trip Search Results page. The posted field values compose the WHERE clause in the SQL SELECT statement. The following example lists the WHERE clauses that can be generated depending on the criteria set on the search page: WHERE WHERE WHERE AND AND tripLocation = 'China' tripLocation Like 'C%' tripLocation = 'China' departureDate > 1/1/2001 price < 1500 In the previous example, the SQL AND operator joins the search condition clauses. To simplify the trip search example, you will use the SQL AND operator to combine all the search condition clauses. A more sophisticated search criteria page might present the user a choice of using AND or OR to connect one search criterion with the others. 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 color red <cfelse> . . . statements for other than red </cfif> Building a SQL WHERE clause in code is largely an exercise in string concatenation. The & operator combines two strings in ColdFusion. 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. [. . . ] SQL Update The SQL UPDATE statement updates or changes rows in a relational table. The syntax of the UPDATE statement is as follows: UPDATE table_name SET column_name = new_value WHERE column_name = some_value Consider a database table named Clients that contains information about people in the following rows: PersonID 1 2 3 LastName Green Wall Madigan FirstName Tom Peter Jess Age 12 42 20 After the following SQL statement executes: UPDATE Clients SET LastName ='Pitt' WHERE ID = 3 the table contains the following rows: PersonID 1 2 3 LastName Green Wall Pitt FirstName Tom Peter Jess Age 12 42 20 Update several rows The UPDATE statement updates all rows that meet the criteria found in the WHERE clause. If there is no WHERE clause, every row of the table is updated. After the following SQL statement executes: UPDATE Clients SET Age = Age + 1 WHERE ID = 3 128 Chapter 10: Lesson 6: Adding and Updating SQL Data The table contains the following rows: PersonID 1 2 3 LastName Tom Peter Pitt FirstName Green Green Jess Age 12 42 21 Updating multiple records The cfupdate statement works well when you want to update the current record within a cfquery. [. . . ]