Mysql ignore accents

1

I'm in a project with bootstrap and php + mysql. In it I have a simple search:

$y = mysql_query("SELECT *,date_format('ultimologin','%d/%m às %H:%i') as 'ulogin' FROM $tabela WHERE estado='$estado' COLLATE utf8_general_ci ORDER BY ultimologin DESC LIMIT $limite") or die(mysql_error());

This "COLLATE" I recently added in an attempt to solve the following problem:

When, for example,

$estado='São Paulo'; 

It lists normally. But when

$estado='Sao Paulo';

Nothing is returned.

I want you to get the result both with and without the accent.

Obs. 1: In some cases I pass the variable via URL ($ _GET) and in others it does not. So, by assurance, I set up a basic function that removes the accents ... and it needs to be that way.

Obs. 2: The table and fields are in utf8_general_ci as well as the page (which is utf-8).

Curiosity: Looking there in PhpMyAdmin I see "So Paulo".

Finally, I say that I've searched a lot before deciding to ask here.

I'm counting on your help.

    
asked by anonymous 25.09.2016 / 17:19

1 answer

3

We solved the problem defined by the tables as utf8_unicode_ci

We convert the type of character input from the table

ALTER TABLE 'tabela' DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Then we convert the data that already exists to the same encoding used in Query above.

ALTER TABLE 'tabela'
CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

or even in "gambiarra" character you make the query using collate

Select * from TABELA where CAMPO like '%texto_para_encontrar%' collate utf8_general_ci

obs: latin1_swedish_ci tbm serves

    
25.09.2016 / 22:43