Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

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.
0
sigkappu
Asked:
sigkappu
1 Solution
 
gdemariaCommented:
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

0
 
gdemariaCommented:
oops, pasted in the wrong version.  Here it is with your search term and the OR/AND variable..

<cfset form.field_search = "7ft metal">
<cfset variables.andOR = "AND">  <!---- set to AND or OR to match all or ANY ----->

<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>#variables.andOR#</cfif>
     columnName 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

0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
SidFishesCommented:
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)
0
 
sigkappuAuthor Commented:
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?
0
 
gdemariaCommented:
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..
0
 
gdemariaCommented:
you could also post your code, it may have a small mistake somewhere
0
 
sigkappuAuthor Commented:
I did have a syntax error.    This line  columnName LIKE '%aWord%'  needed ## around the %aWord%.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now