Do an independent search of the order of the keywords

Navigation
2

I have a database in which I need to search based on the keywords, regardless of the order. See the full table below: 

+----+-----------------------+
| id |     description       |
+----+-----------------------+
| 1  | joão de santo cristo  |
| 2  | eduardo e mô nica     |
| 3  | santo cristo joão     |
| 4  | cristo santo joão     |
| 5  | juazeiro do norte     |
+----+-----------------------+

When I search with a using LIKE "%joão%cristo%" the result is: 

+----+-----------------------+
| id |     description       |
+----+-----------------------+
| 1  | joão de santo cristo  |
+----+-----------------------+
The query searches only the result according to the order of the words, first joão tracking anything and second cristo . I would like the return to be this way below independent of the word order. See: 

+----+-----------------------+
| id |     description       |
+----+-----------------------+
| 1  | joão de santo cristo  |
| 2  | santo cristo joão     |
| 3  | cristo santo joão     |
+----+-----------------------+

What would the query look like in order to search the database independently of the word order?

    
asked by anonymous 04.03.2017 / 06:05

3 answers

5

1) Change the MyISAM table engine (which is the default) to InnoDB.

2) Add an index of type FULLTEXT in the search field. 

ALTER TABLE 'nometabela' ADD FULLTEXT 'idx_fulltext_nomecoluna' ('nomecoluna');

3) Use query with MATCH ... AGAINST as follows: 

SELECT * FROM nomedatabela WHERE MATCH(description) AGAINST('joão cristo');

I did a test to check if that was the recipe for a cake and that's right. Here is a test print:

Toreturnonlyrecordsthathavebothwordstogether,simplyaddtheINBOOLEANMODEtotheAGAINSTmethodwiththe+operatoroneachkeyword.Seebelow:

SELECT*FROMnomedatabelaWHEREMATCH(description)AGAINST('+joão+cristo'inbooleanmode);

Tolearnmoreabouttherepresentationofeachoperator,seethe Boolean Full-Text Searches .

    
04.03.2017 / 16:41
0

Try this: 

description LIKE "%joão%cristo%" OR description LIKE "%cristo%joão%"

This second condition %cristo%joão% will give you the missing lines in the query:

  • holy christ john
  • cristo santo joão
04.03.2017 / 06:44
0

something like that, building a dynamic sql

based on an array with the words

joao Holy christ 

select * 
from tabela
where (
       description like '%joao%'
       or
       description like '%santo%'
       or
       description like '%cristo%'
      )
order by (sign(instr(description ,'joao')) +
          sign(instr(description ,'santo')) +
          sign(instr(description ,'cristo')))

read also about link

    
04.03.2017 / 13:32
Display / Hide "Terms of Use" by clicking checkbox Porting Visual C ++ code to another language [closed]