First, Middle, and Last name search box

[jamie61880]

Hey everyone,
Does anyone know how to be able to search on all 3 fields for a First Name, Middle Name, and a Last Name all in the same search box? I am able to do this now, but the problem is if there are 2 words in a last name, such as "La Rue", the search can't find this. The only way I know how to make this work is to do a search on each field separately, but we want to be able to search for all 3 fields in one search box. Does anyone know how to do this? I have attached the 2 different types of code for searching in one box, or searching in 3 boxes that we already have. Thanks.

Andy

[Fetch]

The simplistic way to do this would be to have your code check the number of space-delimited strings entered, then base the search of that number. Like this:

One String (i.e. user likely inputted a first OR last name like James, or Price)
- search against first and last name in db

Two Strings (i.e. user likely inputted a full name like James Price)
- search first string against first name field AND/OR last name against last name field

Three Strings (i.e. user likely inputted a first, middle, and last name like James Terry Price)
- search first string against first name field AND/OR last name against last name field AND/OR middle name against middle name field

Four Strings (i.e. two middle names)
- the code here is the most tricky...first you need to do some fancy trimming to break it up into the three chunks you need, with the two 'middle' strings comprising the middle name, and then do your searches 'as normal'.

Hope this helps!

[Newsgroup_User]

Fetch wrote:
> Four Strings (i.e. two middle names)
>

It is just as likely that it is two word first name ("Mary Sue") or a
multiple word last name ("Von Der LongName") as it is middle names.

[jamie61880]

I have updated the code with what I have attached below. I can search for a last name such as "La Rue" and find the person. But if I type in "Don La Rue", it won't work. How do I do this with searching for all these instances with each field that may have 2 names in each field?

[Newsgroup_User]

try something like this:

where 1=1
<cfif Len(Trim(form.FullName))>
AND First like '%#form.FullName#%'
Or Middle Like '%#form.FullName#%'
Or Last Like '%#form.FullName#%'
<cfif listlen(trim(form.fullname), " ") gt 1>
<cfloop list="#trim(form.fullname)#" delimiters=" " index="w">
OR First LIKE '#w#%'
OR Last LIKE '#w#%'
OR Middle LIKE '#w#%'
</cfloop>
</cfif>
</cfif>


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[jamie61880]

Azadi,
The code you sent didn't work either. When I type in the name "La Rue" it comes up with a lot of contacts that don't even have La Rue in the name anywhere. It comes up with fewer results when "Don La Rue" is typed in, but still doesn't narrow it down to just Don La Rue. Do you have any other ideas? Thanks.

Andy

[Newsgroup_User]

You may want to try this from another direction. Instead of trying to
break up your search name and then trying to match it to various parts
of your name fields you could concatenate the name fields and then
search that for one or more name parts.

How you do this is going to greatly depend on your database management
systems. You will also probably want to look at modify your database
schema so that some of these calculations can be done ahead of the time
and stored for searching rather then doing it all on the fly for every
search.

But the basic idea in pseudo code would be.

WHERE
(lastName LIKE '%FirstName + MiddleName + LastName%' AND
middleName LIKE '%FirstName + MiddleName + LastName%' AND
firstName LIKE '%FirstName + MiddleName + LastName%')

There are opportunities here to add functionality to search for all
names then for two of names then for any one of the names and sort
results accordingly.

[Fetch]

Sorry for the delayed response; the answer to the problem my psuedo-code didn't catch would be to add in one more case - if three strings exist match against two separate cases, one case in which string1 and string2 are matched against the first name, and the second case is where string2 and string3 are matched against the last name...plus the obvious case in which string1 is matched against first, string2 matched against middle, and string3 matched against last.

[Dan_Bracuk]

Change your form so that the user is specifying which name is which.