where field1 + field2 + field3 LIKE“％my_own_string％”？

Question

I have a string, like Elton 1999, and a MySql table with 4 fields :

table(id, name, surname, year)

I have to check if, joining these fields as "single field", it contains all the words of my string.

I mean :

1 Elton John 1999 -> must return the record
2 Elton Peter 1999 -> must return the record
3 Paris Elton 1999 -> must return the record
4 Elto John 1999 -> must not return the record
5 Elton Pierre 2000 -> must not return the record

Can I do it directly with MySql or I need first to get all the records and than parse them on server side? (on PHP, in my case)

P.S. The same result must begin if my string is 1999 Elton, so the order of my words doesnt matter...

Answer 1

As long as your input strings are consistently formatted, you should be able to join the fields together using CONCAT_WS() with a space as the separator and compare them to your input string when querying.