MYSQL LIKE on 2 columns?

0
votes

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'
1
phpmysql
Expand the question to show how Johnny Bishop should be searched. - danblack
you could build a php function that extracts de forename - Antonio Abrantes
Side note: Learn to use parameterized queries. - sticky bit

1 Answers

1
votes

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