Mysql ignore accents

Question

I am in a project with bootstrap and php + mysql. 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 added recently 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 with or without the accent.

Note. 1: In some cases I pass the variable via URL ($_GET) and in others I do not. So, just in case, I’ve put together a basic function that removes accents... and I need it to be that way.

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

Curiosity: Looking at Phpmyadmin I see "SÃ Paulo".

Finally, I say that I’ve looked long before I decided to ask here.

I count on your help.

Answer 1

We solve the problem defined as tables utf8_unicode_ci

We convert the character input type of 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 the above Query.

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

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

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

Obs: latin1_swedish_ci tbm serves