5

When i see data as stored on mysql database using phpmyadmin, the characters are stored exactly as é à ç however when i use php to display these data on an html document that has the exact following structure:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title></title>
</head>

<body>
</body>
</html>

I got square instead of accented character, however, i don't have this issue with any accented characters on static content that haven't been loaded from mysql in the same page.

when i see on the source code of the page they seem to be identical! for example:

part of static data on the source code displayed as:

éçà

part of mysql origin data:

éçà

i tried replacing

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

with

<meta http-equiv="Content-Type" content="text/html; charset=windows-1552" />

and as result i got mysql one fixed, static with squares !

any hints?

Mbarry
  • 241
  • 1
  • 2
  • 13
  • 1
    Looks like a misconfiguration issue. Maybe your DB drivers are not using UTF-8? The fact that the data that comes from the DB shows OK when you change to `windows-1552` and the static files do not can mean that your source file is (correctly) in UTF-8, but the data from your DB is arriving in the wrong encoding format. Whatever is going on, stick to UTF-8. – ubik Apr 20 '13 at 17:20
  • Thank you for your reply, i have checked the database and i found the Collation is set to `latin1_swedish_ci`, could that be the source of the problem? – Mbarry Apr 20 '13 at 17:24
  • 1
    Yes, that's the source of the problem, you are storing your data in ascii in mysql but then trying to show it as utf8 in your html.. – Nelson Apr 20 '13 at 17:26
  • Yes, that's the issue, most probably. – ubik Apr 20 '13 at 17:28
  • many choices at the `utf-8` menu level on phpmyadmin, which the right one should i pick? – Mbarry Apr 20 '13 at 17:30

2 Answers2

5

This is quite common charset issue, you need to set connection encoding manually for MySQL connection (those should be first queries you execute after establishing connection):

SET NAMES utf8;
SET CHARACTER SET utf8;

And also make sure every table has CHARACTER SET set to UTF-8.

Or you could also update server configuration.

Community
  • 1
  • 1
Vyktor
  • 19,006
  • 5
  • 53
  • 93
  • Fixed with `SET NAMES utf8;` executed as suggested. Thank you! – Mbarry Apr 20 '13 at 17:38
  • See http://stackoverflow.com/questions/3513773/change-mysql-default-character-set-to-utf8-in-my-cnf for instructions on how to do this automatically on the server side. – ubik Apr 21 '13 at 11:32
2

Looks like a misconfiguration issue. Most probably your DB or drivers are not using UTF-8.

The fact that the data that comes from the DB shows OK when you change to windows-1552 and the static files do not can mean that your source file is (correctly) in UTF-8, but the data from your DB is arriving in the wrong encoding format.

Whatever is going on, stick to UTF-8.

UPDATE: There is a thread that explains how to automatically set the encoding for the connection:

Change MySQL default character set to UTF-8 in my.cnf?

Community
  • 1
  • 1
ubik
  • 4,134
  • 2
  • 21
  • 28
  • Thank you Pedro, the issue has been fixed and i won't stick to windows-1552. where there's a term windows there's issues lol. thank you – Mbarry Apr 20 '13 at 17:40