Search name by first and last name

Asked

Viewed 925 times

1

I’m having a hard time designing a query Mysql to search for a name, and the person can type the first and last name and accept to have another name between them. For example:

Search: 'JOSÉ NUNES' or 'NUNES JOSÉ'

Result: JOSÉ SILVA NUNES

The way I research today is basically:

SELECT * FROM pessoas WHERE nome LIKE %pesquisa%

I read something about REGEXP, but I could not use functionally and did not want to repeat several "AND WHERE nome LIKE..." because I use PDO, so it complicates a little.

php mysql pdo

  • The column nome stores the full name?

  • @Andersoncarloswoss yes

3 answers

0

Try it this way:

SELECT * FROM pessoas WHERE nome LIKE %$nome%$sobrenome%

Assuming you have separate fields for the name and surname

0

Try to replace the spaces by replacing them with the joker:

php:

 str_replace (' ' , '%' , $pesquisa )

mysql 

REPLACE(pesquisa,' ','%')

Editing:

use the RLIKE command, but it can return unwanted records. Example:

SELECT * FROM pessoas WHERE nome RLIKE REPLACE('JOSÉ NUNES',' ','|')

This query will return the equivalent of: 

SELECT * FROM pessoas WHERE nome LIKE %JOSE% OR nome LIKE %NUNES%

  • I just tried that, but in case I search the person’s last name first, and then the name, I get no results. I wanted some similar result when I use several AND LIKE name ...

  • can work with arrays, giving a split in the variable search where there are spaces ?

  • Yes, I can do it

  • and can assemble the query string into a loop in php ?

  • with you, but the difficulty of putting the AND name LIKE... is that I use PDO, then I have a bindValue to replace a ":name" inside the query, you understand?

  • got it! look at my answer below :)

Show 1 more comment

0


I got it using RLIKE, like this:

SELECT * FROM person WHERE name RLIKE' (?=. *Silva)(?=. *Jose)'

so it doesn’t matter the order, nor if there’s anything between them :D

  • and if the user enters 3 names ?

  • actually, what I did in the code was to use a foreach giving explodes in the name: $nameSestopped = explodes(" ", $name); $sqlNome = ""; foreach ($namePath $n){ $sqlNome .= "(?=.*".$n.")" ; }

  • 1

    Well rsrs...blz congratulations.

Browser other questions tagged php mysql pdo

You are not signed in. Login or sign up in order to post.