Auto-Suggest Query

[HestonJames]

Hello Guys,

I'm looking for some help on writing a query which is going to be used for populating an auto-suggest field for a list of customers.

The customer table which I'm querying has three fields which comprise the customers name.

Salutation
FirstName
LastName

The form the user fills in is a single field for search terms.

I've tried using a query like this:

				Select	FirstName,
						LastName,
						Salutation
				From	Customer
				Where	FirstName LIKE <cfqueryparam value="%#ARGUMENTS.Search#%" cfsqltype="cf_sql_varchar" />
				Or		LastName LIKE <cfqueryparam value="%#ARGUMENTS.Search#%" cfsqltype="cf_sql_varchar" />
				Or		Salutation LIKE <cfqueryparam value="%#ARGUMENTS.Search#%" cfsqltype="cf_sql_varchar" />

however this doesn't seem to work quite as I want as the user seems to have put the search words in the correct comprised parts order.

So, searching for 'Heston' finds nothing.
as does searching for 'James'

However, searching for 'Mr.' will find me, as will 'Mr. Hes'

Can anyone please advise me on what it is that I'm doing wrong?

Thanks,

Heston

 

[MdotButler]

If the serch term is "Bill Smith" your query cannot work as neither the first name or the last name are going to be LIKE. Your best bet is to split the query form into the three components. Alternately you could do...

Where Salutation + FirstName + LastName LIKE <cfqueryparam value="%#ARGUMENTS.Search#%" cfsqltype="cf_sql_varchar" />

Your query form should then state the order you are expecting the name component in otherwise you will get "Smith, Bill" and the query still fails.

HTH
Mark