Encoding MySQL text fields to UTF-8 text files - problems with special characters
I am writing a php script to export MySQL database strings to a .txt file formatted for Adobe InDesign internal markup.
Exports work, but when I encounter special characters like é or umlauts, I get weird characters (like Chloë Hanslip
instead of Chloë Hanslip
). Instead of doing search and replace for all possible strange characters, I need a better method.
I have checked when the text hits the database it is saved correctly - in the database I see special characters. My export code is basically running some regex to inject InDesign code tags, and I'm leaving strange characters. If I just output the text to the browser (instead of being asked to download the text file), it displays correctly. When I save the file, I use this code:
header("Content-disposition: attachment; filename=test.txt");
header("Content-Type: text/plain; charset=utf-8");
I've tried various combinations utf8_encode()
and to iconv()
no avail. Can anyone point me in the right direction?
a source to share
InDesign will not be able to use any encoding specified in the title. (He would not even see it, as it is not saved when saved to disk on Windows.) Instead, you must explicitly tell it the encoding in a special tag at the beginning of the file, for example:
<ANSI-WIN>
Unfortunately, it doesn't use standard encoding names , and there is no tag that InDesign understands to conform to UTF-8 encoding at all. The only encoding tag you can use will allow you to include any character you like:
<UNICODE-WIN>
which matches UTF-16 (little-endian with BOM), with Windows CRLF line endings. (The only line ending option is MAC, which you don't need at all since these are older Mac OSes prior to OSX where the line ending character was CR.)
So, given the UTF-8 $ s string, including the UTF-8 byte sequences, you pulled from the database string and simple (Unix-Linux-OSX-web-style) LF strings, you should write it like this:
$s= "<UNICODE-WIN>\r\n".str_replace("\n", "\r\n", $s);
echo iconv('UTF-8', 'UTF-16', $s);
(Ensuring not to output any whitespace before or after, as that would break UTF-16 encoding.
a source to share
It looks like the ISO-8859-1 string is being sent as UTF-8 ...
Make sure your table and are in UTF-8 and connect to the database in UTF-8. If your table and fields are in UTF-8 and you don't specify MySQL encoding, MySQL will convert the data on the fly to ISO-8859-1 (latin1) - this is the default configuration for all hosts I've used so far ...
This is how I use this (backward compatible with PHP 5.2.2 and less):
$conn = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('dbname');
if (mysql_errno())
{
//Handle database connection error here
}
if (function_exists('mysql_set_charset'))
mysql_set_charset('utf8', $conn); //PHP 5.2.3+ only
else
{
if (mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'", $conn) === false)
{
//Unable to set database charset! Handle error here...
}
}
a source to share