UTF-8 Database Problem

Question

I've a MySQL table that has a UTF-8 charset and upon attempting to insert to it via a PHP form, the database gives the following error:

PDOStatement::execute(): SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xE8' for column ...

The character in question is 'è', yet I don't see why this should be a problem considering the database and table are set to UTF-8.

Edit

I've tried directly from the mysql terminal and have the same problem.

Have you tried mysql_query('SET NAMES UTF-8') before making your queries? — Tom
– Tom, Commented Apr 11, 2010 at 1:06
Yet another idea regarding your comment below: Did you ensure that the character set used to send the form is UTF-8 (client-side; set in XML encoding or header/meta-attribute and maybe also enforced by <form accept-charset="utf-8" ...>)? PHP may or may not be using UTF-8 internally (php.ini), but on some servers you are not allowed to dynamically change that character set. If PHP uses some 8 bit charset it should still pass your UTF-8 encoded text to the database (mysql_real_escape_string works depending on the database charset) but PHP may need utf8_decode/utf8_encode on string manipulation. — Energiequant
– Energiequant, Commented Apr 11, 2010 at 10:30

Community · Accepted Answer · 2017-05-23 12:13:32Z

3

Your database might be set to UTF-8, but the database connection also needs to be set to UTF-8. You should do that with a SET NAMES utf8 statement. You can use the driver_options in PDO to have it execute that as soon as you connect:

$handle = new PDO("mysql:host=localhost;dbname=dbname",
    'username', 'password', 
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

Have a look at the following two links for more detailed information about making sure your entire site uses UTF-8 appropriately:

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Apr 11, 2010 at 1:23

mercator

28.7k8 gold badges66 silver badges74 bronze badges

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

1 Comment

Danten Over a year ago

For future reference, I did this. The problem was multi-byte strings.

Energiequant · Accepted Answer · 2010-04-11 01:07:55Z

1

E8 is greater than the maximum usable character 7F in a one-byte UTF8 character: http://en.wikipedia.org/wiki/UTF-8

It seems your connection is not set to UTF8 but some other 8 bit encoding like ISO Latin. If you set the database to UTF8 you only change the character set the database uses internally, connections may be on a different default value (latin1 for older MySQL versions) so you should try to send an initial SET CHARACTER SET utf-8 after connecting to the database. If you have access to my.cnf you can also set the correct default value there, but keep in mind that changing the default may break any other sites/apps running on the same host.

answered Apr 11, 2010 at 1:07

Energiequant

8895 silver badges15 bronze badges

3 Comments

Danten Over a year ago

Thanks, problem was overcome using php's utf8_encode() function.

mercator Over a year ago

SET CHARACTER SET is not the right command for this (see: stackoverflow.com/questions/1566602/…), and it should be utf8, not utf-8. @Danten, you should not need to use utf8_encode() if you've set everything up correctly.

Energiequant Over a year ago

For some reason, SET NAMES never worked the way we needed it on some of our client's servers (on some servers it seemed to have no effect at all). Naming of character sets unfortunately differs depending on the distribution you use. utf-8 was available on all servers we deployed to while utf8 was sometimes unknown. However, since we have to support some strangely hosted webspaces, some of them may have had a wrong setup. In any case, at least the character set (and maybe the command itself) should best be kept in a config file if changes have to be made quickly on deployment.

purab · Accepted Answer · 2013-07-31 06:03:37Z

1

Before passing the value to Mysql you can use the following code:

$val = mb_check_encoding($val, 'UTF-8') ? $val : utf8_encode($val);

convert the string the to UTF-8, If it's matter of only one field.

answered Jul 31, 2013 at 6:03

purab

3131 gold badge3 silver badges9 bronze badges

2 Comments

jeromej Over a year ago

You should definitely avoid doing that! Checking what is the encoding is consuming and is not always precise: Best try to know what the encoding actually is by others ways (if you aren't unlucky enough to don't have a clue, it's often mentioned and even multiples times sometimes though).

purab Over a year ago

Yes, You are right. What is mentioned is the quick fix to solve the issue. You should solve the issue by using Mysql

Collectives™ on Stack Overflow

UTF-8 Database Problem

3 Answers 3

1 Comment

3 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

2 Comments

Linked

Related