How character encoding can be done correctly in both php database and mysql

Question

Possible duplicate:
Utf-8 all through

Search high and low for a solution. I tried many options before posting the question.

What do I need for the names to appear the same on the phpMyAdmin and html pages? Could this be achieved?

EDIT 1: It would seem to be a mysql problem. What for? Because the php generated html page will always show the correct characters. At the moment, only the database is showing this incorrectly.

EDIT 2: Clarification. With the initial settings shown in the snapshot of the code and images below,

• Enter João and submit
• Joà £ o is displayed in the database
• Display João after reboot

Adding mysqli_query ( $link, 'SET NAMES utf8' )

• Enter João and submit
• João is displayed in the database
• Jo o is displayed after reboot

end Edit 2

In the mysql database viewed with phpMyAdmin:

Elements are displayed in the database as follows: (I changed the first João to look correct in the database)

And on the html page with a set of encoding, the names look like (the order is changed and changed with black diamond)

Encoding: <meta http-equiv="Content-Type" content="text/html; charset=utf-8">

I tried changing the column sorting to utf8_bin, utf8_general_ci, utf8_unicode_ci, all unchanged in both directions. The document (BBEdit) was also changed from UTF-8 to UTF-8 (with BOM), ISO Latin 1 and Windows Latin 1. Several of them created more black diamonds, which made the problem even worse. (Installed in UTF-8 on images) I even tried preg_replace ã, é, etc. With encoded equivalents.

In short, João is entered on the page (content type above), Joà £ o is in the database, and João comes to the html page when updating.

We are looking for ideas. Thanks.

Answer 1

Character set problems are often very difficult to understand. Basically, you need to make sure that all of the following is true:

• DB connection uses UTF-8
• DB Tables Use UTF-8
• Separate columns in database tables use UTF-8
• The data is actually stored correctly in UTF-8 encoding inside the database (this is often not the case if you imported from bad sources or changed tables or columns)
• Web page requests UTF-8
• Apache Serves UTF-8

Here is a good tutorial on working with this list: from start to finish: http://www.bluebox.net/news/2009/07/mysql_encoding/

It looks like your problem is that you have double encoded (or three-code) characters, possibly due to changing character sets or importing already encoded data with the wrong encoding. There's a whole section on fixing this in the above tutorial.

Answer 2

make sure your DB connection also uses UTF-8. Try placing the bottom line on top of your page,

 mysql_query("SET NAMES utf8"); 

Answer 3

PHP hates UTF8 by default. Make sure you use mbstring and not the usual built-in string functions.

Answer 4

Verify that your html page, along with scripts involved in AJAX communication, is served with the appropriate HTTP headers, including

 Content-Type: text/html; charset=UTF-8 

Since html-side encoding options can simply be ignored by browsers