Weird charset problem

[Free_Man]

Hi,

I try to take data from mysql and insert into xls. I achieve that but I cannot sort out the char problem. Turkish characters are displayed in a weird shape in the file but everything seems fine when i display them in a web page.

I tried ISO-8859-9(which is turkish) then tried UTF-8 for both php pages and mysql but still there's the problem.

What happens is, if i send the data with $_POST it writes into file as it is but once i input the data to mysql and return it to the file there i get the problem.

Any ideas how to sort it out?

[sDJh]

I don't know how many turkish characters differ from the ISO-Standart, but if there aren't many, you could try to mask them like for "ü" etc. You save then the masked code in SQL, fetch the data later and unmask it ""=>"ü", etc.

That might help, just is a hell a lot of work, when there are many characters to mask.

[Free_Man]

thank you for your answer. There are around 10 characters different than iso standard so I could try to mask but if i would just now how :)

if you could give an example, i would love to try.

cheers

[digital-ether]

Hi,

I try to take data from mysql and insert into xls. I achieve that but I cannot sort out the char problem. Turkish characters are displayed in a weird shape in the file but everything seems fine when i display them in a web page.

I tried ISO-8859-9(which is turkish) then tried UTF-8 for both php pages and mysql but still there's the problem.

What happens is, if i send the data with $_POST it writes into file as it is but once i input the data to mysql and return it to the file there i get the problem.

Any ideas how to sort it out?

It sounds like the encoding is being mangled by MySQL. Masking the special characters as suggested is not a bad idea, but probably not the best solution. This would also be similar to converting the characters to html entity equivalents, which would be a bit faster since you could use the native PHP funciton htmlspecialchars(). This would be ready for display in XML languages, such as xHTML without having to convert back.

What charset is your data being saved as in the database? What is the database field/column type?

Just make sure you save the characters in the database as the same character encoding you receive them as, or convert them to the database field encoding. That way the database wont mangle them.

You can then convert the data to any charset, encoding you need for display.

[Free_Man]

now the problem came to a different point... I changed the charsets for both php file and mysql table from ISO,latin5 to UTF8,UTF8_turkish_ci and then generate xsl file, not an actual excel file anymore though, I just created a file called filename.XSL and wrote some html in it.

That works fine in local as I can manipulate mysql settings as i wish but in server it's a bit tricky, mysql collation is set to utf8_general_ci and when i generate the file, chars again look weird but when i check the source of the file, there chars are fine. now i'm very confused!!!

[digital-ether]

now the problem came to a different point... I changed the charsets for both php file and mysql table from ISO,latin5 to UTF8,UTF8_turkish_ci and then generate xsl file, not an actual excel file anymore though, I just created a file called filename.XSL and wrote some html in it.

That works fine in local as I can manipulate mysql settings as i wish but in server it's a bit tricky, mysql collation is set to utf8_general_ci and when i generate the file, chars again look weird but when i check the source of the file, there chars are fine. now i'm very confused!!!

Make sure your browser sees the encoding correctly. In the XSL file you generate, make sure the HTTP Header "Content-Type" is being set to "text/xsl; charset=utf-8". (I'm not sure what the XSL mimetype is).

If you use Firefox as your browser, install the firebug extension and view the HTTP Headers.

Its fuzzy how browsers choose the encoding, but usually the HTTP Content Type header overrides the others.

[Free_Man]

I'm sorry it took me a while to answer. Since the last answer I didn't have any time to test the script, just today I found 5 mins and tested it, it works fine now, all Turkish characters are displayed properly. Below I added to final test script for everyone who needs. I want to thank you digital-ether and sDJh for your close attention.

What following code does;
It takes results from MySQL and writes into an Excel file as HTML. Turkish chars works fine and I guess every other format will be ok. When you copy/paste the code, be sure that you save the file as UTF-8, DB tables also should be in UTF-8 format.

<?
  header('Content-Type: text/html; charset=utf-8');
  header("Content-type: application/vnd.ms-excel; charset=utf-8");
  header("Content-Disposition: attachment; filename=\"TestFile.xls\"");
//First connect to DB then....
$result_csv=mysql_query("SELECT Statement goes here");
?>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<style type="text/css">
body, div, table { font-family: Arial; font-size: 10px; color: #000000 }
</style>
<div align="center"><h2>the list</h2></div>

<table border="1">

<tr height="40" style="background-color:#DEDEDE">
<th>col-1</th>
<th>col-2</th>
</tr>
<?
while($listRow=mysql_fetch_array($result_csv)){

?>
<tr height="40">
<th><?=$listRow[1]?></th>
<th><?=$listRow[2]?></th>
</tr>
<?
}

?>
</table>