Searching for keywords in two MySQL columns

1
votes

I have a user table with columns named first_name and last_name.

SELECT * 
    FROM users 
WHERE first_name LIKE '%keywords%' 
    OR last_name LIKE '%keywords%'

Using the above, if I search for "John" or for "Doe" I'll get a hit.

But if I search for "John Doe" I will get 0 results. How can I search MySQL in a way that will match "first_name last_name" rather than just one or the other?

3
sqlmysql
Are you limited to only using one variable? – OMG Ponies
The variable is input from a search field, so it may be just one word or it may be two (first and last name). – Jeff

3 Answers

1
votes

One solution is to split the keywords in your application, and then build the query as follows:

-- Split "John Doe" -> "John", "Doe"

SELECT * FROM users WHERE
(
    (
         first_name LIKE '%keyword_1%' OR
         last_name LIKE '%keyword_1%'
    )
    AND
    (
         first_name LIKE '%keyword_2%' OR
         last_name LIKE '%keyword_2%'
    )
)

The above would also match "Doe Joe", not just "Joe Doe". This will get bigger if you are searching into more columns, as you will have to add an "AND block" for each keyword.

In addition, this comes with a big performance cost, since such a query is unable to use indexes on first_name and last_name, if there are any. A better approach would be to use Full Text indexing.

0
votes

Try:

SELECT * FROM users WHERE CONCAT(first_name, ' ', last_name) LIKE '%keywords%';