UTF-8, MySQL and foreign characters

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
mx781
Forum Newbie
Posts: 2
Joined: Sun Oct 18, 2009 1:56 pm

UTF-8, MySQL and foreign characters

Post by mx781 »

Hi,

I have come here in seek of help to a problem that has been haunting me for the past days.
I am having problems when inserting characters such as 'ā, č, š, ž, ķ, etc.' into my database. Beforehand it worked great with another project, by inserting, for example, Å¡ instead of š and Ä« instead of ī. While after recent research, it seems that this is an indicator of a faulty setup, I didn't pay much attention to it as when retrieved from the db again, they showed up perfectly normal - as š and ī.

When working with another project that involves this kind of characters, a problem arose. First, when testing stuff out with the db and stuff (using the insert command from phpMyAdmin), they all showed up normally (ā, ņ, etc.) in pma. But when I retrieved them from the db to output, they returned as Å¡, Ä«, etc. - basically, gibberish. I re-ensured that my .php file was saved as utf-8, then checked the mysql connection charset - set it to the utf8_bin, which is used for the database, the table and all of columns - basically changed any character set to utf8_bin.

This proved to work, to some extent. From that moment on, all of these characters were now inserted as html entities into the database, yet two characters - š and ž didn't. They insert as normal characters into the db and can be seen as normal characters (in form of š and ž) in pma. Though when they are retrieved and displayed, the once again show up gibberish. I wouldn't mind having the 'faulty' method to work, as that at least worked properly with all characters, but I can't distinguish any differences nor between the databases used (and the collations used there), nor between the way php files handled sql queries.
I did try using SET NAMES UTF-8 after connecting to the database, but that didn't change anything.
I am using PHP 5.2.10 on a Mac OSX machine.

I would be very grateful if anyone could shed some light the right way.
Thanks, Max
mx781
Forum Newbie
Posts: 2
Joined: Sun Oct 18, 2009 1:56 pm

Re: UTF-8, MySQL and foreign characters

Post by mx781 »

Finally found a solution!
The trick is to add

AddDefaultCharset UTF-8

to your .htaccess file. I've no idea why that works, but it does. Characters are inputted as ā, š, ž into the database and are retrieved properly as well - even without using html entities.

I found this solution posted by a user in the comments of this page, so I reckon other suggestions over there might come in handy for similar problems.
So it's working all good now, cheers!
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: UTF-8, MySQL and foreign characters

Post by Weirdan »

mx781 wrote:I've no idea why that works, but it does.
http://httpd.apache.org/docs/2.0/mod/co ... ultCharset - it instructs browser that the page is in particular charset/encoding. By extension, most browsers will assume the server expects requests in the same character set/encoding (and use it when making subsequent requests).
Post Reply