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

Question

I’m having a problem with a search field on a php site with Mysql database.

I have a record in the bank as "SIPHON" and when I search as siphon, siphon or SIPAO does not return this record. I wish you wouldn’t differentiate between upper/lower case 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. It’s not even working.

Follows 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'];
  }

Answer 1

Complementing @Victor Hartur de Carvalho’s reply

a simple function to remove the special characters:

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

Answer 2

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

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

the UPPER() function transforms the argument passed to uppercase, the LOWER() function transforms into a letter. In your case I think I could do it like this:

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

As for the accents (ã, à, á) I only see a form that would be doing a function that removes these accents from the characters, when I move with PHP I didn’t know anything that made it automatic, today it may already exist.

Answer 3

You need to convert the database data and variables into lower case (or upper case), before making the comparison:

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)