SQL Query - Multiple Keywords searching Multi Columns

I am looking for a way to search multiple columns in a sql table with multiple keywords using Coldfusion. I currently have the following code which half works.

<cfquery name="rsSearchEmployees" datasource="#request.database#">
SELECT idn, firstname, lastname
FROM Employees 
WHERE (<cfloop list="#arguments.nameSearchString#" delimiters=" " index="word">firstname LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#Trim(word)#%" /> OR </cfloop> (1 <> 1))
OR (<cfloop list="#arguments.nameSearchString#" delimiters=" " index="word">lastname LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#Trim(word)#%" /> OR </cfloop> (1 <> 1))
ORDER BY firstname ASC

It searches 2 columns, firstname and lastname with keywords entered into a textbox. The keywords are broken down using the cfloop tag.

This code does produce results, but currently does not remove results if 2 or more keywords are not found in one or both columns.

For example, say i wanted to search for Steve Smith, when i type in Steve it shows the row for Steve. The same happens for Smith. If i type in Steve Smith it still shows the row which is correct.

It goes wrong wh开发者_StackOverflow中文版en i type in something like Steve Jobs. Typing this in still shows Steve Smith when it shouldnt. Does anyone have a clue how to get around this?

Thanks for any help in advance. Chris

Chris,

I think the heart of your problem is that your logic is currently saying "Show me all records where FirstName or LastName matches one of the words in the search string". You are allowing someone to search "Matthew Mark Luke John" as a string to pull up people with ANY of those names, rather than treating the search word as a single name only.

This causes the fact that record 'Steve Jobs' comes up for search='Steve Smith' due to the match on firstName only. Sounds like the SQL logic is too broad.

Sounds like you want a search to instead treat the string as a full name (first name + lastname) instead of a list of multiple names to match against. However, if more than one word is provided, you want to narrow down quickly on records that match ALL words not ANY.

Names can be complicated things, with first or last name being mutiple words. This presents some minor difficulties.

Desired outcome:

• Search "Allen" - bring up records with first or last name of 'Allen'
• Search "Allen Smith" bring up records with BOTH first and last name matches against 'Allen' and/or 'Smith'

This query will allow any # of words in search string but only show records that match each word in some fashion (allows searching names in any order, eg. First Last or Last First). This is probably what you want.

SELECT idn, firstname, lastname
FROM Employees 
WHERE 1=1
<cfloop list="#arguments.nameSearchString#" delimiters=" " index="word">
    AND ( firstname like <cfqueryparam cfsqltype="cf_sql_varchar" value="#word#%" />
    OR lastname like <cfqueryparam cfsqltype="cf_sql_varchar" value="#word#%" /> )
</cfloop>

That you're wrapping both sides of your LIKE clauses with percents tells me you really want some fuzzy-ish matching. I would skip with SQL WHERE statements and use a full text solution instead. Options include:

1. Database Full Text Indexing: SQL Server, Oracle and MySQL all have solutions.
2. Search Engine: ColdFusion 9 ships with both Verity and SOLR

Either of these options will make your life much easier and avoid the complex AND/OR LIKE logic you'd need to introduce in a SQL WHERE clause to achieve some similar effect.

Making a couple of assumptions, like that you will always enter a "firstname" and "lastname", to look for and that you want to fuzzy search on the stub e.g. smith ... returns smith, smithers etc. this might do it. You could put back in your leading % if you really want to match blacksmith, ironsmith, etc.

<cfquery name="rsSearchEmployees" datasource="#request.database#">
SELECT idn, firstname, lastname
FROM Employees 
WHERE firstname like <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(word)#%" />
AND lastname like <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(word)#%" />
</cfquery>

you could run an alternative "OR" SQL if only one word is being searched for