Search for the same piece of data in multiple columns

snorider

Hello,

Right now I have a table set up with a first_name and last_name field in it. I have a search box the user can enter text into and I am trying to make it so that if they enter "John Smith" the database will check both first_name and last_name for "John" and "Smith".

Something that might look like this:

SELECT * FROM names WHERE (first_name OR last_name) LIKE "John" OR (first_name OR last_name) Like "Smith";

Is there any way to do this without going the long way and putting first_name LIKE "John" OR first_name LIKE "Smith" OR ... ?

It has to check both because the user might enter "Smith John" or "John Smith" and I want both to return the same result.

Also just using first_name and last_name form fields is not a viable option for this.

Thanks in advance for any help you can give me

Lars_Berg

If you use like there is no shortcut. I'm a bit curious why you have like but no wildcards?

If you are looking for exact matches you could write

SELECT * 
FROM names 
WHERE 'John' in (first_name,last_name) or 'Smith' in (first_name, last_name)

sfullman

Take 5 minutes browsing the functions section of the mysql manual

http://www.mysql.com/doc/en/Function_Index.html

but the way I do this is

select whatever from table where CONCAT(FirstName, ' ', LastName) = 'John Smith' (poor John Smith)

you could even still use like vs. =

you can also make that a field:
select CONCAT(FirstName, ' ', LastName) AS FullName
WHERE
CONCAT(FirstName, ' ', LastName) = 'John Smith' (poor John Smith)

Unfortunately, you can't do CONCAT(*) to search all fields, you need to name them specifically.

Sam Fullman

snorider

Unfortunately I don't know what I would be looking for in the MySQL functions list. I'm all out of ideas for functions that it has that I might use.

I had tried the method you suggested a little earlier in my testing and I found that it works great for "John Smith" but if the user enters in "Smith John" it doesn't work.

Any other ideas would be appreciated

Thanks again