CODEWITHSUNDEEP
phpsqlmysqlsearchselect
Norbert
Norbert

Reputation: 2771

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 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

Upvotes: 0

Views: 1928

Answers (4)

Déjà vu
Déjà vu

Reputation: 28830

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.

Upvotes: 1

rgin
rgin

Reputation: 2311

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

Upvotes: 0

Nanne
Nanne

Reputation: 64399

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 :)

Upvotes: 0

Robik
Robik

Reputation: 6127

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.

Upvotes: 1

Related Questions