Solved

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

Posted on 2011-03-24
8
385 Views
Last Modified: 2012-05-11
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
Comment
Question by:sigkappu
8 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 35207132
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
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 35207142
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
 
LVL 15

Expert Comment

by:myselfrandhawa
ID: 35207163
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 35207171
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:sigkappu
ID: 35207406
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 35207427
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 35207443
you could also post your code, it may have a small mistake somewhere
0
 

Author Closing Comment

by:sigkappu
ID: 35207576
I did have a syntax error.    This line  columnName LIKE '%aWord%'  needed ## around the %aWord%.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now