Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
398 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 2000 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 16

Expert Comment

by:Gurpreet Singh Randhawa
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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
 

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

886 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