Joining two fields together for searching.

Hello,

I have a last name and first name field in my table but want to be able to submit a form with someone either entering a first name or last name or both last name and first name.

My code is below but it's not quite working how I need it yet.

Thanks,

Brian
<cfquery name="qSearch" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
    SELECT *
    FROM site_users t1
    WHERE last_name +' '+ first_name = '%#FORM.field_search#%'
</cfquery>

Open in new window

brihol44Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Brijesh ChauhanConnect With a Mentor Staff IT EngineerCommented:
What is your default storage engine ? You can find it in my.ini file, is it INNODB or MYISAM ?

If you are using MYISAM then you can take advantage of MySQL full text searching.
0
 
Pratima PharandeCommented:
try like this

<cfquery name="qSearch" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
    SELECT *
    FROM site_users t1
    WHERE last_name +' '+ first_name = '%#FORM.field_search#%'
 OR last_name  = '%#FORM.field_search#%' Or first_name = '%#FORM.field_search#%'
</cfquery>
0
 
brihol44Author Commented:
Hello,

Seems to be pulling all of my names no matter what. All of the first names and last names are unique. If I enter just the first name. It pulls all of the records. If I enter the last name same thing or both the last name and first name.

Thank you for the help...

Brian
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Pratima PharandeCommented:
<cfquery name="qSearch" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
    SELECT *
    FROM site_users t1
    WHERE last_name +' '+ first_name = '#FORM.field_search#%'
 OR last_name  = '#FORM.field_search#' Or first_name = '#FORM.field_search#'
</cfquery>
0
 
OP_ZaharinConnect With a Mentor Commented:
do you want them to enter exact name or they can key-in 'BRI' and list all name like 'BRI? try this:

- if exact name:
<cfquery name="qSearch" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
    SELECT *
    FROM site_users t1
    WHERE 
    last_name + ' ' + first_name = '#FORM.field_search#' OR
    last_name = '#FORM.field_search#' OR
    first_name = '#FORM.field_search#' 
</cfquery>

Open in new window


- if search for name like 'something':
<cfquery name="qSearch" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
    SELECT *
    FROM site_users t1
    WHERE 
    last_name + ' ' + first_name LIKE '%#FORM.field_search#%' OR
    last_name LIKE '%#FORM.field_search#%' OR
    first_name LIKE '%#FORM.field_search#%' 
</cfquery>

Open in new window

0
 
Brijesh ChauhanStaff IT EngineerCommented:
If you are using MYISAM, then you just have to do

<cfquery name="qSearch" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
    SELECT *
    FROM site_users t1
    WHERE 
    MATCH (last_name,first_name)
    AGAINST ('#FORM.field_search#')
</cfquery>

Open in new window


the table column should be fullindex, this performs much faster.
0
 
gdemariaCommented:
Your code needs to use LIKE instead of = in order to use the wildcards...

   WHERE last_name +' '+ first_name LIKE '%#FORM.field_search#%'

However, this will not match if the person enters the name in the wrong order.  You are testing SMITH BOB, if someone enters BOB SMITH, there will be no match.

If you want to extend your search, you can check each word seperately as show in the code below.  However, this will return BOB JONES, JOHN SMITH and BOB SMITH.  That may be helpful to your app, it may not.



<cfquery name="qSearch" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
    SELECT *
    FROM site_users t1
    WHERE 1=1 
   <cfif len(trim(form.field_search))> <!---- make sure some text was entered ---->
    and (
   <cfset isFirst = true>
   <cfloop index="aWord" list="#form.field_search#" delimiters=", ">
    <cfif isFirst><cfset isFirst = false><cfelse>OR</cfif>
     first_name + ' ' + last_name LIKE '%aWord%'
   </cfloop>
    )
   <cfelse> <!--- if no search term entered, return nothing, remove this to return ALL records ---->
    and 1=2
   </cfif>
</cfquery>

Open in new window

0
 
ravi_bachwalaCommented:
<cfquery name="qSearch" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
    SELECT *
    FROM site_users t1
    WHERE last_name +' '+ first_name LIKE '%#FORM.field_search#%'
    OR first_name  LIKE '%FORM.field_search%'
    OR last_name LIKE '%FORM.field_search%'
</cfquery>
0
 
gdemariaConnect With a Mentor Commented:
Wow, that looks familiar,  but maybe I am missing something here...


Isn't it true that anything that matches one of these ...

  first_name  LIKE '%FORM.field_search%'
  last_name LIKE '%FORM.field_search%'

will also match this...

   last_name +' '+ first_name LIKE '%#FORM.field_search#%'



So using this...

    WHERE last_name +' '+ first_name LIKE '%#FORM.field_search#%'
    OR first_name  LIKE '%FORM.field_search%'
    OR last_name LIKE '%FORM.field_search%'

Is the same as filtering with this...

    WHERE last_name +' '+ first_name LIKE '%#FORM.field_search#%'


?

0
All Courses

From novice to tech pro — start learning today.