How to search for a name in two columns in MySQL? I want to search a name 'Hamed Haddadi'. 'Hamed' is first name and 'Haddadi' is the last name of the user. When I search individually in the first name and last name columns it shows a record.

My query is: 

SELECT * 
FROM nbaplayermaster 
WHERE firstname LIKE "%Hamed%" OR 
      lastname LIKE "%Haddadi%";


But whenever I search his name in two columns, it shows an empty result set in MySQL.

My query is:

SELECT * 
FROM nbaplayermaster 
WHERE firstname LIKE "%Hamed Haddadi%" OR 
      lastname LIKE "%Hamed Haddadi%";


I want to search his full name in two columns.
I suspect that Hamed is the first name and Haddadi is the last name, you can do it in 3 ways;

Solution 1:

Split the query in name and last name:

SELECT * 
FROM nbaplayermaster 
WHERE firstname LIKE "%Hamed%" OR 
      lastname LIKE "%Hamed%";


Solution 2:

Use the wildcard % in between the names(spaces)

SELECT * 
FROM nbaplayermaster 
WHERE firstname LIKE "%Hamed%Haddadi%" OR 
      lastname LIKE "%Hamed%Haddadi%";


Solution 3:(recommended)

Add a fulltext index on this 2 columns:

ALTER TABLE nbaplayermaster ADD FULLTEXT KEY `full_name` (`firstname`,`lastname`);


and then search using the fulltext index

SELECT * FROM nbaplayermaster WHERE MATCH(firstname,lastname) AGAINST ('Hamed Haddadi');