How to retrieve utf-8 data with php and show the correct encoding in an excelsheet db dump?

Question

Hi I am saving mostly english and german characters into a mysql database which currently is set to utf-8 charset.

I am assuming that I should use latin1 charset for this type of data, is that correct?

If so how can I change the charset to correct the german chars which are now saved in utf-8?

UPDATE

Maybe then it is a retrival problem ... When I export data from the db via php of course I get utf-8 back, can I do the retrival to give me latin1?

UPDATE 1

Ok I am building a website, the html encoding is uft-8 the db is uft-8, and now I want to run some exports and extract data, which should be returned in an excel sheet, and the data is utf-8, but here I need the chars to be latin1 ... or the encoding of the excel sheet extracted from the db need to be such that TÃ¶st will show Täst. Right now I get the data like this -> TÃ¶st

UPDATE 2

I am using following php script to do the dump:

http://www.fundisom.com/phparadise/php/databases/mySQL_to_excel

on line 48 I have changed the code to

header("Content-Type: application/$file_type; charset=utf-8");

no change in behaviour.

How would I solve the issue?

Almost Solution

<?php
$text = "Ã¶ is a valid UTF-8 character";
echo 'Original : ', $text, PHP_EOL;
echo 'TRANSLIT : ', iconv("UTF-8", "ISO-8859-1//TRANSLIT", $text), PHP_EOL;
echo 'IGNORE   : ', iconv("UTF-8", "ISO-8859-1//IGNORE", $text), PHP_EOL;
echo 'Plain    : ', iconv("UTF-8", "ISO-8859-1", $text), PHP_EOL;
?>

this is what I need I think ... but I need to check it in context of the php script... tomorrow :-)

score 2 · Answer 1 · answered Dec 17 '10 at 00:49

2

Once you using double byte characters like UTF-8, there is no turning back...

The closer you can use is iconv

like this

<?php
$text = "ü is still a valid ISO-8859-1";
echo 'Original : ', $text, PHP_EOL;
echo 'TRANSLIT : ', iconv("UTF-8", "ISO-8859-1//TRANSLIT", $text), PHP_EOL;
echo 'IGNORE   : ', iconv("UTF-8", "ISO-8859-1//IGNORE", $text), PHP_EOL;
echo 'Plain    : ', iconv("UTF-8", "ISO-8859-1", $text), PHP_EOL;
?>

details : http://php.net/manual/en/function.iconv.php

answered Dec 17 '10 at 00:49

ajreal

46,720
11
89
119

but why would I need to convert the data if I am writing my excel sheet in utf8? – mahatmanich Dec 17 '10 at 11:52
Not 100% sure excel allow UTF-8, which mean an UTF-8 will be double-encoded ... while change back to ASCII able to retain at least for umlauts – ajreal Dec 17 '10 at 12:38
I am still confused about the issue. What would it look like if the data was saved correctly in the db as uft-8? – mahatmanich Dec 17 '10 at 14:56
If you do a query like `select 'ä', length('ä'), char_length('ä');` it should return `ä,2,1`. If you do using output (encoding UTF-8), it should return `ä` as well. There are other environment will mutate the result (like charset used in php for mysql connection) – ajreal Dec 17 '10 at 16:26

score 1 · Answer 2 · answered Dec 16 '10 at 23:12

1

With UTF-8 you can store any character supported by Unicode, With UTF-8 you can store any character supported by Unicode, so you shouldn't have any problem using it to store just latin1 characters (which are just a really small subset of what Unicode supports).

So, for information storage you're ok; if you need to do any conversions when retrieving the data, that depends on the connector you use to get the data from the DB and on how your programming language handles string.

For the update: assuming that you're using PHP to produce web pages, can't you just send the right HTTP header specifying that your page is encoded in UTF8?

answered Dec 16 '10 at 23:12

Matteo Italia

123,740
17
206
299

ok what would that do? Transfer all data in UTF-8, even äöü, and write it correctly into the database? – mahatmanich Dec 16 '10 at 23:20
Yes, I'd go that way. Using UTF-8 in the whole application you don't restrict to any language, so, once it's working, you shouldn't have problems with any character the user may input. – Matteo Italia Dec 16 '10 at 23:22
Where do you get this problem exactly? – Matteo Italia Dec 16 '10 at 23:48
What are you using to produce it (Excel via ODBC/PHP/...)? – Matteo Italia Dec 17 '10 at 00:40

score 0 · Answer 3 · answered Dec 16 '10 at 23:12

UTF-8 is the very best choice for all intents and purposes. Unless you have a really pressing reason to go for latin1 (e.g. compatibility with other applications), go for it.

There are several UTF-8 collations that handle umlauts and sort orders differently (see here for a list). You may need to pick one over the other depending on your requirements. They all can store umlauts, though.

score 0 · Accepted Answer · answered Dec 16 '10 at 23:23

I agree with the previous answers that UTF-8 is a good choice for most applications.

Beware the traps that might be awaiting you, though! You'll want to be careful that you use a consistent character encoding throughout your system (input forms, output web pages, other front ends that might access or change the data).

I have spent some unpleasant hours trying to figure out why a simple β or é was mangled on my web page, only to find that something somewhere had goofed up an encoding. I've even seen cases of text that gets run through multiple encoders--once turning a single quotation mark into eight bytes.

Bottom line, don't assume the correct translation will be done; be explicit about character encoding throughout your project.

Edit: I see in your update you've already started to discover this particular joy. :)

How to retrieve utf-8 data with php and show the correct encoding in an excelsheet db dump?

4 Answers4

Linked