Get Started with ColdFusion

Lesson 3: Using CFML to Search the Database

In part two, we used CFML to dump the entire database out to the screen. This works fine for a small database, but in real life it’s rarely useful, and can cause some serious performance problems. The easiest solution is to give users the ability to search the database to find exactly what they need. With CFML this is a snap.

1. Create a new file inside the lesson2 folder named INDEX.CFM

2. Between the BODY tags of this document, add an HTML form like so:

   <div align=”center”>    
   
 <form action=”search.cfm” method=”POST”>    
 Search for:<input type=”text” name=”k”>    
<input type=”submit” value=”Find!”>    
 </form>    
   
</div>

3. Now create another new page named SEARCH.CFM

4. Between the BODY tags we need to deal with validating the submitted form. Add this code:

   <CFIF len(FORM.k) IS 0>    
 Oops!<br>    
 You need to enter some search criteria!<br>    
 <a href=”javascript:history.back()”>Go back</a>    
and try again.    
   
 <CFELSE>    
   
<CFQUERY DATASOURCE=”CFdata” NAME=”findem”>    
SELECT *    
FROM Info    
WHERE Name LIKE ‘%#FORM.k#%’    
</CFQUERY>    
   
<CFIF findem.recordcount IS 0>    
   
 Sorry!<br>    
 Your search for <CFOUTPUT>#FORM.k#</CFOUTPUT>    
did not return any matches.<br>    
<form action=”search.cfm” method=”POST”>    
 Search again:<input type=”text” name=”k”>    
<input type=”submit” value=”Find!”>    
 </form>    
   
   
<CFELSE>    
<!--- display your results --->    
   
<table align=”center” border=”1”>    
<tr><td colspan=”6” align=”center”>Your search returned    
<cfoutput>#findem.recordcount#</cfoutput> matches.</td></tr>    
   
<CFOUTPUT QUERY=”findem”>    
<tr>    
 <td>#CURRENTROW#</td>    
 <td>#NAME#</td>    
 <td>#ADDRESS#<BR>#CITY# #STATE#, #ZIP#</td>    
 <td>#PHONE#</td>    
 <td>#EMAIL#</td>    
 <td>#NOTES#</td>    
</tr>    
</CFOUTPUT>    
</table>    
</CFIF>    
 </CFIF>
   

5. Save the pages and try them out in your browser.

Now, let’s review the code. There’s a lot more this time so we’ll take it in chunks.

The INDEX.CFM page contains no tricks. It’s just a simple HTML form. I named the text field “k” to stand for “keywords”…this is just how I do it.

SEARCH.CFM contains a load of CFML you’ve probably never seen before. The first new tag you see is <CFIF>. This is the standard method we use to deal with conditional processing in CFML. This tag has several variations. For full details, check the reference at the end of this article. Basically what this specific use does, is stop users who try to submit for search form without entering any search keywords.

Conditional processing allows a developer to say “if the sky is blue do this. Otherwise do this or this”. This lets you trap events and possible errors, and control them to do exactly what you need.

<CFIF len(FORM.k) IS 0>

The “len(FORM.k) IS 0” portion of the above tag uses the LEN() function to determine the length of the submitted form field contents. In this case, if the length of the form field is zero, we will stop the page from processing.

<CFELSE>

This tag is the “otherwise” condition. So if a user submits proper search criteria, CFMX will continue to process the page.

Let’s take a closer look at that SQL. The first two lines should be familiar, but that last line might be new.

WHERE Name LIKE ‘%#FORM.k#%’

This is the WHERE CLAUSE in SQL. It’s used to tell the database exactly what data you want selected. The basic syntax is WHERE ColumnName LIKE ‘something’. The “something”, if surrounded by quotes, must be a STRING VALUE -- a word or words. Without quotes, you tell the database to match on a number. In this case, we’re letting ColdFusion fill in the blank with the form field value submitted by the user.

Wildcards: as with many languages, SQL allows you to use wildcards to increase the power of a search. In the above code we use the percent symbol to denote a wildcard. Inside the percent signs we place the submitted form variable “k”. Here are some examples:

• ‘%variable%’ means CONTAINS the variable value
• ‘variable%’ means START WITH the variable value and ends in anything
• ‘%variable’ means ENDS WITH the variable value and starts with anything.

The next section of the results code that we need to examine is the next CFIF block. This code checks the RECORDCOUNT of the query, and if it is not zero -- meaning the query returns anything larger than the number zero -- the page will process. Next is a section that states how many records were found that match. Note the way the variable is called: FINDEM.RECORDCOUNT. This uses the query variable RECORDCOUNT, and only displays it once. If you were to place this code inside a CFOUTPUT that contained the QUERY attribute, the code would repeat for each record found! Not at all useful!

Each ColdFusion query returns three automatic variables. CURRENTROW is the actual numbering of each record. For instance, if your query returns 50 records CURRENTROW would number them 1-50. RECORDCOUNT returns as a number the amount of records your query found. COLUMNLIST returns a comma separated list of the columns in the table searched.

So there you have it! The basics of ColdFusion and CFML. As you can see, it takes some work, but CFML is a simple and effective way to create any kind of Web application you can think of. Keep your eyes peeled for more on CF soon!