Make a search independent of the order of the keywords

Asked

Viewed 701 times

3

I have a database in which I need to do a search based on the keywords, regardless of the order. See the complete 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 do a search with using LIKE "%joão%cristo%" the result is:

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

To query search only result according to word order, first joão accompanying anything and second cristo. I would like the return to be this way below regardless of the order of the words. See:

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

How would the query to search the database regardless of word order?

php mysql sql
  • 1

    I believe this solves: http://stackoverflow.com/questions/32873328/sql-search-for-words-in-any-order

  • @Miguel took a look at the link, but it’s not solving the problem. The answer there seemed a nice approach, but in my case here is not returning as desired. I will do some more research here.

3 answers

6


1) Change the engine from Myisam table (which is the default), to Innodb.

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

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

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

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

I did a test to see if this was the cake recipe and that’s right. Here’s a print of the test: Query com match against

To return only records that have the two words together, simply add the IN BOOLEAN MODE to the method AGAINST with the operator + in each keyword. See below:

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

To learn more about each operator’s representation, see the documentation for Boolean Full-Text Searches.

  • How so change the "Description column to full text"? Have you detailed your answer better? I tried to do this way but it doesn’t work with me.

  • I edited the answer. See now.

  • 1

    I made an adaptation and it worked out great. This way you are doing it, it will return any record that has one of the two words or the two together regardless of the position they find. In my case I needed to return only the records that have the two keywords together. So I added the "IN BOOLEAN MODE" and the operator + in each keyword. Maybe I didn’t make it explicit, but that’s what I wanted to pass on. If you want to adapt, I validated your answer. Anyway, thank you!

  • All right, I added the in Boolean mode with the +. :)

  • 1

    I changed the answer by leaving what you had done and adding the adaptation and also a link reference. Thanks again. Hugs.

  • 1

    It also helped me because I still didn’t know the 'in Boolean mode'. Valew! Big hug.

Show 1 more comment

0

Try it like this:

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

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

  • holy Christ John
  • holy Christ John

  • It may even partially solve, but I do not consider it very effective considering that there are not only two words, but for example 10 key words. What would be your strategy on this?

0

something like that , mounting a dynamic sql

based on an array of 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 https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html

Browser other questions tagged php mysql sql

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