试图缩短其他loooong查询

问题描述：

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!

答

Use IN statement :

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

答

I'm a bit confused on what you want to match. Looking at the query you have, it seems like you'd match Maria Bo Johnson or even Bo Anna. Is that intended?

If so, can you do

SELECT * FROM tablename a WHERE
(a.first_name = '$search_term[0]' OR a.first_name = '$search_term[1]' OR
a.first_name = '$search_term[2]' OR a.first_name = '$search_term[3]')
AND
(a.last_name = '$search_term[0]' OR a.last_name = '$search_term[1]' OR
a.last_name = '$search_term[2]' OR a.last_name = '$search_term[3]')

(Possibly adding a check that first_name != last_name...)

答

Dont explode but concatenise in mysql

where concat(a.first_name, ' ', a.last_name) = '$search_terms' or a.display_name = '$search_terms'

and use LIKE if the input might be just a part of the name:

where concat(a.first_name, ' ', a.last_name) LIKE '%$search_terms%' or a.display_name LIKE '%$search_terms%'

edit if you fear people sometimes starting with last name, sometimes with first name, add 1 more condition:

where concat(a.first_name, ' ', a.last_name) = '$search_terms' or concat(a.last_name, ' ', a.first_name) = '$search_terms' or a.display_name = '$search_terms'

试图缩短其他loooong查询

相关推荐