German Umlaute in Mysql/Phpmyadmin

Question

I have Flex application with UT8-encoding. It is sending back to the Server (PHP), and the data gets written in to Mysql (UT8 charset, utf8_general_ci). I have no problems at all writing/reading Umlaute from/to the database.

I only realized, by looking at the data with PHPmyadmin that the Umlaute get somehow converted to:

ö => ö ü => ü etc.

As I said, I had no problems at all. The strange thing is, when I write Umlaute directly with PHPmyAdmin into the database, they are displayed correctly

Now I am printing a PDF, and I need to call ut8_decode() on all values to display them correctly. However, those entered manually into the DB (which are displayed correctly in phpmyadmin) do not get decoded.

I assume that those are not written to the Db in UT8 then, since decoding malforms them?

1. )But why are in the first place UT8-encoded values displayed in this strange way in the DB? 2.) How can I enter data into mysql with PHPmyAdmin in UTF-encoding? (I have set the connection to ut8).

Thx, Martin

Solution 1

I struggled with the same problem for a long time. Run this query as soon as you connect to the database and your web application will display characters as they appear in phpmyadmin:

SET NAMES 'utf8'

For some reason MySQL is set up on my systems to assume input and output are encoded as latin1, which means when I send it utf8 input it stores it in the database incorrectly, but because the conversion is reversed for output, the mess is undone and it displays correctly in the browser (except when using phpmyadmin, which displays it faithfully). This is only true when the conversion results in characters that are permitted by the character set used in the database field where it is stored, so you can get errors unless you stop this conversion from happening with the above query.

Solution 2

You can either execute a query SET NAMES 'utf8' everytime you open a connection to the MySQL server, or if you've admin access to the sql server, you can add these lines to your my.cnf file. This will set UTF-8 as default char set for every new connection and every new created database and table:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

Solution 3

I had the same problem. I saved data with PHP in my database. When I showed the data by a PHP script, everything was fine. But when I watched the data in phpmyadmin, umlauts were shown wrong. The core of the problem was PHP running on my windows machine was by default communicating in latin1 with the mysql server, despite the sever itself was set to utf8. I solved the issue by manually setting a charset after I connected to my mysql-server by PHP:

$mysqliObj->set_charset("utf8");

Now the data is stored and shown correctly.

Solution 4

The fundamental fact you have to keep in mind when talking about this kind of problem is this: bytes and text are two different things, and whenever you convert between them you have to use the correct character encoding i.e. the same that was/will be used for the reverse conversion and one that supports all the characters that are being used.

The problem is that with every additional conversion and every additional application that is involved, there is a chance for things to go wrong. Web apps are the worst possible case in this regard since there are always multiple conversions (usually 2*(number of applications-1)) and several different applications involved - at the very least: the web app, the browser and the DB. In your case, PHPMyAdmin as well.

It is hard to tell which conversion went wrong when there are so many. However, it looks like your problems are caused by PHPmyAdmin since it displays umlauts as two characters, which is typical for applications that try to interpret UTF-8 encoded bytes as Latin1. Now the question is whether the erroneous conversion happens when PHPmyAdmin gets the data from the DB or when it sents the data to your browser. What is the encoding declared by PHPmyAdmin in the headers of its HTML pages? Do you have the option of accessing the DB through a non-web app such as DbVisualizer? If so, do that, since it eliminates one conversion (and thus potential for error).

View more solutions

Comments

[Pascal Klein]

There seem to be no upvotes and nothing on this thread. So I just want to say, that "SET NAMES 'utf8' solved my issue

[Captain GouLash]

solved it for me too. Thumbs up. $db->query("SET NAMES 'utf8'");