Search for terms out of order php MYSQL

Question

Hello folks was doing a search around here and would like to know how do I search for out-of-order terms

example I have a table with the following name: name_prod

in it is an example of a name:

BUSINESS CARD - 4X0- COUCHE BRIGHTNESS 250GRS / ROUNDED CORNER

I wish I could do a search where I could search for example:

cardboard 4x0 rounded couche

and the result appears, is there any way to do this? follows my code:

<?php
// Incluir aquivo de conexão
include("Connections/conn2.php");

// Recebe o valor enviado
$valor = $_GET['valor'];

// Procura titulos no banco relacionados ao valor
$sql = $conn1->query("SELECT * FROM cad_produtos WHERE nome_prod LIKE '%".$valor."%' OR codigo_prod LIKE '%".$valor."' OR tipo_prod LIKE '%".$valor."' ORDER BY codigo_prod ASC");


echo "<div class=\"row\" style=\"justify-content:center;\">\n";
if ($sql) {
while ($prod = $sql->fetch_object()) { 

    $variavel = $prod->tipo_prod;
    $imgse = $conn1->query("SELECT `arquivo` FROM cad_prodrutos_tipo_prod WHERE tipo_prod='$variavel' "); 
    if ($imgse->num_rows > 0) { 
    while($imgrow = $imgse->fetch_object()) {



$variavel_limpa = strtolower( preg_replace("/[ -]+/", "-", strtr(utf8_decode(trim($variavel)), utf8_decode("áàãâéêíóôõúüñçÁÀÃÂÉÊÍÓÔÕÚÜÑÇ"),"aaaaeeiooouuncAAAAEEIOOOUUNC-")) );

        $valor = $prod->valor_uni;

$Preco = number_format($valor,2,",",".");

    ?>

<?php

    echo "     <div class=\"card_prod card_prod--19\" >\n";
echo "        <div class=\"card_prod__header card_prod__header--19\">\n";
echo "          <div class=\"card_prod__watermark\" data-watermark=\"plus\"></div>\n";
echo "\n";
echo "          <img src=\"assets/img/brand/logo.png\" alt=\"Nike\" class=\"card_prod__logo \">\n";
echo "\n";
echo "             <span class=\"card_prod__price \">R$ $Preco </span>\n";
echo "\n";
echo "          <h1 class=\"card_prod__title text-white \">$prod->nome_prod</h1>\n";
echo "          <span class=\"card_prod__subtitle text-uppercase\">$prod->desc_prod</span>\n";
echo "\n";
echo "        </div>\n";
echo "\n";
echo "        <div class=\"card_prod__body\">\n";
echo "          <img src=\"upload/$imgrow->arquivo\" alt=\"Nike 19\" class=\"card_prod__image\">\n";
         }
 } else {
    echo "<center>"."<h1>"."Não há produtos!"."<div>"."<small class="."text-muted".">"."Cadastre um para visualizar!"."</small>"."</div>"."</h1>"."</center>";
 }

echo "\n";
echo "          <span class=\"card_prod__category \">COD:$prod->codigo_prod</span>\n";
echo "            <span class=\"card_prod__category \">$prod->tamanho1_prod cm X $prod->tamanho2_prod cm</span>\n";
echo "             <span class=\"card_prod__category \">$prod->tipo_prod</span>\n";
echo "        </div>\n";
echo "      </div>\n";



}
}else{

    die($conn1->error);
}




?>

Answer 1

For this it is necessary to make a filter with LIKE. To make match in parts and have more than one word in the filter you can use the % where you want to accept parts that do not match.

ex: SELECT * FROM tabela WHERE coluna LIKE %TERMO_1%TERMO_2%

By doing this filter SQL will match lines where the content is TERMO_1 E TERMO_2, and may be at the beginning, middle or end of the content.

Considering your query, it would be necessary to replace the spaces by %: SELECT * FROM cad_produtos WHERE nome_prod LIKE '%".str_replace(" ", "%", $valor)."%' OR codigo_prod LIKE '%".$valor."' OR tipo_prod LIKE '%".$valor."' ORDER BY codigo_prod ASC

---

As requested in the comments, follow alternatives to get the terms out of order.

1st alternative Create several LIKE with AND. Works similarly to the previous one, but creates various search conditions.

ex:

$busca_termos = "nome_prod LIKE '%" . str_replace(" ", "%' AND nome_prod LIKE '%", $valor) . "%'";
SELECT * FROM cad_produtos WHERE (".$busca_termos.") OR codigo_prod LIKE '%".$valor."' OR tipo_prod LIKE '%".$valor."' ORDER BY codigo_prod ASC

2nd alternative This would be the most "elegant" alternative, but may require changes to your table and even base, to enable the FULL TEXT SEARCH.

SELECT *
FROM cad_produtos
WHERE
  match(nome_prod) against ('+TERMO_1 +TERMO_2' in boolean mode)
  OR codigo_prod LIKE '%".$valor."'
  OR tipo_prod LIKE '%".$valor."'
ORDER BY codigo_prod ASC

Note that in all examples, the search was changed only in the column nome_prod.

PS: This answer does not take into account other points that may be relevant to the realization of queries with user inputs, such as "how to avoid SQL Injection". I recommend that you search in Sopt itself about questions related to the theme.