I have a database which uses latin-1 and a PHP application which is utf-8.
I have strings in the database like this:
'Société' which should be Société
'€1bn' which should be €2bn.
When I print the faulty characters to screen with PHP's ord(), from the returning data in the db, it prints 195 and 226.
Could somebody explain why this is happening (why saving like this and why characters being read as they are) and if I can reverse it.
The WHY:
1) é is unicode 233 (as the browser reads it).é utf8 bytes converted into latin1 chars bytes is à ©. This is why it appears like this in the database.à © is recognised as à which is code point 195. Hence why you see that.
2) € is unicode 8364.
€ utf8 bytes converted into latin1 chars bytes is â <82> ¬. Again this is why they appear like this in the db.â <82> ¬ is recognised as â which is code point 226. Again this is why you see this.
That is why you see those values from ord() and why the characters are stored in that manner in a latin-1 database.
Reverse:
To reverse it we need Latin-1 char bytes to UTF8 bytes.
If we try it:â is 226. Converted latin-1 to utf8 produces â.à is 195. Converted latin-1 to utf8 produces Ã.
Problem:
The problem is Latin-1 has less characters than utf-8 (by a long way).
Latin1 single-byte stream and UTF8 multi-byte char stream so 1 char in utf8 could produce up to 4 chars for latin1.
So the UTF-8 to Latin-1 conversion produces faulty characters.
Latin1 back to utf8 is not possible.
Solution:
IF you are unable to change the character set of your database I could suggest encoding special characters in the database in their character entity before writing them (so the db can stay as latin1 and app as utf8 as both can understand html entities) e.g. umlaut as Ä.
It could be done using PHPs html_entity_decode() combined with mb_detect_encoding() to detect and convert specific characters.
References:
See ltf.ed.ac.uk for the utf8 char bytes to latin1 bytes:
http://www.ltg.ed.ac.uk/~richard/utf-8.cgi?input=%C3%96&mode=char
These are strings in UTF-8 but displayed as if they were latin1. In UTF-8 é and € are encoded with two bytes, that's why you see two characters when the string is interpreted as latin1. So what you are doing is storing UTF-8 data in a table that was not declared as UTF-8. You should change the encoding of the database* and the connection**, then you will get a consistent presentation of your data
*) for example see here: https://stackoverflow.com/a/6184788/664108 (case 2)
**) SET NAMES 'utf8' in SQL