Special (Nordic) characters in MySQL

Anon

Hi!

I have cells in a MySQL database containing nordic characters, for example å
and ö. When I run a normal query in the MySQL client the characters are
displayed the way they should be. But when I use PHP to run the query and
then echo the result, ö is converted to a ” -sign (haven't tried other
special characters). But PHP itself supports these characters, when I write
echo ('åäö') the characters are displayed the way they should be.
Using setlocale() hasn't helped...

Does anyone know how to get PHP to process the result from mysql_... -commands
so that the characters are displayed correctly?

Thanks in advance,

Johan

spacemax

use

string htmlspecialchars(string string);

to html encode these characters, so the browser knows exactly what to display..

spacemax

string htmlentities(string string);

Anon

Thanks, but using htmlentities() didn't help. I don't think this problem has got to do with html, as it should support these characters
<META HTTP-EQUIV="Content-type" CONTENT="text/html;charset=iso-8859-1">
I had the same problem when mailing the texts from the database.

I can't really understand how this problem occurs as PHP should use ISO-8859-1 by default (specifying this charset in php.ini didn't help), as should MySQL also. After being in contact with a newsgroup, used by people who also use these characters, I believe this problem doesn't occur on all platforms, e g not on Linux. I'm using NT4 WS SP6a, Apache 1.3, MySQL 3.23.35a and PHP 4.0.4pl1.

The nordic characters I use are 'ÅÄÖåäö', and these were converted into 'Ž™†„”'. As these "wrong characters" are used very rarely, I decided to simply use ereg_replace() to replace them with the correct characters, e g
$string = ereg_replace(143, 'Å', $string);
Also, as I didn't find the corresponding decimal number for "the wrong characters" in a ISO 8859-1 table
http://www.utoronto.ca/webdocs/HTMLdocs/NewHTML/iso_table.html
, I had to use ord() to get them.

johan

Anon

The "wrong" characters got displayed incorrectly on phpbuilder, they don't look the same!

spacemax

Try to use the function not for displaying the character but for inserting it into the DB

Anon

That's a good idea, but there is couple of problems. The first one is that since for example Å gets converted into Å , I would have to redesign my DB in order to have the varchar fields long enough to contain the bigger amounts of characters. The second problem is that I would have to convert Å back to Å somehow, in order to be able to use it in emails for example.

But I did notice one thing, when I insert the scandinavian characters into the DB via PHP, they get converted in the insertion process to the "wrong" characters, so they are stored as "wrong" characters. And when I run a query via PHP they are converted back to the correct characters. I didn't notice this earlier as I'm developing my site and inserted stuff into the DB via the MySQL client.

Also the SQL syntax
ORDER BY whatever ASC
seems to work correctly, although they characters are "wrong".

spacemax

The problem with the fieldlength should be solveable, I think.

the other one with the email you have to write a function with some 😉 eregi statements, i din't found a function converting it back.

or you send a mime formatet html email
mail("dominik@nufer.org","guestbook entry from: ".$name." ".$vname,urldecode($text),"From: ".$email."
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Mozilla-Status: 8001");
}

by copying this function i remarked, for my system i store all urlencoded in the database, this is because of some trouble with some special characters..

Anon

Thanks for all your suggestions Dominik, but the actual problem was finally solved by a Finnish Java programmer. Or actually there was never any problem (!). The reason why PHP showed the the characters incorrectly was that they were inserted into the DB incorrectly. This happened because the MySQL client program is a DOS program. And dos treats characters differently than Win. So when I inserted 'ö' into the DB with the DOS client, it was inserted incorrectly, but still displayed the correct way when writing queries in the DOS client, because the characters were displayed in the 'DOS way'. But PHP accesses the DB via Win (or the network), and therefore displayed the characters the way Win saw them.
I also tried inserting values into the DB using a Win clientprogram, which uses ODBC, and the characters changed in the DOS client exactly the same way PHP had changed them.

How typical! This almost the same as when your colorprinter prints the colors incorrectly, and you immediatly think there is something wrong with the printer, although it can also be the monitor that displays the colors incorrectly. But for some reason we almost automatically seem to think the problem is where it occurs...

johan