mysql php UTF-8

Question

There's a strange problem here:

i have to write data from a CSV file into a mysql DB (default charset UTF-8)

when i read the CSV and print the query string on terminal (for debug purpose )the output is correct:

insert into orari (pv_id, day) values(5492, 'giovedì pomeriggio')

but when i run a select query with mysql client the result is weird:

*************************** 273. row ***************************
             id: 28856
             day: giovedÃ¬ pomeriggio
             pv_id: 5760

Same thing if i run the query on phpmyadmin.

The HTML output is correct, the charset is set to UTF-8 and the characters like àòù are correctly visualized.

* Edit * This is my mysql charset and collation settings:

SHOW VARIABLES LIKE 'c%';

| character_set_client     | utf8                                                  |
| character_set_connection | utf8                                                  |
| character_set_database   | utf8                                                  |
| character_set_filesystem | binary                                                |
| character_set_results    | utf8                                                  |
| character_set_server     | utf8                                                  |
| character_set_system     | utf8                                                  |
| collation_connection     | utf8_general_ci                                       |
| collation_database       | utf8_general_ci                                       |
| collation_server         | utf8_general_ci                                       |

yes, if i print mb_detect_encoding($string) after reading from CSV the result is UTF-8, and the echo $string output in terminal is correct (the terminal is for sure utf8: — bicccio
– bicccio, Commented Feb 21, 2012 at 16:57

Treffynnon · Accepted Answer · 2012-02-21 16:44:10Z

3

Each MySQL client should specify the charset they want when they handshake with the server. Unless overidden the default is latin1.

You can issue the following query on every connection to work around this:

SET NAMES 'utf8';

Or you can set it in the MySQL charset configuration as described in the MySQL manual.

edited Feb 21, 2012 at 16:44

answered Feb 21, 2012 at 16:38

Treffynnon

21.6k6 gold badges68 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

bicccio Over a year ago

thx for the light speed answer. I have just tried to run SET NAMES 'utf8'; as a first query after the client connection but the output is the same...

Treffynnon Over a year ago

Are you sure the CSV is actually UTF-8? You can try using mb_detect_encoding to find out.

bicccio Over a year ago

the result of mb_detect_encoding is UTF-8... is the insert query that change in some way the charset, beacuse just after this operation the insert query string is in UTF-8

Treffynnon Over a year ago

This is a curious problem that I have never encountered before. Sorry.

bicccio Over a year ago

thx anyway... the ridiculus things is that if i run utf8_decode on the string just before the read from CSV operation i get a weird terminal outoput,ai correct result from mysql client and a weird HTML output...

Francis Avila · Accepted Answer · 2012-02-21 17:05:36Z

You say that the HTML and the input is correct, but only mysql and phpmyadmin are incorrect. You need to set the proper connection charset for these as well, since they default to latin1 which means "8-bit clean, one byte per char".

(This shouldn't affect your problem, but you should make sure your table or column specify utf-8 storage.)

You'll notice your garbage output is two bytes, which means that what is stored in the DB is definitely UTF8. Your data is being misinterpreted on output.

For the mysql terminal program, start it with the --default-character-set parameter. Make sure it matches your terminal's character set.

I don't know how this is set with phpMyAdmin, but there should be a setting for connection character set as well.

Collectives™ on Stack Overflow

mysql php UTF-8

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related