Simple Database Search

Home > ColdFusion Tutorials > User Experience > Simple Database Search

Simple Database Search

Get the Code for this Tutorial
View a Working Example of This Code
Comment on this Tutorial

Simple Coldfusion Search - User enters word or words to search for, coldfusion gets the results from your database and displays them.
We will play with two files here. The form is in frmSearch.cfm and the action, or results page is in actSearch.cfm. You can arrange things however you like, but this will show the basics clearly.

The Form
First we need a FORM to enter our search criteria. This part is simple. A plain html form with the action attribute pointing to actSearch.cfm. The method is POST, sending the form data the FORM scope in Coldfusion, rather than the URL scope if we where to use method="get". Finish it off with a simple submit button. Also notice that the form submits to itself.

Save this as index.cfm

<form action="index.cfm" method="post" enctype="text/plain">
   Search For: <input type="text" name="search" />
   <input type="submit" value="Go!" name="submitit" />
</form>
<hr>

That is really all there is to that, you will most likely want to format it a bit nicer.

If the Form is Submitted...
Let's use a cfif to tell if the form has been submitted. Remember, the form submits to itself, there is no 'form page' 'action page' - they are one and the same, our index.cfm. Because we used the HTML form method of POST, we will be using the Coldfusion FORM variable scope to get our for data. In this case, we are going to look at the FORM.search variable to get the search words that the user entered into the form.

We want to start by checking that our FORM was even submitted. Do this by making sure the name of the submit button and the text field was sent to the actSearch.cfm page. If one of them does not exist, the form should not be processed.

<cfif structKeyExists(form,"submitit") AND structKeyExists(form,"search")>

The Query...
The next step is to make our query. This query, if left as is, would return every row, every time. We don't want that!

<cfquery datasource="#dsn#" name="getResults">
   SELECT names, ages
   FROM cfml_example_data
</cfquery>

So we are going to add a WHERE clause to the query to use the word(s) that the user is searching for to narrow our query results. The SQL LIKE word allows use to use wildcards so that we can search partial matches.
In the CFQUERYPARAM tag, you can see the use of percent signs (%) before and after the search word. These are SQL wildcards. It will match our search word, with anything before it, and after it.

WHERE names LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#form.search#%">

The complete query tag then looks like this:

<cfquery datasource="#dsn#" name="getResults">
   SELECT names, ages
   FROM cfml_example_data
   WHERE names LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#form.search#%">
</cfquery>

Get Results
Before we output the results, we want to make sure that there even where any results, so we can let the user know whats going on. By checking the record count of our query, we can see how many results there are. if there are results, tell the user how many, if there are no results, let the user know that too. You'll notice I like to use the record count as a Coldfusion boolean, rather then an integer. It saves time and space, and works just as well. Any number that is NOT 0 is a YES or TRUE boolean in Coldfusion.

<cfoutput>
   <cfif getResults.recordCount>
      Hello, there are #getResults.recordCount# results for your search for '#FORM.search#'
   <cfelse>
      Sorry! Your search for '#FORM.search#' returned no results!
   </cfif>
</cfoutput>

Now we can output the results. A simple CFOUTPUT with the query attribute will do the trick. If there are no results, nothing will be displayed here.

<table>
   <tr>
      <td>Names</td>
      <td>Ages</td>
   </tr>
</table>
<cfoutput query="getResults">
   <tr>
      <td>#names#</td>
      <td>#ages#</td>
   </tr>
</cfoutput>
</table>

Finish off the cfif

</cfif>

And that's the simple search. A more advanced example will come soon. You can also incorporate the 'Pagination Tutorial' if you are expecting long recordsets, the 'Sort By Table Headers Tutorial' to allow the results to be sorted, and even the 'Alternating Row Colors Tutorial' to make the results easier to read.

____________

Back to the Top

Tutorial Code

Download the Database

<form action="index.cfm" method="post" enctype="text/plain">
   Search For: <input type="text" name="search" />
   <input type="submit" value="Go!" name="submitit" />
</form>
<hr>


<cfif structKeyExists(form,"submitit") AND structKeyExists(form,"search")>



<cfquery datasource="#dsn#" name="getResults">
   SELECT names, ages
   FROM cfml_example_data
   
   WHERE names LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#form.search#%">
</cfquery>


<cfoutput>
   
   <cfif getResults.recordCount>
      Hello, there are #getResults.recordCount# results for your search for '#FORM.search#'
   <cfelse>
   
      Sorry! Your search for '#FORM.search#' returned no results!
   </cfif>
</cfoutput>


<table>
   <tr>
      <td>Names</td>
      <td>Ages</td>
   </tr>
</table>


<cfoutput query="getResults">
   <tr>
      <td>#names#</td>
      <td>#ages#</td>
   </tr>
</cfoutput>
</table>


</cfif>

Back to the Top

Comments

Oliver
09/27/2007

It is very simple and quite a solution for me who's unable to search database using CFSEARCH. Thanx! Anyway, do you have CFSEARCH tutorial?

Oliver
09/27/2007

Why do you have to use CFQUERYPARAM on : <br> WHERE names LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#form.search#%"> <br><br> instead of: WHERE names LIKE "%#form.search#%" <br> ??

Kevin
10/22/2007

http://www.lot-o-nothin.com/cfml/articles/index.cfm/id/1/

CFML Scripts and Tutorials