Solved

Multi Word Search

Posted on 2011-03-16
4
296 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 500 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

23 Experts available now in Live!

Get 1:1 Help Now