How to compare data with accents in the database?

Question

How to compare data with accents in the database?

Navigation

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

5

I am having the problem in a search I made in that user filter content by district and county, it works fine when I choose district and county without accents but when I choose a district and a county that contains accents does not give me any values I would like how do I resolve a 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

asked by anonymous 19.02.2015 / 01:19

3 answers

Use static Closures or Closures in PHP? how to fill the stars of a star rating?

score 2 · Answer 1

The ideal solution is for BD to be accent insensitive. Is changing the format feasible?

It may require repopulating the database and giving work, but this problem has happened to me in the past and doing workarounds is a nightmare, as new situations arise that cause problems.

score 0 · Answer 2

I do not know what exactly it would look like in php, but ... there's a suggestion.

Assemble an array with all the characters you want to evaluate: Special Characters = ['Á', 'á', 'É', 'é', .. 'Ç', 'ç'];
Compare each character of your string with your CharacterSpecs and make the necessary changes.

score 0 · Answer 3

I recommend you take a look at the PHP documentation and look for the utf8_encode and utf8_decode functions. When sending a string to BD, force the correct encoding on it with utf8_encode and when it will retrieve 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!";