Gustavo Porto Gustavo Porto - 7 months ago 10
SQL Question

Priority of SQL Query with multiple OR

I have two columns:
name, surname (name, lastname)

In a query, try the full name (ex.: Steve Jobs), then I divide the words into the variable (in php) and do the query:

SELECT * FROM `usuarios` WHERE
(`nome` REGEXP '$bt1 $bt2') OR
(`nome` REGEXP '$bt1') OR
(`sobrenome` REGEXP '$bt1') OR
(`sobrenome` REGEXP '$bt1 $bt2')


The problem is that the display of the results are not following the order of queries, then ends up returning:

Steve John
John Jobs
Steve Jobs


How do I prioritize the query and return in the case Steve Jobs?

KM. KM.
Answer

try this:

          (SELECT '1' as sb, usuarios.* FROM usuarios WHERE nom` REGEXP '$bt1 $bt2')
UNION ALL (SELECT '2' as sb, usuarios.* FROM usuarios WHERE nome REGEXP '$bt1')
UNION ALL (SELECT '3' as sb, usuarios.* FROM usuarios WHERE sobrenome REGEXP '$bt1')
UNION ALL (SELECT '4' as sb, usuarios.* FROM usuarios WHERE sobrenome REGEXP '$bt1 $bt2')
ORDER BY sb