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

This is the first one that has just one search box for all 3 fields:
<CFIF IsDefined("url.FullName")>
<CFSET form.FullName=url.FullName>
</CFIF>
<CFQUERY NAME="ContactSearch" datasource="#application.DataSource#">
SELECT Company, First, Middle, Last,
ContactID, Contact_Num, RFQID, RFQID_SPEC, ItemID, Entry_Date, RFQ_Leader,
Standard_Part_Number, Generic_Description, M2M_Quote_Number,
Sales_Order_Number, Company_Classification, Highest_Quantity_Request,
Date_First_Entered, Address1, Address2, Address3, City, State, Zip, Country,
Phone, Cell_Phone, Fax, Email

<!--- Use this joining code below to display only 1 of each RFQ Number and to
not have multiple items of an RFQ display on the page. This works because of
the ItemID and it's not the RFQID. --->
FROM
(((Contacts LEFT JOIN RFQ_Numbers ON Contacts.ContactID =
RFQ_Numbers.Contact_Num)
LEFT JOIN Companies ON Contacts.Company = Companies.Company_Name)
LEFT JOIN RFQ_SPEC ON RFQ_SPEC.ItemID = RFQ_Numbers.Item_Num)

where 1=1
<cfif listlen(form.fullname,' ') eq 3>
AND First like '%#listgetat(form.FullName,1,' ')#%'
AND Middle Like '%#listgetat(form.FullName,2,' ')#%'
AND Last Like '%#listgetat(form.FullName,3,' ')#%'
<cfelseif listlen(form.fullname,' ') eq 2>
AND First like '%#listgetat(form.FullName,1,' ')#%'
AND Last Like '%#listgetat(form.FullName,2,' ')#%'
<cfelse>
And First like '%#form.FullName#%'
OR Middle Like '%#form.FullName#%'
OR Last Like '%#form.FullName#%'
</cfif>

ORDER BY #set_order#
</CFQUERY>



This is the 2nd one that has 3 separate search boxes for all 3 fields:
<CFIF IsDefined("url.FirstName")>
<CFSET form.FirstName=url.FirstName>
</CFIF>
<CFIF IsDefined("url.MiddleName")>
<CFSET form.MiddleName=url.MiddleName>
</CFIF>
<CFIF IsDefined("url.LastName")>
<CFSET form.LastName=url.LastName>
</CFIF>

<CFQUERY NAME="ContactSearch" datasource="#application.DataSource#">
SELECT Company, First, Middle, Last,
ContactID, Contact_Num, RFQID, RFQID_SPEC, ItemID, Entry_Date, RFQ_Leader,
Standard_Part_Number, Generic_Description, M2M_Quote_Number,
Sales_Order_Number, Company_Classification, Highest_Quantity_Request,
Date_First_Entered, Address1, Address2, Address3, City, State, Zip, Country,
Phone, Cell_Phone, Fax, Email

<!--- Use this joining code below to display only 1 of each RFQ Number and to
not have multiple items of an RFQ display on the page. This works because of
the ItemID and it's not the RFQID. --->
FROM
(((Contacts LEFT JOIN RFQ_Numbers ON Contacts.ContactID =
RFQ_Numbers.Contact_Num)
LEFT JOIN Companies ON Contacts.Company = Companies.Company_Name)
LEFT JOIN RFQ_SPEC ON RFQ_SPEC.ItemID = RFQ_Numbers.Item_Num)

where 1=1
<cfif Len(Trim(form.FirstName))>
AND First like '%#form.FirstName#%'
</cfif>
<cfif Len(Trim(form.MiddleName))>
AND Middle Like '%#form.MiddleName#%'
</cfif>
<cfif Len(Trim(form.LastName))>
AND Last Like '%#form.LastName#%'
</cfif>

ORDER BY #set_order#
</CFQUERY>

[GArlington]

Post by 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
 <CFIF IsDefined("url.FullName")>
 <CFSET form.FullName=url.FullName>
 </CFIF>
 <CFQUERY NAME="ContactSearch" datasource="#application.DataSource#">
 SELECT Company, First, Middle, Last,
 ContactID, Contact_Num, RFQID, RFQID_SPEC, ItemID, Entry_Date, RFQ_Leader,
Standard_Part_Number, Generic_Description, M2M_Quote_Number,
Sales_Order_Number, Company_Classification, Highest_Quantity_Request,
Date_First_Entered, Address1, Address2, Address3, City, State, Zip, Country,
Phone, Cell_Phone, Fax, Email
 <!--- Use this joining code below to display only 1 of each RFQ Number and to
not have multiple items of an RFQ display on the page. This works because of
the ItemID and it's not the RFQID. --->
 FROM
 (((Contacts LEFT JOIN RFQ_Numbers ON Contacts.ContactID =
RFQ_Numbers.Contact_Num)
 LEFT JOIN Companies ON Contacts.Company = Companies.Company_Name)
 LEFT JOIN RFQ_SPEC ON RFQ_SPEC.ItemID = RFQ_Numbers.Item_Num)
 where 1=1
 <cfif listlen(form.fullname,' ') eq 3>
 AND First like '%#listgetat(form.FullName,1,' ')#%'
 AND Middle Like '%#listgetat(form.FullName,2,' ')#%'
 AND Last Like '%#listgetat(form.FullName,3,' ')#%'
 <cfelseif listlen(form.fullname,' ') eq 2>
 AND First like '%#listgetat(form.FullName,1,' ')#%'
 AND Last Like '%#listgetat(form.FullName,2,' ')#%'
 <cfelse>
 And First like '%#form.FullName#%'
 OR Middle Like '%#form.FullName#%'
 OR Last Like '%#form.FullName#%'
 </cfif>
 ORDER BY #set_order#
 </CFQUERY>
 <CFIF IsDefined("url.FirstName")>
        <CFSET form.FirstName=url.FirstName>
 </CFIF>
 <CFIF IsDefined("url.MiddleName")>
        <CFSET form.MiddleName=url.MiddleName>
 </CFIF>
 <CFIF IsDefined("url.LastName")>
        <CFSET form.LastName=url.LastName>
 </CFIF>
 <CFQUERY NAME="ContactSearch" datasource="#application.DataSource#">
 SELECT Company, First, Middle, Last,
 ContactID, Contact_Num, RFQID, RFQID_SPEC, ItemID, Entry_Date, RFQ_Leader,
Standard_Part_Number, Generic_Description, M2M_Quote_Number,
Sales_Order_Number, Company_Classification, Highest_Quantity_Request,
Date_First_Entered, Address1, Address2, Address3, City, State, Zip, Country,
Phone, Cell_Phone, Fax, Email
 <!--- Use this joining code below to display only 1 of each RFQ Number and to
not have multiple items of an RFQ display on the page. This works because of
the ItemID and it's not the RFQID. --->
 FROM
 (((Contacts LEFT JOIN RFQ_Numbers ON Contacts.ContactID =
RFQ_Numbers.Contact_Num)
 LEFT JOIN Companies ON Contacts.Company = Companies.Company_Name)
 LEFT JOIN RFQ_SPEC ON RFQ_SPEC.ItemID = RFQ_Numbers.Item_Num)
 where 1=1
 <cfif Len(Trim(form.FirstName))>
        AND First like '%#form.FirstName#%'
 </cfif>
 <cfif Len(Trim(form.MiddleName))>
        AND Middle Like '%#form.MiddleName#%'
 </cfif>
 <cfif Len(Trim(form.LastName))>
        AND Last Like '%#form.LastName#%'
 </cfif>
 ORDER BY #set_order#
 </CFQUERY>

The usual way of using one search field is to break search field value
into sub-strings (use ListGetAt() using space as separator) and then
concatenate all DB fields that you are searching and compare to each
of the search sub-strings...

[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!

[Ian Skinner]

Post by Fetch
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?

where 1=1
<cfif listlen(form.fullname,' ') eq 3>
AND First like '%#listgetat(form.FullName,1,' ')#%'
AND Middle Like '%#listgetat(form.FullName,2,' ')#%'
AND Last Like '%#listgetat(form.FullName,3,' ')#%'
<cfelseif listlen(form.fullname,' ') eq 2>
AND First like '%#listgetat(form.FullName,1,' ')#%'
AND Last Like '%#listgetat(form.FullName,2,' ')#%'
<cfelse>
And First like '%#form.FullName#%'
OR Middle Like '%#form.FullName#%'
OR Last Like '%#form.FullName#%'
</cfif>

<cfif Len(Trim(form.FullName))>
Or First like '%#form.FullName#%'
Or Middle Like '%#form.FullName#%'
Or Last Like '%#form.FullName#%'
</cfif>

[Azadi]

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

[Ian Skinner]

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.