MySQL query to give priority to a LIKE

Question

MySQL query to give priority to a LIKE

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

1

I am writing to you because I would like to make a query in MySQL giving priority to a LIKE. eg: I have a search engine where they put "Old House" ... and I search in the fields subject, document and number the words "old house", "house" and "old" without an ORDER BY

What I want to achieve is to order the query that first shows me the coincidence in "Old house" then those of "home" and then those of "old", can it be done in a query?

Example of the query I do in this case:

SELECT * FROM documentos WHERE (tema LIKE '%Casa vieja%' OR documento LIKE 
'%Casa vieja%' OR numero LIKE '%Casa vieja%' OR tema LIKE '%Casa%' OR 
documento LIKE '%Casa%' OR numero LIKE '%Casa%' OR tema LIKE '%vieja%' OR 
documento LIKE '%vieja%' OR numero LIKE '%vieja%' )

Thank you very much

mysql

asked by Pedro 01.12.2018 в 14:17

source

3 answers

Decrypt Base64 Array in nodejs Error printing XML created in Java

score 1 · Answer 1

1

what's wrong because not at the end of your query you include the order by (field1, field2, camop3)

answered by 01.12.2018 в 14:29

score 1 · Answer 2

Will it serve you as a UNION?

SELECT * 
FROM documentos 
WHERE 
    tema LIKE '%Casa vieja%'
    OR documento LIKE '%Casa vieja%' 
    OR numero LIKE '%Casa vieja%'
UNION
SELECT *
FROM documentos
WHERE
    tema LIKE '%Casa%'
    OR documento LIKE '%Casa%' 
    OR numero LIKE '%Casa%'
UNION
SELECT *
FROM documentos
WHERE
    OR tema LIKE '%vieja%' 
    OR documento LIKE '%vieja%' 
    OR numero LIKE '%vieja%'

Update 1 .

Viewing this post.

link

I was inspired to do something of this style.

SELECT * 
FROM documentos 
ORDER BY
    tema LIKE '%Casa vieja%' DESC,
    documento LIKE '%Casa vieja%' DESC,
    numero LIKE '%Casa vieja%' DESC,
    tema LIKE '%Casa%' DESC,
    documento LIKE '%Casa%' DESC,
    numero LIKE '%Casa%' DESC,
    tema LIKE '%vieja%' DESC,
    documento LIKE '%vieja%' DESC,
    numero LIKE '%vieja%' DESC

score 0 · Answer 3

You can use Order By Fields, and specify the order in which you need that information, in the following link you can find more on the subject: Order by field

The following code

SELECT name, species FROM 'pet'
ORDER BY FIELD(species, 'dog','cat','snake','bird'), name ASC

Would have an exit like the following

|name     | species|
|Bowser   |  dog   |
|Buffy    |  dog   |
|Fang     |  dog   |
|Claws    |  cat   |
|Fluffy   |  cat   |
|Slim     |  snake |
|Chirpy   |  bird  |
|Whistler |  bird  |