I've got a table of first and last names
firstname lastname --------- --------- Joe Robertson Sally Robert Jim Green Sandra Jordan
I am attempting to search this table according to a port that includes the entire title. For instance:
input: Joe Robert
I figured about using
SELECT * FROM tablename WHERE firstname LIKE
However the table stores the foremost and surname individually, so I am unsure how you can perform the search within this situation
SELECT * FROM mytable WHERE MATCH(firstname, lastname) AGAINST ("Joe* Robert*" IN BOOLEAN MODE);
This can run considerably faster should you produce a
CREATE FULLTEXT INDEX ON mytable (firstname, lastname)
For that query to have the ability to search rapid names (like
Joe inside your situation), you will need to set the variable
1 before creating the index.
InnoDB you will need to split the search string:
SELECT * FROM mytable WHERE firstname LIKE 'Joe%' AND lastname LIKE 'Robert%'
An alternative choice to Quassnoi's method:
SELECT * FROM mytable WHERE CONCAT(firstname, " ", lastname) = "Joe Robert"