MYSQL LIKE on 2 columns?

Question

I have a table called users, which as 2 columns forename and surname

i have created a search input, and wondered how i can join the forename and surname column when searching.

For example if my table has the forename Johnny and the surname Bishop

if the user searched for Johnny or Bishop then it will show the result, however if the user types the full name - Johnny Bishop no results show.

My SQL looks like

AND u.forename LIKE '%$name%' OR u.surname LIKE '%$name%' OR u.email = '$name' 

Answer 1

A simple solution would be to use CONCAT() to concatenate forename and username and search based on the result.

AND u.forename LIKE '%$name%' 
OR u.surname LIKE '%$name%' 
OR CONCAT(u.`forename`, " ",u.`surname`) LIKE '%$name%' 
OR u.email = '$name' 

If you want to include cases where the user typed multiple white spaces you could use REPLACE() to remove all whitespaces

OR REPLACE(CONCAT(u.`forename`,u.`lastname`),' ','') LIKE CONCAT('%',REPLACE('$name',' ',''),'%')