As a search for any term removing spaces, traces and points with LIKE

Asked

Viewed 216 times

3

I’m having a hard time changing a search that I have today by searching a code that has, for example this mask A 901.325.0744 the search is correct because the code is registered in this way and the search is now done without the point, trace and space, but how can I concatenate the LIKE in my query making the search find the product by any term, I tried some alternatives and it did not work.

What I have is this, my initial SQL:

// PESQUISA INICIAL
$sql = "   
SELECT 
      produtos.codigo,
      produtos.nome,
      produtos.lancamento,
      produtos.id_categoria,
      produtos.id_produto,
      produtos.data_inicial,
      produtos.data_expiracao                             
    FROM
      `produtos`      
    WHERE
      (produtos.id_idioma = '".$_SESSION['idioma']."') AND
      (produtos.`status` = 1)
  ";

To query dynamics I created to concatenate the searches:

$sqla = " AND REPLACE(REPLACE(REPLACE(codigo_original,'.',''),'-',''),' ','')  = REPLACE(REPLACE(REPLACE('".$pesquisa."','.',''),'-',''),' ','') OR
              REPLACE(REPLACE(REPLACE(codigo_original1,'.',''),'-',''),' ','') = REPLACE(REPLACE(REPLACE('".$pesquisa."','.',''),'-',''),' ','') OR
              REPLACE(REPLACE(REPLACE(codigo_original2,'.',''),'-',''),' ','') = REPLACE(REPLACE(REPLACE('".$pesquisa."','.',''),'-',''),' ','') OR
              REPLACE(REPLACE(REPLACE(codigo_original3,'.',''),'-',''),' ','') = REPLACE(REPLACE(REPLACE('".$pesquisa."','.',''),'-',''),' ','')";

The search with the concatenated fields:

if ($sqla != "") {
    $sql = $sql . $sqla . $ordenacao; 
} else {
    $sql = $sql . $ordenacao;
}

Some data from the table:

inserir a descrição da imagem aqui

As an example, I would like the user to search 411, term that is part of the original code column and original code_1 were displayed these records in the search result.,

php mysql sql database
  • 1

    Can provide examples of your data so we can provide a functional response?

  • Hi @Sorack What exactly you need?

  • 1

    It puts about 3 lines with data from your table and the term you’re researching that would return one of them. So I can suggest substitutions or even an alternative way to solve your problem

  • 1

    In your example the other 2 records would not be shown?

  • Yes, they would be shown in the search result.

3 answers

4

I think the best way to do this is to remove the spaces and points in the backend before submitting the term to the SQL query. And in the query, you do the same with the field, removing the spaces and points. Making the appropriate adaptations to your example, would be so:

SELECT *
FROM `sua_tabela`
WHERE REPLACE(REPLACE(nome_do_campo,' ',''),'.','') = '$pesquisa_formatada'

Where the variable $pesquisa_formatada is the term searched without the spaces and points.

This way, it doesn’t matter if the user has typed A 901.325.0744 or A901.325.0744 or A9013250744 the consultation will always work.

  • 1

    I’ll test your tip, thanks @Alexandrepaiva.

  • Let me know if it’s working or if you still have any questions.

  • Hello @Alexandrepaiva, thanks for the post, but was already doing it.

2


The first suggestion I give is that you create a function to remove special characters from the text:

DELIMITER $
CREATE FUNCTION remover_especiais(texto VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
 DECLARE resultado VARCHAR(20);
 
 SET resultado = REPLACE(texto, '.', '');
 SET resultado = REPLACE(resultado, '-', '');
 SET resultado = REPLACE(resultado, ' ', '');
  
 RETURN resultado;
END

After that, to use on PHP just make the call as follows:

$sqla = " AND remover_especiais(codigo_original) LIKE CONCAT('%', remover_especiais('".$pesquisa."'), '%') OR
              remover_especiais(codigo_original1) LIKE CONCAT('%', remover_especiais('".$pesquisa."'), '%') OR
              remover_especiais(codigo_original2) LIKE CONCAT('%', remover_especiais('".$pesquisa."'), '%') OR
              remover_especiais(codigo_original3) LIKE CONCAT('%', remover_especiais('".$pesquisa."'), '%')";

You can see the query operating in the DB Fiddle.

CONCAT

For quoted strings, concatenation can be performed by placing the strings next to each other.

In free translation:

For highlighted strings, concatention can be performed by placing the strings one after the other.

  • Help me, where and how I use this function, where I declare to remove characters?

  • 1

    Just run it in your bank. The call is made as in WHERE I gave an example. I asked another question in your question, take a look there.

  • For new registered products I must perform this function?

  • 1

    @adventistapr only needs to run it in the search. I added an example to DB-Fiddle and includes the link in my reply.

  • forgive my ignorance, but I still don’t understand how the execution of this function is done in the search, this code should be inserted in my SQL?

  • 1

    @adventistapr the creation of the function yes. The call is made in the WHERE as in the example

  • @adventist managed?

  • 1

    Hello @Sorack, yes, it was a super tip, had never done a function like this, stayed show.

Show 3 more comments

2

My dear, try this way, if it doesn’t work let me know to look for another solution and if it meets the characters, if they are really in utf8

$palavras = explode(' ', $pesquisa);
$sqla = "";
foreach($palavras as $indice => $valor){
        $sqla .= " AND (`codigo_original` LIKE '%".$valor."%') OR
        (`codigo_original1` LIKE '%".$valor."%') OR
        (`codigo_original2` LIKE '%".$valor."%') OR
        (`codigo_original3` LIKE '%".$valor."%')";
    }
  • 1

    Opa, I’ll try it too, thanks @Joaopauloaraujo

Browser other questions tagged php mysql sql database

You are not signed in. Login or sign up in order to post.