php - MySQL error on special chars -

i have mysql database hosted in provider. running ok last 3 years somehow since yesterday special characters show invalid chars. instance :

'sugestão' shows 'sugestão' 

i did not make changes in database , host company claims can't nothing help. , stranger yet, new inclusions not being affected problem, data included prior yesterday.

i know happens in host back-end, refuse admit because claim don't have logs prove did not change db charset or collation. of course, know did not this, app running 3 years , never had problem.

so i'm on own fix problem updating columns kind of problem manually, in each table. there way faster inside mysql workbench itself, instead of creating app ?

for instance, have table called 'crm'. here example result set :

select * crm; cr_date     cr_history 2016-07-11  quer sugestão  2016-07-11  país de destino : canadá 

here how result set looked before problem , how need fix again :

select * crm; cr_date     cr_history 2016-07-11  quer sugestão  2016-07-11  país de destino : canadá 

is there way update query change 'ã' strings inside column 'ã', keeping rest of column content intact ?

something : replace ocurencies o 'ã' in field, 'ã'. php's str_replace directly in mysql.

just add more information, db it's accessed php application, problem happens accessing db directly mysql workbench, it's not php related problem.

thanks in advance !

this typical (and seen) result when stored utf-8 data in ansi (or other non-utf) column. php seems prone type of error (i've seen several times before). so, when retrieving data interpreted in encoding of column instead of true encoding - hence wrong display.

you can see this:

mysql> select cast(_latin1'müller' char character set utf8); +---------------------------------------------------+ | cast(_latin1'müller' char character set utf8)  | +---------------------------------------------------+ | müller                                           | +---------------------------------------------------+ 1 row in set (0,00 sec)  mysql> select cast('müller' char character set utf8); +--------------------------------------------+ | cast('müller' char character set utf8)  | +--------------------------------------------+ | müller                                     | +--------------------------------------------+ 1 row in set (0,00 sec) 

what can set right encoding (charset in (my)sql) column. use alter table command, mess existing values further (it try re-encode utf-8 encoded values again in utf-8). better approach this:

• add new column utf-8 charset + collation.
• update table , set values of new column values of old colum. important here to cast old column's charset true value (utf-8). not convert data store existing bytes in new column, time correct encoding.
• once done remove old column , rename new 1 old name.

with additional column make sure don't lose data (a backup still recommendable) , can drop , start on if goes wrong.