Problems with ORDER BY in MY SQL search

Question

Good morning guys, how are you?

I have a little problem to get a more "advanced" search with my MYSQL, you can help me?

I created a search as follows

SELECT idproduto, descricao, vconsumidor FROM produtos WHERE descricao LIKE '%$descricao%' OR marca LIKE '%$descricao%'"

Let’s get to the first problems

take the example: when searching NORTON it appears all search marks BRAND

but if I place 'PO NORTON' or 'NORTON PICKS UP PO' it does not return anything, because PO PICKS is in the description and NORTON in the brand

until then I decided as follows

    $descricaoalterada = str_replace(' ', '||', $descricao);
    //pega a descrição e altera todos os espaços por ||


    $d=explode("||", $descricaoalterada );
    //faz um array, com uma coluna pra cada || do resultado de cima

    $texto = "";

    for ($i=0; $i < count($d); $i++){
        $texto = $texto."OR descricao LIKE '%".$d[$i]."%' ";
        //para cada coluna do array ele cria no $texto um novo OR descricao LIKE %%
        //exemplo OR descricao LIKE %PEGA% OR descricao LIKE %PO% OR descricao LIKE %NORTON%
    } 
    
    $sqlT = "SELECT idproduto, descricao, vconsumidor FROM produtos WHERE descricao LIKE '%$descricao%' 
    ".$texto."OR marca LIKE '%$descricao%'";
    //exemplo impresso: SELECT idproduto, descricao, vconsumidor FROM produtos WHERE descricao LIKE '%PEGA PO NORTON%' OR descricao LIKE %PEGA% OR descricao LIKE %PO% OR descricao LIKE %NORTON% OR marca LIKE '%PEGA PO NORTON%'
    $resultT=mysqli_query($conexao,$sqlT);

was cool, returned EVERYTHING HAS GRIP PO NORTON in the result, but it appears first description, an example is PAINT 'PO'LISTER that comes before in the description IS PRINTING LIKE THIS: POLYESTER PAINT MAGPIE

How could I first make him return the query first? example all NORTON PO PICKS, then appear everything that has PO or GRIP or NORTON? as in this example:

MAGPIE

POLYESTER PAINT NORTON SANDPAPER ETC

If you have other better ways to display this result would be very grateful!! thanks in advance for the cooperation!!

Answer 1

The LIKE doesn’t help much in this, so a solution would be:

• Make two different queries and use UNION to join the results, and with a "priority column";
• Sort by that priority.

I can’t mount a functional example now, but as your query is ready it should be very consistent example (I will use the final query, just adapt in PHP):

SELECT 1 AS  prioridade, idproduto, descricao, vconsumidor 
  FROM produtos 
 WHERE descricao LIKE '%PEGA PO NORTON%'
UNION
SELECT 2 AS prioridade, idproduto, descricao, vconsumidor 
  FROM produtos 
 WHERE descricao LIKE %PEGA% OR descricao LIKE %PO% OR descricao LIKE %NORTON% OR marca LIKE '%PEGA PO NORTON%'
ORDER BY prioridade

Note that the first query has priority 1 and the second priority 2, which will make the results bring first the results of the first query because of the ORDER BY. If you need to change the order, just move the criteria to the first query, or add more if applicable.

This works but may need to use ( or a SELECT * FROM (... AQUI VÃO AS QUERIES..) ORDER BY prioridade if you are not ordering correctly