How to search in 5 different fields with all possible options not defined in MySQL using LIKE

Question

I have a database that contains 5 fields that are: name, address, idcard, city, street

Then I have a PHP request that takes one parameter. In this parameter you can enter any fields that you like, and the results should be accurate.

For example, if I enter john doe skate in the search box, you will get all the records whose name is John Doe, and they live on the street known as Skate Street.

Also note that the name is stored in one field in the SURNAME NAME order (I cannot change this because it is necessary, so please do not tell me that it is better to change it ... I know that :)). Obviously, I would like the name to be entered in any order, be it NAME SAMEAME or SAMEAME NAME.

My idea is to first attach the first two parameters entered as a name. I will tell my client that the first 2 parameters should be first and last name, last name and first name. Following the name, he can then enter any of the attributes of the order in any order, for example,

They will be accepted:

name surname idcard town street
surname name idcard town street
name surname town idcard street
surname name address street idcard

They are not accepted:

idcard town name surname street
town name surname idcard

for the simple reason that we make the request too complicated (due to the reason for the name / surname and the fact that they are in the same field)

If there is a way, as the latter is possible without a lot of complications, I would also like to hear it.

Answer 1

, , , , , MySQL. , .

, "People Name Fake Street" :

SELECT * FROM Users
WHERE (FirstName LIKE "%Persons%"
OR LastName LIKE "%Persons%"
OR Address LIKE "%Persons%")
AND (FirstName LIKE "%Name%"
OR LastName LIKE "%Name%"
OR Address LIKE "%Name%")
AND (FirstName LIKE "%Fake%"
OR LastName LIKE "%Fake%"
OR Address LIKE "%Fake%")
AND (FirstName LIKE "%Street%"
OR LastName LIKE "%Street%"
OR Address LIKE "%Street%")

, , . . , - . "" ( )

MySQL Full Text , . .

, Lucene. , , . , , , . .

( ), , .

Answer 2

, , :

imput paramater " idcard city street", :

psudocode
---------
where
name+address+idcard+town+street like %input_token1% or
name+address+idcard+town+street like %input_token2% or
...
name+address+idcard+town+street like %input_token5%

Answer 3

, Person.

:

preg_match_all('/(\S+)*\s+/im', $input, $value, PREG_PATTERN_ORDER);
for ($i = 0; $i < count($value[0]); $i++) {
    # Matched text = $result[0][$i];
}

a script, SQL Server. MySQL, MySQL, .

The idea is to join the input table for each column that you want to find. The proposal wheretakes care that the input values ​​are at least in the number of columns, since there are input values.

DECLARE @Table TABLE (Name VARCHAR(20), Surname VARCHAR(20), Address VARCHAR(20), IDCard VARCHAR(20), Town VARCHAR(20), Street VARCHAR(20))
DECLARE @Inputs TABLE (Value VARCHAR(32))

INSERT INTO @Table VALUES ('Doe', 'John', 'Dontknow', 'Dontknow', 'US', 'Skate')
INSERT INTO @Inputs VALUES ('%John%')
INSERT INTO @Inputs VALUES ('%Doe%')
INSERT INTO @Inputs VALUES ('%Skate%')

SELECT  t.*
FROM    @Table t
        LEFT OUTER JOIN @Inputs i_name ON t.Name LIKE i_name.Value     
        LEFT OUTER JOIN @Inputs i_surname ON t.SurName LIKE i_surname.Value        
        LEFT OUTER JOIN @Inputs i_address ON t.Address LIKE i_address.Value
        LEFT OUTER JOIN @Inputs i_idcard ON t.IDCard LIKE i_idcard.Value
        LEFT OUTER JOIN @Inputs i_town ON t.Town LIKE i_town.Value
        LEFT OUTER JOIN @Inputs i_street ON t.Street LIKE i_street.Value
        CROSS APPLY (SELECT inputCount = COUNT(*) FROM @Inputs) cnt
WHERE   cnt.inputCount <= 
          CASE WHEN i_name.Value IS NULL THEN 0 ELSE 1 END
          + CASE WHEN i_surname.Value IS NULL THEN 0 ELSE 1 END
          + CASE WHEN i_address.Value IS NULL THEN 0 ELSE 1 END
          + CASE WHEN i_idcard.Value IS NULL THEN 0 ELSE 1 END
          + CASE WHEN i_town.Value IS NULL THEN 0 ELSE 1 END
          + CASE WHEN i_street.Value IS NULL THEN 0 ELSE 1 END