Viewing Tabledata In Utf8

[ErikEgon]

I am in a country where extended characters matter. The database default characterset is UTF8 and data is correctly entered in and retrieved from the database. Representation in the website is also OK.

When querying the database using SQLYog extended characters are displayed as two seperate symbols: an é is represented as é. I am using Consolas as the display font and changing it to Times New Roman or Arial doesn't change a thing. Starting a query with SET NAMES UTF8 doesn't help either.

How can I display tabledata as the characters they were entered?

Kind regards,

Erik.

[peterlaursen]

Please read this FAQ: http://webyog.com/fa…-in-sqlyog.html

This is not a bug. This is a typical 'doble encoding' issue. The client encodes where it should not (the server should do it).

It does not matter that

1) The database default characterset is UTF8 if tables or columns are still latin.

2) Representation in the website is also OK.. PHP etc. will decode client side

You can verify for yourself:

1) Enter data with SQLyog. Now they display OK, right?

2) Dump the table (with mysqldump or SQLyog – not phpMyAdmin or any other PHP tool). Now open in Notepad or another editor that supports UTF8. Here you will see data display exactly like in SQLyog. Right?

3) Try watching the same data in a GUI tool from MySQL/Oracle (Query Browser or Workbench).

[peterlaursen]

 .. and fonts are completely irrelevant for the discussion BTW. 

[ErikEgon]

Hi Peter,

Thank you for responding at such short notice. Still chewing in the information.

Okay, so how do I go about when I need:

1. A website where customers must leave personal data (first and lastnames with extended characters do happen)

2. A database that supports extended characters (I need to store the data)

3. An easy GUI like SQLYog to printout readable data when querying

This is the setup:

1. The webpage (meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″) is in UTF-8.

2. I told the form (accept-charset=”utf-8″) it should send all entered data in UTF-8.

3. I setup the PHP database connection using SET NAMES 'utf8'

4. Created a test database with default characterset utf8

5. Created a table with Character Set = utf8, Collation = utf8_general_ci

6. All varchar columns are in Character Set = utf8, Collation = utf8_general_ci

7. Retrieving previously entered data from the database is correctly displayed, without the help of any PHP function, in the test page.

8. In MySQL the data is displayed as mentioned in the first post.

What is wrong? If you could share any thoughts, it would be much appreciated, not just by me ….

Never thought this was a MySQL bug …

Happy new year!

Erik.

[ErikEgon]

Peter,

When I dump the table on my local PC the data in the file shows, as you expected, two characters for a simple é.

I thought that telling MySQL it should expect UTF-8 data it would accept and understand multibyte characters and store it as multibyte characters. And apparently, it does.

So I would rather expect that I should create a SQLYog UTF-8 connection so that SQLYog would send UTF-8 data and accept and display UTF-8 data on return.

Stil unsure if this is a situation that has a 100% satisfying answer: perhaps all my wishes just don't mix.

Regards,

Erik.

[peterlaursen]

Maybe you could share a small 'project' consisting of a table dump + what HTML and/or PHP files are involved to demonstrate the issue. .If you don't want to post in public please create a support ticket by sending a mail to [email protected]. A very small 'project' (like a table with 5 rows and a PHP file with 20 lines!) would be best.

It it no secret that we had similar complaints over time. I am no web developer myself, but we have a bunch of engineers in our SQLyog team and also our teams taking care of our IssueBurner application (http://issueburner.com/) and the developer team taking care of our website and Customer Portal. They are all PHP applications using a MySQL backend with UTF8 data. We dont face this issue with any of those apps. So undoubtedly we can advise.

The dump tells what is stored in the database just like SQLyog does. Some clients may decode it. But we believe that server alone should encode and decode (from/to character_set_client and character set used for storage).

BTW: When you wrote “8. In MySQL the data is displayed as mentioned in the first post.” I think you meant “8. In *SQLyog* …” . Right? Did you try other clients?

[ErikEgon]

Quote:

BTW: When you wrote “8. In MySQL the data is displayed as mentioned in the first post.” I think you meant “8. In *SQLyog* …” . Right? Did you try other clients?

Yes, SQLyog was what I meant indeed. But I also checked this on the commandline using Putty a few minutes ago and the mysql client tool gives me the exact same results.

I'd be more than happy to share. I think this is an issue that headaches not only me!

Please find attach a zip file containing a very, very basic “index.php” that lets one insert records in a database and immediately displays the results. It also contains the test database containing the table and one example record.

Regards,

Erik.

[ErikEgon]

Sorry, lost the attachment, I think. Another try …

[ashwin]

Follow these steps to attach files here:

1) Click on “Add Reply”. (“Fast reply” option does not have an option to attach files)

2) Below the editor there is an Attachment section.

3) Here, click on browse to select the required file.

4) Now click on “Attach This File”.

[peterlaursen]

and a 5th step (optional):

You may click the 'attach to post' link and something like

Code:

[attachment=1680:file.name]

.. will display. This you may copy where you want if you want the thumbnail to appear a specific place.

If you are not able to attach files here for some reason please try to .zip what you have or create a support ticket by sending a mail to [email protected] file format may not be supported here or the file may be too big.

[ErikEgon]

I've just sent it through the mail. The zip-file is very small: 1,798 bytes.

[peterlaursen]

We received the mail but there is no attachment.  Are you sure that you don't have a very restrictive mail etc. filter setting removing attacments with any Kind of code inside (even PHP scripts)? 

[Author]

Posts