I am setting up a job agency website and I have a search that looks in the field title and description, what the person searched, as per the query below:
SELECT *
FROM vagas
WHERE (titulo LIKE '%termo_de_busca%' OR observacoes LIKE '%termo_de_busca%')
The query works fine and searches the 2 columns, but I wanted to bring it first, bring the results that the "term_de_search" has in the title column and then the description column.
How can I do this?
You will have to do for subquery :
SELECT * FROM
(
(SELECT * FROM vagas
WHERE titulo LIKE '%termo_de_busca%')
UNION ALL
(SELECT * FROM vagas
WHERE observacoes LIKE '%termo_de_busca%')
) as apelido
You're simply making 2 selects , putting them together, and making select on them. Finally, by naming this select of apelido .
Important : Whenever you do a union ( UNION ) the 2 tables must have the same fields.
As stated by @RicardoPontual in the comments, there may be a ordering
UNION.
If you are not ordering correctly, or want to be sure that it will be, create a tipo field with the relative value:
SELECT * FROM
(
(SELECT *, 1 as tipo FROM vagas
WHERE titulo LIKE '%termo_de_busca%')
UNION ALL
(SELECT *, 2 as tipo FROM vagas
WHERE observacoes LIKE '%termo_de_busca%')
) as apelido
ORDER BY tipo
The results of select by titulo the value of tipo will be 1 and observacoes will 2 , so you can sort them.