CODEWITHSUNDEEP
databasemysql
donpal
donpal

Reputation: 2112

Searching 2 fields at the same time

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

Upvotes: 2

Views: 77

Answers (2)

Scharrels
Scharrels

Reputation: 3055

An alternative to Quassnoi's method:

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

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425291

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

Upvotes: 4

Related Questions