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
– Omid, Commented Dec 20, 2013 at 14:54
@HarshalMahajan No, but I know just changing the collation won't make any change. — Omid
– Omid, Commented Dec 20, 2013 at 14:55
possible duplicate of How to convert wrongly encoded data to UTF-8? — deceze
– deceze ♦, Commented Dec 20, 2013 at 14:59

eggyal · Accepted Answer · 2013-12-20 15:09:17Z

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, 2013 at 15:09

answered Dec 20, 2013 at 14:54

eggyal

127k18 gold badges220 silver badges244 bronze badges

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

Collectives™ on Stack Overflow

convert utf8 characters using PHP or MySQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related