?
Solved

Multi Word Search

Posted on 2011-03-16
4
Medium Priority
?
303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

Question has a verified solution.

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

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
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 …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month10 days, 10 hours left to enroll

765 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