How to compare data with accents in the database?

Asked

Viewed 280 times

5

I’m having a problem with a search I did where the user filter content by district and county, is working well when I choose district and county without accents but when I choose a district and a county that contains accents does not return me any values I would like to know how I can resolve situation ?

PHP

$result_categoria = mysql_query("select * from categorias_estabelecimentos where      categoria_slug='".$categoria."'");
while($row_categoria = mysql_fetch_object($result_categoria)){
$result_local=mysql_query("select * from estabelecimentos where id='".$row_categoria->estabelecimento_id."' and distritos='".$distritos."' and concelhos='".$concelhos."' and activo=1");
while($row_local=mysql_fetch_object($result_local)){
    if(mysql_num_rows($result_local)>0){
        $result_anexo_local=mysql_query("select * from estabelecimentos_anexos where id_mae='".$row_local->id."' and seccao='thumbnail'");
        $row_anexo_local=mysql_fetch_object($result_anexo_local);
php mysql

  • What is the collation used in your table?

  • utf8_unicode_ci

  • Try with LIKE then: ... and distritos LIKE '".$distritos."' and concelhos LIKE '".$concelhos."' and ...

  • I tried like I said Didn’t solve

  • What is the character set? utf-8?

  • Try implementing this function in your https://gist.github.com/williamurbano/f4c8d23a0e0d21622334database

Show 1 more comment

3 answers

2

The ideal solution is for BD to be Accent insensitive. Changing the format is feasible?

It may require repopulating the database and give work, but I have happened this problem in the past and go doing workarounds is a nightmare, because new situations arise that cause problems.

0

I recommend you take a look at the PHP documentation and look for the functions utf8_encode and utf8_decode. When sending a string to BD, force the correct encoding on it with utf8_encode and when retrieving a string, apply utf8_decode before performing the comparison.

Example:

$string_acentuada = "Olá!";
$string_utf8 = utf8_encode($string_acentuada);
echo $string_acentuada." - Encoding do sistema\r\n";
echo $string_utf8." - Encoding UTF8\r\n";
if(utf8_decode($string_utf8) == $string_acentuada)
    echo "São iguais!";

0

I don’t know how it would be exactly in php, but... will a suggestion.

  • Create an array of all the characters you want to evaluate: Specific characteristics = ['Á', 'á', 'É', 'é', .. 'Ç', 'ç''];

  • Compare each character of your string with its Special Features and make the necessary changes.

Browser other questions tagged php mysql

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