Improve company productivity with a Business Account.Sign Up

x
?
Solved

Multi Word Search

Posted on 2011-03-16
4
Medium Priority
?
310 Views
Last Modified: 2012-05-11
I am trying this code example:

<CFQUERY NAME="searchresults" DATASOURCE="yourdatasource">
    SELECT *
    FROM table
    WHERE 0=0
    AND

(
<cfloop index="i" list="#form.searchkeyword#" delimiters=" ">
    <cfoutput>fieldname LIKE '%#i#%' AND </cfoutput>
</cfloop>
'%%')

</CFQUERY>


The program I am having is that the last '%%' is giving me no results.  If i take that last and '%%' i get the correct results.

Is there a better way to do something like this?
0
Comment
Question by:theideabulb
  • 2
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 35148043
Only include the AND block if there is a searchKeyword
Then use OR to find any of the words, but only put in an OR after the first word

<cfset isFirst=true>
<CFQUERY NAME="searchresults" DATASOURCE="yourdatasource">
    SELECT *
    FROM table 
    WHERE 0=0
  <cfif len(form.searchkeyword)>
    AND (
   <cfloop index="i" list="#form.searchkeyword#" delimiters=" ">
    <cfif isFirst><cfset isFirst=false><cfelse>OR</cfif>
     fieldname LIKE '%#i#%'
    </cfloop>
    )
  </cfif>
</cfquery>

Open in new window

0
 

Author Comment

by:theideabulb
ID: 35148155
That worked great, but I am still confused on the AND/OR thing.

Say I want to search for DVD and Star Wars, or say the product number for that is 12345 and then of course the title is Star Wars.  What is the best way to search for that?
0
 

Author Closing Comment

by:theideabulb
ID: 35148163
Great solution.  Answered my question perfectly
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35148234
AND would have to find both STAR and WARS

OR would find either STAR or WARS

Typically you would match either, you could give the user the option or choose the one that fits for your app
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
In the video, one can understand the process of resizing images in single or bulk. Kernel Bulk Image Resizer is an easy to use tool for resizing large number of images. One can add and resize multiple images with this tool in single go. The video sh…
Watch the video to know the process of migration of Exchange or Office 365 mailboxes in absence of MS Outlook. It is an eminent tool which can easily migrate Public, Archive user mailboxes from one another Exchange server and Office 365. Kernel Migr…

595 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