How to display text in UTF8 from mysql /phpmyadmin?

Question

I have a database with multiple tables. The database and table and every row of table is set to utf8_romanian_ci.

Every name which I import that contains diacritics will not be displayed correctly.

For example: If i introduce MaÂehx in table I have just M

The data from table are imported from csv file. In Excel the diacritics appear normally.

How can I resolve this ?

Answer 1

UTF-8 is a fickle mistress. For a typical web application, you have to set UTF-8 at many levels. Even if the phpMyAdmin code works well with UTF8, it does not operate in a vacuum: the server configuration is important. So is the database, and so is the data source for the database. Let's summarize areas of potential friction in a general application, with a particular focus on areas of interest to phpMyAdmin.

A. Data Source

When data is imported into the database, such as from a text file, it must have the right encoding. The diacritics may be showing in Excel, but the encoding could be anything. So you can have a CSV file that looks right but that does not import properly. You can check and convert the encoding of your CSV file in a text editor such as EditPad or an IDE such as Komodo. In Notepad++, which many people have, you can select "Encoding" to see your current encoding, and you can select Encoding / Convert to UTF8 in needed.

B. In your database. The table's character set and collation have to be set to UTF-8. It sounds like you may have already done so.

If not, you need something like:

ALTER TABLE MyTable
DEFAULT CHARACTER SET utf8,
COLLATE utf8_general_ci;

That is the generic setting, but your Romanian collation should work fine too.

C. The connection to the database. You need something like:

$connectDSN = "mysql:host={$db_host};dbname={$db_name};charset=UTF-8";

PhpMyAdmin should connect correctly. On the landing page, you should see

Server charset: UTF-8 Unicode (utf8) 

If not, keep reading.

D. php has to process UTF8. In php.ini, you will want something like:

default_charset = UTF-8     
mbstring.language = Neutral 
mbstring.internal_encoding = UTF-8      
mbstring.encoding_translation = On      
mbstring.http_input = auto      
mbstring.http_output = UTF-8        
mbstring.detect_order = auto        
mbstring.substitute_character = “0xFFFD”    

You can check your server settings with phpinfo(). On a shared host, you usually don't have access to php.ini and have to change settings directly in the script. By the way, remember to use the mb functions.

E. Header setup

For the browser to know what it is getting, it needs to receive a header such as the following sent by php:

<?php  header('Content-type: text/html; charset=UTF-8');   ?>

This should not be an issue with phpMyAdmin. In Firefox, using the Web Developer extension, you can go Information / View Page Information. In the General tab, this will show you the encoding.

F. HTML Meta Tag

This is recommeded to help the browser.

<meta charset="UTF-8">

This should not be an issue with phpMyAdmin. Again check in the browser what encoding it is seeing.

G. Font

The browser may not have the font to display some characters, although modern versions are pretty good about fallback fonts. This is probably not a problem in your case.

So there are many places where things can go wrong.

Hope this helps you identify the problem!

When everything lines up, then magic... You get UTF8 everywhere.

And don't forget:

ಬಾ ಇಲ್ಲಿ ಸಂಭವಿಸು ಇಂದೆನ್ನ ಹೃದಯದಲಿ

ನಿತ್ಯವೂ ಅವತರಿಪ ಸತ್ಯಾವತಾರ