Search 2 Columns with 1 Input Field

I have a db with two columns: first name and last name. The first name can have multiple words. Last name can contain hyphenated words.

Is there a way to search both columns with only one input box?

Database

ID        `First Name`       `Last Name`
1          John Peter         Doe
2          John               Fubar
3          Michael            Doe

Search

john peter returns id 1

john returns 开发者_StackOverflow社区id 1,2

doe returns id 1,3

john doe returns id 1

peter john returns id 1

peter doe returns id 1

doe john returns id 1

I previously tried the following. Searching for John Doe:

SELECT * FROM names WHERE (
  `first` LIKE '%john%' OR
  `first` LIKE '%doe%' OR
  `last` LIKE '%john%' OR
  `last` LIKE '%doe%'

)

which returns both 1 and 3

A correlation between the first and last names is required.

For two words (w1,w2), the condition

( first LIKE "%w1%" AND last LIKE "%w2%" ) OR ( first LIKE "%w2%" AND last LIKE "%w1%" )

Meaning, w1 must be in first name AND w2 in last name,
OR w2 must be in first name AND w1 in last name.

This way John Doe will have ID 1 selected.

You can split user name "words" by space and get the last item of array which should be his last name. Then you should query SQL like "Nanne" said or you can use tags search.

Good luck.

I don't know what you're asking exactly, but don't you mean just this?

WHERE (`first name` LIKE '%searchString%' OR `last name` LIKE '%searchString%')

As you comment says, if any of the words can be in any column (and should they be present at least once? or should it be best fit? does a different order mean anything) then you'll have to do something more, but you really need to think of some specifications then first :)

A short solution would be to find the last whitespace of the input by preg_match or something to that effect(really can't remember the right function right now), then use that as a marker. Store everything after the marker in the last name "searchstring" and everything before it in the first name "searchstring".

An obvious flaw in this solution though would be if the person had 2 words for a last name. But, I'm not sure if you /do/ accept 2 word last names or not. So, let me quote Nanne here:

you should clearify your question a bit, don't you think? what can be searched, what is in the columns, etc etc