asked on

Coldfusion Page containing a search form that queries database fields returning results that contain all keywords in field

I currently have a coldfusion page that contains a search field that allows a user to enter a search term that could be any string. I received help with getting the query to find search terms no matter what order they were in the database field. Basically it will search for any of the search string words. Now I would like to have the database query search for all of the words in any order in the database field. I tried to just change the query that I am using to having AND instead of OR between the Where items but it gives and error.

Currently when a user enters a search term such as 7ft metal it will return anything that matches either of those keywords . I wanted to know if there is a good way to make it so that the search terms can be in any order in the database field so that search will return items that are labeled both metal and 7ft as part of their description.

Any help would be appreciated.

gdemaria

It's hard to solve the error without seeing the code or being told what the error is. It could be just a missing comma or something.

Here is a block of code that could do it for you, you can switch between OR or AND in the 2nd line..

<cfquery name="qSearch" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
    SELECT *
    FROM site_users t1
    WHERE 1=1 
   <cfif len(trim(form.field_search))> <!---- make sure some text was entered ---->
    and (
   <cfset isFirst = true>
   <cfloop index="aWord" list="#form.field_search#" delimiters=", ">
    <cfif isFirst><cfset isFirst = false><cfelse>OR</cfif>
     first_name + ' ' + last_name LIKE '%aWord%'
   </cfloop>
    )
   <cfelse> <!--- if no search term entered, return nothing, remove this to return ALL records ---->
    and 1=2
   </cfif>
</cfquery>

Open in new window

ASKER CERTIFIED SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Coast Line

put ur search item as:

%#searchkeyword#% - it will search all the characters in the field and display you

#searchkeyword#% - it will search the keyword which start from 7 etc etc

SidFishes

this type of query work may best be handled by using the built in search engine Verity or Solr depending on which version you are using. It handles all the chunky work and removes the need for you to code for all possibilities and allows for Similar, wilcards, scoring

version 8 intro docs are here
http://livedocs.adobe.com/coldfusion/8/htmldocs/intro_verity_2.html#1037795

cf8 query types are here
http://livedocs.adobe.com/coldfusion/8/htmldocs/indexSearch02_2.html#1211859

and examples of some simple queries
http://livedocs.adobe.com/coldfusion/8/htmldocs/indexSearch02_3.html#1212757

it is a bit to wrap your head around but it is a very useful tool. (possibly overkill but you may get other ideas - I know I did)

sigkappu

ASKER

Using gdemaria's code, it returns no results. However, it should return results. 4 records should be returned. Their values for the field being compared are: "7ft Green metal bookcase", "7ft Grey metal bookcase", "7ft Metal bookcase" and "7ft Tan metal bookcase".

I'm not sure why it isn't returning any results. Any ideas?

gdemaria

can you show the resulting query as it looks in the debug output? That will let us know how the SELECT tag looks when passed to the database..

gdemaria

you could also post your code, it may have a small mistake somewhere

sigkappu

ASKER

I did have a syntax error. This line columnName LIKE '%aWord%' needed ## around the %aWord%.