German Umluit in Mykle / Pampyadmin

Question

I have a UT8 encoded Flex application. It is sent back to the server (PHP), and the data is written to Mysql (UT8 charset, utf8_general_ci). I have no problem writing / reading Umlaute from / to the database.

I just realized, looking at the data with PHPmyadmin, that Umlaute is somehow converted to:

ö => ö ü => ¼, etc.

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

Now I am printing the PDF, and I need to call ut8_decode () for all the values ​​in order to display them correctly. However, those entered manually in the database (which are displayed correctly in phpmyadmin) are not decoded.

I assume that they are not written to Db in UT8, then, since decoding distorts them?

• ) But why are UT8 values ​​encoded in this strange form in the database in the first place? 2.) How can I enter data in mysql with PHPmyAdmin in UTF encoding? (I established a connection with ut8).

thanks Martin

Answer 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 the characters as they appear in phpmyadmin:

SET NAMES 'utf8'

For some reason, MySQL is configured on my systems to suggest that input and output are encoded as latin1, which means that when I send its input utf8, it does not store it correctly in the database, but since the conversion is reversible for output, the mess is canceled and displays correctly in the browser (except when using phpmyadmin, which displays correctly). This is true only when the conversion results in characters that are allowed by the character set used in the database field where it is stored, so you may receive errors if you do not stop this conversion due to the above request.

Answer 2

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

 [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 

Answer 3

The fundamental fact that you should keep in mind when dealing with this problem is this: bytes and text are two different things, and whenever you convert between them, you must use the correct character encoding, i.e. the same that was / will be used for the inverse transform and for those that support all the characters used.

The problem is that with every additional conversion and every additional application that is involved, there is a chance that everything will go wrong. Web applications are the worst possible case in this regard, as there are always several conversions (usually 2 * (number of applications-1)) and several different applications - at least: a web application, a browser and a database. In your case also PHPMyAdmin.

It is hard to say which transformation went wrong when there are so many. However, it seems that your problems are caused by PHPmyAdmin, as 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 occurs when PHPmyAdmin receives data from the database or when it transfers data to your browser. What is the encoding declared by PHPmyAdmin in the headers of HTML pages? Do you have the ability to access the database through a non-web application such as DbVisualizer ? If so, do it as it eliminates one conversion (and therefore potential for error).

Answer 4

There are many different places to set a character set in MySQL, which is great.

It looks like you are not actually saving UTF8, but instead storing UTF8 strings as latin1. If they somehow convert to UTF8 when reading from the database, they will still display correctly in your application.

Do you establish your connections with UTF-8, for example?

 SET CHARACTER SET utf8; SET SESSION character_set_server = utf8; SET character_set_connection = utf8; 

Answer 5

Here is one possibility:

It looks like phpMyAdmin displays UTF-8 data as Latin-1. Check the Content-Type header that phpMyAdmin returns. If you have firefox using the webdev toolbar, you can directly view the headers by going to the Information section → View response headers or information → View page information

Answer 6

I had the same problem. I saved data with PHP in my database. When I showed the data using a PHP script, everything was in order. But when I looked at the data in phpmyadmin, the umlauts were not shown correctly. The core of the problem was that PHP running on my Windows computer reported by default in latin1 with the mysql server, even though sever itself was installed on utf8. I solved the problem by manually setting the encoding after I connected to my PHP mysql server:

 $mysqliObj->set_charset("utf8"); 

Now the data is saved and displayed correctly.

Answer 7

Use mysqli_set_charset(<connection goes here>,'utf8'); immediately after opening the mysqli connection.

By the way, PHP prefers to use this function:

This is the preferred way to change the encoding. Using mysqli_query () to set it (e.g. SET NAMES utf8) is not recommended. See the MySQL character set section for more information. ( http://php.net/manual/mysqli.set-charset.php )

Answer 8

Sorting latin1_general_ci worked for me.