convert utf8 characters using PHP or MySQL

Question

I'm not sure if someone has asked this question before or not but I didn't find any. While I hadn't set connection charset in PDO and table collation was utf8_percian_ci, so all data has been stored in unreadable characters like Ø³Ù„Ø§Ù… that is سلام in Persian.

Before setting charset by adding mysql:charset=utf8mb4; to PDO DNS I was able to retrieve all data correctly but now I see Ø³Ù„Ø§Ù… instead of سلام in browser.

My website is a blog and now it seems I have to reenter all the texts and posts and then restore them to be saved correctly. That's a disaster!

I used mb_detect_encoding() for both Ø³Ù„Ø§Ù… & سلام and found out that both of them are UTF-8. This is so funny to search "How to convert utf8 to utf8 ?" and absolutely I get no expected result.

Is there anyway to convert Ø³Ù„Ø§Ù… to سلام using MySQL ? If not, I thought another way could be using PHP to read old data then convert and insert into database again.

What should I do ?

I have tried that. The problem is not just about output encoding — Omid, Dec 20 '13 at 14:54
@HarshalMahajan No, but I know just changing the collation won't make any change. — Omid, Dec 20 '13 at 14:55
possible duplicate of [How to convert wrongly encoded data to UTF-8?](http://stackoverflow.com/questions/20151835/how-to-convert-wrongly-encoded-data-to-utf-8) — deceze, Dec 20 '13 at 14:59

eggyal · Answer 1 · 2013-12-20T15:09:17.580

2

You can simply do:

UPDATE my_table SET my_column = BINARY CONVERT(my_column USING latin1)

(where latin1 is the character set in which your connection was set at the time of insertion).

edited Dec 20 '13 at 15:09

answered Dec 20 '13 at 14:54

eggyal

122,705
18
212
237

Nice Answer , Thank you – waseem Alhabahs Mar 14 '20 at 12:47

convert utf8 characters using PHP or MySQL

1 Answers1