Php + Mysql (UTF-8 ) some characters are still bug

Question

Well i got a php script that takes nicknames from a the Steam web-api and insert them into a mysql db. Many of them got rare russian and greek characters. I set php to utf-8 in the php.ini and in all the php files with

mb_internal_encoding('utf-8');

My PDO connector is configured to handle utf8

$connection = new PDO('mysql:host=localhost;dbname=d2bd;mysql:charset=utf8mb4', 'root', '');
$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$connection->setAttribute(PDO::ATTR_PERSISTENT, true);
$connection->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'");

my mysql db is properly configured with utf8mb4

character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database utf8mb4
character_set_filesystem binary
character_set_results utf8mb4
character_set_server utf8mb4
character_set_system utf8
character_sets_dir C:\xampp\mysql\share\charsets\
collation_connection utf8mb4_unicode_ci
collation_database utf8mb4_unicode_ci
collation_server utf8mb4_unicode_ci
completion_type NO_CHAIN
concurrent_insert AUTO
connect_timeout 10
core_file OFF

In few words i take the input of the web-api and encode it with uft8_encode(). Then i insert it into the db. The problem is that some characters are not well encoded and when i recall them from the database they are all bugged.

Example 1:

1.Input -> Перуанский чертовски

2.Encode -> ÐеÑÑанÑкий ÑеÑÑовÑки

3.Insert into DB

4.Select from DB -> Ð?еÑ?Ñ?анÑкий Ñ?еÑ?Ñ?овÑкÐ

5.Decode

6.Output -> �?е�?�?анский �?е�?�?овск�

Example 2:

1.Input -> $ |/| 1 ↓_ € ♥ J

2.Encode -> $ |/| 1 â_ ⬠⥠J

3.Insert into DB

4.Select from DB -> 1 â??_ â?¬ â?¥ J

5.Decode

6.Output -> 1 �??_ �?� �?� J

Answer 1

Checklist for Problems with character/charset/collation

Including mysql, mysqli, PDO

---

Content

1. DISCLAIMER
2. My insert's in my DB doesn't work properly! What can i do?
3. Change Charset and Collation of a Database or Table
4. Set the encoding of your skript files
5. Set the charset of your page with php or meta tag
6. What's the difference between UTF8 and UTF8mb4?
7. Answer to this specific Question
8. Further Information/Additional Links
9. Side Notes

1. DISCLAIMER

This Answer should not only answer this question, also should the answer be a bit more extensive, so more people find faster a bundled and good answer!

!Important Notice!
If you change something in your Database always make sur you have a backup of your database! Check it 2 times, or 3!

I'm open for improvements and comments, such as error corrections.
In addition I apologize if the grammar is not perfect: D

If you get stuck on a question like this:

• Php + Mysql (UTF-8, utf8mb4) some characters are still bug
• How to convert an entire MySQL database characterset and collation to UTF-8?
• “Incorrect string value” when trying to insert UTF-8 into MySQL
• Change MySQL default character set to UTF-8 in my.cnf?
• Using utf8mb4 with php and mysql
• PDO + MySQL and broken UTF-8 encoding
• Error in insertion data in php Mysql
• PHP PDO: charset, set names?
• SET NAMES utf8 in MySQL?
• PHP mysql charset utf8 problems
• UTF-8 all the way through
• Manipulating utf8mb4 data from MySQL with PHP
• ERROR 1115 (42000) : Unknown character set: 'utf8mb4' in mysql

...then my answer maybe helps you!

2. My insert's in my DB doesn't work properly! What can i do?

If your insert's doesn't work properly an your inserted data looks something like this in your database then this could have various reasons!

Examples:

??????????
                                                                    

                                                        
            

查看更多

0人赞 添加讨论(0) 举报

Answer 2

I changed the collation of the tables from SQLyog, but it seems that it's broken. When i changed them directly from a sql query it worked.