Vietnamese and Chinese getting screwed up in MySQL database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Vietnamese and Chinese getting screwed up in MySQL database

Post by rhecker »

We are pasting Chinese and Vietnamese text into a textarea that is then posted to a 5.1.30 MySQL table. The webpage characterset is set to UTF-8. Instead of maintaining the characters, the text is being converted into strings like "&#45688," then, when the text is pulled from the database and displayed in a webpage, it comes in as gibberish characters or question marks.

I have set the collation of specific columns to utf8_unicode_ci, but that makes no difference. If I write the data to a simple text file instead of posting it to the database, the correct characters are maintained.

Can anyone suggest a way to get MySql to stop changing the characters?

Thanks for any help.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Vietnamese and Chinese getting screwed up in MySQL database

Post by onion2k »

You need to set the character set on the table as well the collation. The collation is only used for sorting the data. The character set is used for storing the data. You can define the character set using the charset directive when you create the table.

You'll also need to set the character encoding used by PHP's mysql client. Use "SET NAMES 'utf8';".
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Re: Vietnamese and Chinese getting screwed up in MySQL database

Post by rhecker »

Thank you. Your response was helpful. I have set UTF8 everywhere I can. I now have the Chinese text working fine, but the Vietnamese text still comes out wrong after taking a trip to the database.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Vietnamese and Chinese getting screwed up in MySQL database

Post by Eran »

Make sure the connection collation / character set matches that of the database.
Check out this thread - viewtopic.php?f=1&t=105682
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Re: Vietnamese and Chinese getting screwed up in MySQL database

Post by rhecker »

Thank you. The initial code snippet from that thread made my Vietnamese text worse, not better, but it's an interesting discussion that I need to dig into.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Vietnamese and Chinese getting screwed up in MySQL database

Post by Eran »

You need to read before you try, the initial code snippet was not the solution but a part of the problem.
Post Reply