[SOLVED] MySQL, UTF-8 and foreign characters

[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 ī in the database. 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 I saw 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. The perfect way for it to work would be having it the same everywhere - both š in the db and š when outputted.

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 and the MySQL server version is 5.1.30. Also I just noticed that my MySQL lib version is 5.0.82, which differs from the server and may cause unpredictable behavior (according to phpMyAdmin), so in case this might seriously be a problem, a tip on how to change this would come in handy as well.

 

I would be very grateful if anyone could shed some light the right way.

Thanks,

Max

[corbin]

Make sure the charset on the table is UTF-8, and make sure the browser knows the page being output is UTF-8.

 

 

Telling the browser that the page is UTF-8 is pretty easy:

 

header("Content-Type: text/html; charset=UTF-8"); before any content is output

[mx781]

Thanks for tip. I had already ensured the table was utf-8 and that header had also been included.

Though I found a solution - by adding

 

AddDefaultCharset UTF-8

 

to the .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 yes, it's working all good now, cheers!