How to make SQL query using like comparator and various values out of order

Question

I looked for answers here in the community, but I couldn’t find anything specific. I have a page where I can search the name of previously registered clients in a Mysql database. Currently, I am using the following sentences:

PHP: $valor = str_replace(' ', '%', $pesquisa);

SQL: "select * from user where nome like '%".$valor."%' order by nome asc;"

Being that PHP variable $research, comes from a search form on the same page. (The above code allows me to do searches with more than one value). For example, if there is a client in the database called Luiz Henrique da Silva, and I search for Luiz, Luiz Henrique, or even Luiz Silva, this code will work perfectly, listing such a user.

However, if I search the name out of order, like Silva Henrique, or da Silva Luiz, the user will not be listed. How could this reverse search be possible in a simple way?

Answer 1

Syntax of 'like' in SQL:

SELECT (campos) FROM (tabelas) WHERE campo1 LIKE "%variavel%";

In the context you want to use it would look more or less like this:

$comando = "SELECT * FROM user WHERE nome LIKE '%$valor%' ORDER BY nome ASC;"

But in order to find the log Luiz Henrique da Silva looking for Luiz Silva, wouldn’t, only if you had two different variables and you could do something like this in your SQL query:

SELECT (campos) FROM (tabelas) WHERE campo1 LIKE "%Luiz%" AND campo1 LIKE "%Silva%";

And so the record would appear Luiz Henrique da Silva.

I hope I’ve helped in some way.