将MySQL文本字段编码为UTF-8文本文件 - 特殊字符的问题

Question

I'm writing a php script to export MySQL database rows into a .txt file formatted for Adobe InDesign's internal markup.

Exports work, but when I encounter special characters like é or umlauts, I get weird symbols (eg Chloë Hanslip instead of Chloë Hanslip). Rather than run a search and replace for every possible weird character, I need a better method.

I've checked that when the text hits the database, it's saved properly - in the database I see the special characters. My export code basically runs some regular expressions to put in the InDesign code tags, and I'm left with the weird symbols. If I just output the text to the browser (rather than prompt for a text file download), it displays properly. 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 of utf8_encode() and iconv() to no avail. Can anybody point me in the right direction here?

Answer 1

InDesign wouldn't be able to use any encoding specified in the header. (It wouldn't even see it, as it's not kept when you save to disc in Windows.) Instead you have to explicitly tell it the encoding in a special tag of its own at the start of the file, such as:

<ANSI-WIN>

Unfortunately, it does not use standard encoding names and there is no tag that InDesign understands that corresponds to UTF-8 encoding at all. The only encoding tag you can use that will allow you to include any character you like is:

<UNICODE-WIN>

which corresponds to UTF-16 (little-endian with BOM), with Windows CRLF line endings. (The only other line ending option is MAC, which you don't want at all as it's old-school pre-OSX Macs where the line ending character was CR.)

So, given a UTF-8 string $s including UTF-8 byte sequences you've pulled out of the database and plain (Unix-Linux-OSX-web-style) LF newlines, you'd write it like this:

$s= "<UNICODE-WIN>
".str_replace("
", "
", $s);
echo iconv('UTF-8', 'UTF-16', $s);

(Ensuring not to output any whitespace before or after, because it'll break the UTF-16 encoding.