Solved

Multi Word Search

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Coldfusion Datefield problem 2 124
Need help with EXECUTE sp_executesql in ColdFusion cfquery 10 39
cfscript coding help 4 34
Lucee & <cftransaction.... />  WITAF? 10 30
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
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 …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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