1 Reply Latest reply on Apr 12, 2008 10:11 AM by Dan Bracuk

    Creating a Better Search Form


      I'm trying to figure out how to create a dynamic search form using Coldfusion 8.

      Right now if I enter multiple words in my search field the results omit certain records. For example, if I put "toy blue" in the search field it will return results that only have "TOY BLUE" together and omits any records that only have "TOY" or only have "Blue" in the column being searched.

      Is there a way to add the following abilities to my basic search code located at the bottom of my post:

      - Search multiple words that are entered in the criteria input field.
      - The words can be separated by commas, spaces, or the word "and".
      - Have it so you can search multiple word phrases by using quotes.

      <!--- Search Form--->

      <form name="myForm" method="post" action="results.cfm">
      <input name="criteria" type="text" id="criteria">
      <input type="submit" name="Submit" value="Submit">

      <!--- Results Page --->

      <cfquery name="qSearch" datasource="myDatabase" dbtype="ODBC">
      SELECT itemID, itemName
      FROM myTable
      WHERE 1 = 1

      <cfif IsDefined("form.criteria") AND Len(Trim(form.criteria))>
      and (itemName LIKE '%#form.criteria#%' or itemDescription LIKE '%#form.criteria#%')


      <cfoutput query="qSearch">
      <p>#itemid# - #itemName#</p>


      Any help would be greatly appreciated.


        • 1. Re: Creating a Better Search Form
          Dan Bracuk Level 5
          This is the relevent sql
          and (itemName LIKE '%#form.criteria#%' or itemDescription LIKE '%#form.criteria#%')

          substituting the variable, it looks like this
          and (itemName LIKE '%TOY BLUE%' or itemDescription LIKE '%TOY BLUE%')

          You have to treat form.criteria as a list, and loop through it.