Page 1 of 1
Mysql special character problem
Posted: Sun Jun 29, 2014 12:01 am
by barb woolums
I have a directions field stored in my mysql db that contains a string "325°F" which is correct
When I select it from the db using PDO as follows it becomes "325°F" how do I fix this. I can't set the charset in the header prior to this because I am doing an export so can't send any headers yet. I have set accept-charset="utf-8" on the submitted form though.
The collation on the column, table and db is utf8_general_ci
Code: Select all
$rdb = new PDO("$dbtype:host=$dbhost;dbname=$dbrecipes;charset=utf8", $dbuser, $dbpass);
$sql = "Call query_recipe(:id)";
$dbrecipe = $rdb->prepare($sql);
$dbrecipe->bindValue(':id', $id);
$dbrecipe->execute();
$err=$rdb->errorInfo();
$rsrecipe = $dbrecipe->fetch(PDO::FETCH_BOTH);
$dbrecipe->closeCursor();
$directions=$rsrecipe[1];
Re: Mysql special character problem
Posted: Sun Jun 29, 2014 1:18 am
by requinix
- "Can't send any headers yet"? You can send them at any point up until you output something.
- Accept-Charset controls the input encoding but not the output.
If you're outputting over the web (as opposed to exporting to a file) then you need to send that header. That's the missing piece of the puzzle. If you're dealing with UTF-8 in your database then you should be indicating UTF-8 for everything you do - whether it uses the database or not.
Re: Mysql special character problem
Posted: Sun Jun 29, 2014 1:54 am
by barb woolums
Actually I managed to get it working by doing an iconv on the string and now it exports beautifully.
Now I have a new problem - when I import the same data and try to insert it back into the db the string chops off at the degree symbol.
Do I need to escape it some way first? The solution has to work for postgresql as well, as I am writing portable code (well trying to)
Re: Mysql special character problem
Posted: Sun Jun 29, 2014 2:15 am
by requinix
Did you un-iconv it back?
Seriously. Use one encoding everywhere and you won't have to do any sorts of conversions and it will all Just Work.
Re: Mysql special character problem
Posted: Sun Jun 29, 2014 2:26 am
by barb woolums
I couldn't get it to work any other way. I did add the utf8 charset to the headers when I do send them but that didn't work.
Code: Select all
if ($recipe_format == 'CSV(.csv)') {
// We'll be outputting an csv file
header('Content-type: text/csv; charset=utf-8');
// It will be called exported-recipes.mmf
header('Content-Disposition: attachment; filename="exported-recipes.csv"');
echo $csv_output;
exit();
} elseif ($recipe_format == 'Meal Master(.mmf)') {
// We'll be outputting an mmf file
header('Content-type: text/mmf; charset=utf-8');
// It will be called exported-recipes.mmf
header('Content-Disposition: attachment; filename="exported-recipes.mmf"');
echo $mmf_output;
exit();
} elseif ($recipe_format == 'MS Word(.doc)') {
header('Content-Type: application/msword; charset=utf-8');
header('Content-Length: '.strlen($content));
header('Content-disposition: inline; filename="exported-recipes.doc"');
echo $content;
exit();
}
What have I missed?
I really don't want to iconv and un iconv everything.
Re: Mysql special character problem
Posted: Sun Jun 29, 2014 5:31 am
by requinix
1. Specifying the charset in a header does not affect downloads (that is, "Content-Disposition: attachment)", only content you serve directly to and through the browser.
2. The charset option is only for text/* types, not others like application/msword. (Actually it's on a case-by-case basis, but rule of thumb is it won't work.)
You can try providing the UTF-8 BOM at the beginning of the content. It's a bit hacky but often helps when dealing with downloaded files (meaning you shouldn't use it for non-downloads).
Code: Select all
echo utf8_encode("\xFE\xFF"), $content;
Only use that for the two text files; if the .doc has encoding problems then it should be addressed in whatever it is that generates the .doc's data. Because it's all binary data and has its own way of dealing with that kind of issue.
Though the BOM has additional technical implications, here its purpose is to indicate to programs reading the file that the file uses UTF-8. But the program has to know to recognize it, or else you'll see some odd characters at the beginning.
Re: Mysql special character problem
Posted: Sun Jun 29, 2014 8:25 pm
by barb woolums
For both the .mmf and .csv, - if I opened them with a text editor, the degrees symbol was displayed correctly, but there was a "þÿ" string inserted at the beginning of the document which may break any import scripts based on pure Meal Master Format.
It didn't work at all for the .csv file when I opened it with excel.
Re: Mysql special character problem
Posted: Sun Jun 29, 2014 8:53 pm
by barb woolums
I found a solution that works for all 3 file types. Thanks so much for all your help and for pointing me in the right direction.
echo utf8_decode($content);
Re: Mysql special character problem
Posted: Sun Jun 29, 2014 10:25 pm
by requinix
I was actually about to suggest that: trying a different character encoding. One that supports things like the degrees symbol. ISO 8859-1 does and you can get to that by using utf8_decode().
barb woolums wrote:For both the .mmf and .csv, - if I opened them with a text editor, the degrees symbol was displayed correctly, but there was a "þÿ" string inserted at the beginning of the document which may break any import scripts based on pure Meal Master Format.
That suggests the editor assumed UTF-8 by default - otherwise it would have known to ignore the BOM at the beginning.
barb woolums wrote:It didn't work at all for the .csv file when I opened it with excel.
Some searching suggests Excel doesn't like UTF-8 but may work with UTF-16LE. The code would look like
Code: Select all
echo "\xFF\xFE", iconv("UTF-8", "UTF-16LE", $content);
Re: Mysql special character problem
Posted: Mon Jun 30, 2014 2:38 am
by barb woolums
Yeah all good now. Thanks again!!