Search field with php and mysql. How can I not differentiate special characters?

Question

Search field with php and mysql. How can I not differentiate special characters?

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (-1 votes)

0

I'm having a problem with a search field on a php site with MySql database.

I have a record in the database as "SIPHONE" and when I search as a siphon, sifao or SIFAO does not return this record. I would like you to not case-sensitive and special characters.

The table and records are already converted to latin1 and I am using this collation in the query, as you can see in the code below. Not even that is working.

Follow the code:

  $texto = mysqli_real_escape_string($conn, $_POST['texto']);
  $result = mysqli_query($conn, "SELECT DISTINCT
  produtos.id as id,    
  produtos.nome as nome,
  produtos.descricao as descricao,
  produtos.tags as tags,
  produtos.url as url,
  ambientes.url as AmbienteUrl,
  categorias.url as CategoriaUrl,
  imagens.arquivo as ImagemArquivo
  FROM produtos
      INNER JOIN prod_amb ON prod_amb.produto_id = produtos.id
      INNER JOIN ambientes ON ambientes.id = prod_amb.ambiente_id
      INNER JOIN categorias ON categorias.id = produtos.categoria_id
      INNER JOIN imagens ON imagens.produto_id = produtos.id
  WHERE (produtos.nome LIKE '%".$texto."%' collate latin1_swedish_ci
  OR produtos.descricao LIKE '%".$texto."%' collate latin1_swedish_ci
  OR produtos.tags LIKE '%".$texto."%' collate latin1_swedish_ci)
  AND produtos.situacao = 1 
  AND imagens.principal = 1
  GROUP BY produtos.id");

  while ($row = mysqli_fetch_assoc($result)) {
     echo $row['nome'];
  }

php mysql

asked by anonymous 30.05.2017 / 13:47

3 answers

Names Sequencing Application closes when I try to log in without filling in the fields

score 1 · Answer 1

Man, a solution would be in the query you convert everything to uppercase, or to small, for example:

SELECT * FROM produto WHERE nome = UPPER('nomeProduto')

The UPPER () function transforms the argument passed to upper case, the LOWER () function becomes a lower case. In your case I think it would do it like this:

...WHERE (produtos.nome LIKE UPPER('%".$texto."%')...

As for the accents (ã, à, á) I only see a way that would be doing a function that removes these accents from the characters, when I mechia with PHP I did not know anything that would make it automatic, today it may already exist.

score 1 · Answer 2

Complementing the response of @Victor Hartur de Carvalho

A simple function to remove special characters:

function cleanStr($str)
{
    return preg_replace("/[^a-zA -Z0-9_.]/", "", strtr($str, "ÁÍÓÚÉÄÏÖÜËÀÌÒÙÈÃÕÂÎÔÛÊáíóúéäïöüëàìòùèãõâîôûêÇç", 
                                                             "AIOUEAIOUEAIOUEAOAIOUEaioueaioueaioueaoaioueCc"));
}

score -1 · Answer 3

You need to convert lowercase (or uppercase), bank data, and variables, before comparing:

WHERE (LOWER(produtos.nome) LIKE LOWER('%".$texto."%') collate latin1_swedish_ci
OR LOWER(produtos.descricao) LIKE LOWER('%".$texto."%') collate latin1_swedish_ci
OR LOWER(produtos.tags) LIKE LOWER('%".$texto."%') collate latin1_swedish_ci)