试图缩短其他loooong查询

Question

This is kind of simple but not at the same time.

I've got a basic search box into which a user puts in a name. A name that only consists of first_name and last_name. The table has the columns first_name, last_name, display_name where firs_name and last_name are obviously what the first and last names, and display_name is either the concatenation of first_name and last_name or a preferred name so William Smith might have a display name of Bill Smith.

The wackyness:

Both first names and last names can be double-names. By that I mean we might have a name like

First Name: Anna Maria    Last Name: Smith
First Name: Anna          Last Name: Bo Johnson
First Name: Anna Maria    Last Name: Bo Johnson.

so best case scenario, a name would be Anna Smith but I've got enough worst case scenarios Anna Maria Bo Johnson to have to do this.

When a user searches for Anna Maria Bo Johnson I need to run a query that's not aware of which of the 4 "words" is a first or last name, and sadly a double first double last name has 84 combinations like Anna Bo Maria Johnson, or Bo Maria Johnson Anna etc...

Now My issue is, I dont want my MySQL query to be 84 lines long like this : (3 word name example)

SELECT * FROM tablename a
WHERE
    (a.first_name = '$search_term[0]' AND a.last_name = '$search_term[1]') OR      
    (a.first_name = '$search_term[0]' AND a.last_name = '$search_term[2]') OR         
    (a.first_name = '$search_term[0]' AND a.last_name = '$search_term[1] $search_term[2]') OR
    (a.first_name = '$search_term[0]' AND a.last_name = '$search_term[2] $search_term[1]') OR
    (a.first_name = '$search_term[1]' AND a.last_name = '$search_term[0]') OR
    (a.first_name = '$search_term[1]' AND a.last_name = '$search_term[1]') OR     
    (a.first_name = '$search_term[1]' AND a.last_name = '$search_term[0] $search_term[2]') OR
    (a.first_name = '$search_term[1]' AND a.last_name = '$search_term[2] $search_term[0]') OR
    (a.first_name = '$search_term[2]' AND a.last_name = '$search_term[0]') OR
    (a.first_name = '$search_term[2]' AND a.last_name = '$search_term[1]') OR
    (a.first_name = '$search_term[2]' AND a.last_name = '$search_term[0] $search_term[1]') OR
    (a.first_name = '$search_term[2]' AND a.last_name = '$search_term[1] $search_term[0]') OR
    (a.first_name = '$search_term[0] $search_term[1]' AND a.last_name = '$search_term[2]') OR
    (a.first_name = '$search_term[1] $search_term[0]' AND a.last_name = '$search_term[2]') OR
    (a.first_name = '$search_term[0] $search_term[2]' AND a.last_name = '$search_term[1]') OR
    (a.first_name = '$search_term[2] $search_term[0]' AND a.last_name = '$search_term[1]') OR
    (a.first_name = '$search_term[1] $search_term[2]' AND a.last_name = '$search_term[0]') OR
    (a.first_name = '$search_term[2] $search_term[1]' AND a.last_name = '$search_term[0]') OR
    a.display_name = '$search_term[0] $search_term[1] $search_term[2]' OR
    a.display_name = '$search_term[1] $search_term[2] $search_term[0]' OR
    a.display_name = '$search_term[2] $search_term[1] $search_term[0]'
ORDER BY last_name, first_name ASC

So what would be a nice quick and more efficient way to write this query out. Basically I need to compare (=) each word against all fields in random single word or group of 2 word combinations.

Thanks for any help in advance!

Answer 1

Use IN statement :

SELECT * 
FROM tablename a 
WHERE concat_ws(' ',a.first_name,a.last_name) IN ('John Smith','Samantha Rose')