4

Possible Duplicate:
UTF-8 all the way through

Searching high & low for a solution. I've tried many variations before posting the question.

What is required to have names appear the same in phpMyAdmin and html page? Can this even be accomplished?

EDIT 1: It would seem that this is a mysql issue. Why? Because the php generated html page will always show the correct characters. At this point it is only the database that shows incorrectly.

EDIT 2: Clarification. With the original settings shown in code snip and images below,

  1. Enter João and submit
  2. João displayed in database
  3. João display after reload

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

  1. Enter João and submit
  2. João displayed in database
  3. Jo�o displayed after reload

end Edit 2

In a mysql database, viewed with phpMyAdmin: database structure

The items appear in the database like this: (I've modified the first João to appear correct in database)

phpMyAdmin view of 2 entries of same name

And in the html page with encoding set the names appear like (order is reversed & modified has black diamond),

appearance in html page

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

I have tried changing the column collation to utf8_bin, utf8_general_ci, utf8_unicode_ci, all with no change to either side. Also changed the document (BBEdit) from UTF-8 to UTF-8 (with BOM), ISO Latin 1 and Windows Latin 1. Several of these created more black diamonds, making the issue worse. (Set to UTF-8 in images) I even tried to preg_replace ã, é etc with the encoded equivalents.

The short story is, João is entered on the page (content type above), João is in database, and João comes to the html page on refresh.

Looking for ideas. Thanks.

Community
  • 1
  • 1
David
  • 3,285
  • 1
  • 37
  • 54
  • Maybe you need to tell your Database-Connection, that the receiving data is in UTF-8... this doesn't work automaticaly out of the box... Which way you choosed for your connection? mysql(hopefully not), mysqli or pdo? – mineichen Oct 23 '12 at 15:24
  • @Pekka Nice. Thanks for pointing to that reference. – David Oct 23 '12 at 15:35
  • @MarkusI. Using **mysqli** phpMyAdmin reports 'Connection Collation: utf8_general_ci' (also used utf-8_unicode_ci). Is this what you're referring to? – David Oct 23 '12 at 15:36
  • This may seem like a no-brainer, but make sure the character sets you are concerned with are installed and enabled for your browser as well. I've had at least one case where everything was working fine but displaying wrong because of a local configuration. – Joshua Kaiser Oct 23 '12 at 16:35
  • Try to add $mysqli->set_charset("utf8"); after setting up your Connection... – mineichen Oct 24 '12 at 09:12

4 Answers4

7

Character set issues are often really tricky to figure out. Basically, you need to make sure that all of the following are true:

  • The DB connection is using UTF-8
  • The DB tables are using UTF-8
  • The individual columns in the DB tables are using UTF-8
  • The data is actually stored properly in the UTF-8 encoding inside the database (often not the case if you've imported from bad sources, or changed table or column collations)
  • The web page is requesting UTF-8
  • Apache is serving UTF-8

Here's a good tutorial on dealing with that list, from start to finish: https://web.archive.org/web/20110303024445/http://www.bluebox.net/news/2009/07/mysql_encoding/

It sounds like your problem is specifically that you've got double-encoded (or triple-encoded) characters, probably from changing character sets or importing already-encoded data with the wrong charset. There's a whole section on fixing that in the above tutorial.

code9016
  • 23
  • 5
adrienne
  • 707
  • 1
  • 8
  • 24
  • 1
    Good answer for a methodical approach to solving the question. Thx (It will take a few to digest that) – David Oct 23 '12 at 16:38
  • Yeah, it's a complicated set of interlocking problems. I spent four hours helping someone deal with a version of this a few months back. Turned out to be double-encoded characters in the database, which in my limited but bitter experience is painfully common. – adrienne Oct 23 '12 at 21:59
1

make sure your DB connection is using UTF-8 as well. Try putting the below line on top of your page,

mysql_query("SET NAMES utf8");
Teena Thomas
  • 5,139
  • 1
  • 13
  • 17
  • Wow, I was just working on that. Included it in the db entry script (before entry) and now: `João` in db, `Jo�o` in html. No luck. – David Oct 23 '12 at 15:34
  • What happens with data you've inserted and retrieved using the correct char set in the db session? – symcbean Oct 23 '12 at 15:57
  • @symcbean I think you're asking the question for the anwswer just above your post. Adding the query coder1984 suggested 'reversed' the issue. See edit 2 for summary. – David Oct 23 '12 at 16:06
  • Don't understand your reply. In your first comment on coder1984's answer it's not clear what value you retrieved from the database - the one originally inserted, the amended one, or one inserted via php using the right connection type. – symcbean Oct 23 '12 at 21:31
0

Default PHP hates UTF8. Make sure that you're using mbstring functions rather than the usual built-in string functions.

Sammitch
  • 30,782
  • 7
  • 50
  • 77
  • The script is not using any string functions. In fact, the strings are being sent to the database with ajax. Then the page reloads. It **is** a php generated page. – David Oct 23 '12 at 15:47
  • It would be nice to see some code in your question. – Sammitch Oct 23 '12 at 15:53
0

Make sure that your html page, along with the scripts participated in the AJAX data exchange are being served with a proper HTTP headers including

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

As html-side encoding settings might be just ignored by browsers

Eugene
  • 362
  • 1
  • 4
  • 11
  • Eugene, you might have something here. The request is: Accept-`Charset:ISO-8859-1,utf-8;q=0.7,*;q=0.3`, while the response is: `Content-Type:text/html; charset=utf-8` To be honest, I'm not sure who's doing what to whom, with this character set issue... The request has 2 sets. (?) – David Oct 23 '12 at 16:01
  • What are the response headers for the page showing Jo�o instead of João? – Eugene Oct 23 '12 at 16:07
  • Great question. The same as my answer above the question. – David Oct 23 '12 at 16:14
  • Hm... May be your previous records have been stored with the wrong encoding? Can you try to add some international text from phpMyAdmin to make sure that it;s a pure UTF-8, and then try to see what will be rendered with your HTML – Eugene Oct 23 '12 at 16:19
  • 1
    It might also be helpful to check headers for your AJAX requests/responses – Eugene Oct 23 '12 at 16:19