Search code examples
phpmysqlsqlsql-like

sql like query fullname from three columns

I have a textfield which takes in full name example: michael peter johnson in my table i have three columns firstName , middlename and lastname I have written this query but it returns empty 

SELECT firstName,middleName,lastName
FROM staff
WHERE firstName LIKE "%michael peter johnson%"
OR middleName LIKE "%michael peter johnson%"
OR lastName LIKE "%michael peter johnson%"

if a user types in son it should display michael peter johnson, since son is contained in johnson.

thanks my table : firstName has (michael) middleName has (peter) lastName (johnson)

Solution

  • You're checking for any string that includes all of michael peter johnson in it.

    You need three separate statements, with different comparisons...

    WHERE
   firstName  Like '%michael%'
or middleName Like '%peter%'
or lastName   Like '%johnson%"

    You could reverse the logic...

    WHERE
   'michael peter johnson' LIKE '%' + firstName  +'%'
or 'michael peter johnson' LIKE '%' + middleName +'%'
or 'michael peter johnson' LIKE '%' + lastName   +'%'


    You also give an example where the search is for 'son'. Your existing code will work for that. It's just a 'problem' when you compare each individual part of the name against a parameter that has all of the name.

    So, maybe you want both version together?

    WHERE
   'michael peter johnson' LIKE '%' + firstName  +'%'
or 'michael peter johnson' LIKE '%' + middleName +'%'
or 'michael peter johnson' LIKE '%' + lastName   +'%'
or firstName  LIKE '%' + 'michael peter johnson' + '%'
or middelName LIKE '%' + 'michael peter johnson' + '%'
or lastName   LIKE '%' + 'michael peter johnson' + '%'

    You just need to decide exactly what behaviour you want, and then work it through.