Solved

Multi Word Search

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

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 …
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

775 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