PHP PDO Database Error Special Characters

Question

i've read most of the questions and answers about this situation but i cant fix my character problem. My database's default character set is utf8 and all the tables' collation is utf8_general_ci. I'm sure that all of the settings are utf8 and utf8_general_ci, cuz i've checked them billions of time. Problem is after posting the value within a form, it doesnt seem like what i want in database, and also if i edit the database from phpmyadmin, when i fetch the data, its again not showing what i want.

The DB connection works, i edited it like mentioned before about this situation, but my script is buggy about character speciziliation..

The DB Connect Code is :

try {

$db = new PDO("mysql:host={$db_server};dbname={$db_name};charset=utf-8", $db_user, $db_password,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
// exc 
}

Would be glad if you can help

Regards

for instance ö is -> Ã¶ in mysql and ü is like -> Ã¼r which are their special characters, i can write a function to convert them but there are many files i need to do if i fail with this, what can be an another problem ? — , Sep 03 '12 at 05:46
you need to have in the same encoding: 1) the page/form 2) table and column (if any) charset (**not collation**) 3) db connection. That's it. If you have all 3 of them the same `utf-8` - then it should work. If it doesn't - you're missing something and need to re-check each of them — zerkms, Sep 03 '12 at 05:49
Maybe this helps? [Handling Unicode Front To Back In A Web App](http://kunststube.net/frontback/) — deceze, Sep 03 '12 at 07:09

score 1 · Accepted Answer · answered Sep 03 '12 at 07:40

For those who has the same error even you are sure about the things that @zerkms mentioned :

" you need to have in the same encoding: 1) the page/form 2) table and column (if any) charset (not collation) 3) db connection. That's it. If you have all 3 of them the same utf-8 - then it should work. If it doesn't - you're missing something and need to re-check each of them "

if you are still having problem like i did, check your form process data that if there is a filter that you forgot. i had a filter for security, it was sanitizing inputs and it was only for ansi encoding, so check out everything and than it will be fine.

thanks for all who replied.

Ahh yes, this can be hard to find. – martinstoeckli Sep 03 '12 at 08:04 — martinstoeckli, Sep 03 '12 at 08:04

score 0 · Answer 2 · edited May 23 '17 at 10:34

:) Somewhere in your toolchain, something is not using utf8. PHPMyAdmin is well known for this type of issue but I cannot help you much there as I much prefer the command line or scripts to play with a database. If the output is fine everywhere except in phpMyadmin I can refer you to this post that offers a lot of tips relating to phpmyadmin.

Oh, and you can specify utf-8 encoding in your instanciation call to PDO:

$con = new PDO('mysql:host=' . $server . ';dbname=' . $db . ';charset=UTF8', $user, $pass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

The most basic helpful thing you can do when setting up MysqL is adding this to your /etc/my.cnf file:

[mysqld]
default-character-set=utf8
default-collation=utf8_general_ci
character-set-server=utf8
collation-server=utf8_general_ci
init-connect='SET NAMES utf8'

[client]
default-character-set=utf8

The editors can also play tricks on you. Some editors when not configured properly can switch and resave in improper encoding, causing garbled text when re-opened in utf-8. All decent IDE's and editors can be configured to handle UTF-8.

Hope this helps, good-luck.

"The most basic helpful thing you can do" --- actually it's the most terrible advice ever. You shouldn't rely on the DB server settings. And, actually, it's all completely pointless when you have `charset=UTF8` specified — zerkms, Sep 03 '12 at 06:00
@zerkms still no luck, did everything searched everywhere but still no luck. — , Sep 03 '12 at 06:31

score 0 · Answer 3 · answered Sep 03 '12 at 07:20

You set the charset of the connection object to utf-8, that's good and other settings in the database should not have any impact then.

I would check that your page (the one that inserts and the one that displays) are both correctly encoded. There are two things to check: Check that your page is stored UTF-8 encoded (without BOM), this is the job of your editor/ide. Then check that you declared it correctly with something like:

<meta http-equiv="content-type" content="text/html; charset=UTF-8" />

Of course if you already have inserted data to your db from a wrong encoded page, the data in your db is invalid and cannot be displayed properly, on a correctly encoded page.

PHP PDO Database Error Special Characters

3 Answers3

Linked