Search 2 Columns with 1 Input Field
- by Norbert
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 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
