Searching 2 fields at the same time

Question

I have a table of first and last names

firstname    lastname
---------    ---------
Joe          Robertson
Sally        Robert
Jim          Green
Sandra       Jordan

I'm trying to search this table based on an input that consists of the full name. For example:

input: Joe Robert

I thought about using

SELECT * FROM tablename WHERE firstname LIKE

BUT the table stores the first and last name separately, so I'm not sure how to do the search in this case

Answer 1

In MyISAM:

SELECT  *
FROM    mytable
WHERE   MATCH(firstname, lastname) AGAINST ("Joe* Robert*" IN BOOLEAN MODE);

This will run much faster if you create a FULLTEXT index:

CREATE FULLTEXT INDEX ON mytable (firstname, lastname)

For the query to be able to search the short names (like Joe in your case), you'll need to set the variable @@ft_min_word_len to 1 before creating the index.

In InnoDB you'll need to split the search string:

SELECT  *
FROM    mytable
WHERE   firstname LIKE 'Joe%'
        AND lastname LIKE 'Robert%'

Answer 2

An alternative to Quassnoi's method:

SELECT  *
FROM    mytable
WHERE   CONCAT(firstname, " ", lastname) = "Joe Robert"