15

I have some texts in French (containing accented characters such as "é"), stored in a MySQL table whose collation is utf8_unicode_ci (both the table and the columns), that I want to output on an HTML5 page.

The HTML page charset is UTF-8 (< meta charset="utf-8" />) and the PHP files themselves are encoded as "UTF-8 without BOM" (I use Notepad++ on Windows). I use PHP5 to request the database and generate the HTML.

However, on the output page, the special characters (such as "é") appear garbled and are replaced by "�".

When I browse the database (via phpMyAdmin) those same accented characters display just fine.

What am I missing here?

(Note: changing the page encoding (through Firefox's "web developer" menu) to ISO-8859-1 solves the problem... except for the special characters that appears directly in the PHP files, which become now corrupted. But anyway, I'd rather understand why it doesn't work as UTF-8 than changing the encoding without understanding why it works. ^^;)

Netorica
  • 18,523
  • 17
  • 73
  • 108
s427
  • 1,454
  • 5
  • 17
  • 33

6 Answers6

35

I experienced that same problem before, and what I did are the following

1) Use notepad++(can almost adapt on any encoding) or eclipse and be sure in to save or open it in UTF-8 without BOM.

2) set the encoding in PHP header, using header('Content-type: text/html; charset=UTF-8');

3) remove any extra spaces on the start and end of my PHP files.

4) set all my table and columns encoding to utf8mb4_general_ci or utf8mb4_unicode_ci via PhpMyAdmin or any mySQL client you have. A comparison of the two encodings are available here

5) set mysql connection charset to UTF-8 (I use PDO for my database connection )

  PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
  PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET utf8"

or just execute the SQL queries before fetching any data

6) use a meta tag <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>

7) use a certain language code for French <meta http-equiv="Content-language" content="fr" />

8) change the html element lang attribute to the desired language 

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="fr" lang="fr">

and will be updating this more because I really had a hard time solving this problem before because I was dealing with Japanese characters in my past projects

9) Some fonts are not available in the client PC, you need to use Google fonts to include it on your CSS

10) Don't end your PHP source file with ?>

NOTE:

but if everything I said above doesn't work, try to adjust your encoding depending on the character-set you really want to display, for me I set everything to SHIFT-JIS to display all my japanese characters and it really works fine. But using UFT-8 must be your priority

Community
  • 1
  • 1
Netorica
  • 18,523
  • 17
  • 73
  • 108
  • 1
    Yep, `SET NAMES` is probably his problem. – tftd Dec 29 '12 at 17:03
  • 4
    Indeed `SET NAMES` was missing and adding it was enough to correct my problem. Since I don't use PDO, I had to use the following syntax: `mysql_query('SET NAMES utf8');`. Thanks a lot for your detailed answer! – s427 Dec 29 '12 at 17:24
  • well I experienced the same thing and I will not let someone suffer the same thing again :) – Netorica Dec 29 '12 at 17:25
  • 2
    You can also tack on a `;charset=UTF8` to your `new PDO(...)` call. Something like this, `$dbo = new PDO('mysql:host='.$dbhost_name.';dbname='.$database.';charset=UTF8', $username, $password);` – martisj Jul 30 '15 at 13:02
  • I was able to solve issues with Arabic characters using SET NAMES – Ima Aug 15 '15 at 05:15
  • Thanks for mentioning MYSQL_ATTR_INIT_COMMAND for SET NAMES query. Very convenient for handling the issue during POD object construction. – Guney Ozsan Oct 13 '18 at 18:47
17

This works for me

  1. Make your database utf8_general_ci
  2. Save your files in N++ as UTF-8 without BOM
  3. Put $mysqli->query('SET NAMES utf8'); after the connection to the database in your PHP file
  4. Put < meta charset="utf-8" /> in your HTML-s

Works perfect.

Vucko
  • 20,555
  • 10
  • 56
  • 107
2

If your php.ini default_charset is not set to UTF-8, you need to use a Content-type to define your data. Apply the following header at the top of your file(s) :

header("Content-type: text/html; charset=utf-8");

If you have still troubles with encoding, the cause may be one of the following:

  • a database server charset problem (check encoding of your server)
  • a database client charset problem (check encoding of your connection)
  • a database table charset problem (check encoding of your table)
  • a php default encoding problem (check default_encoding parameter in parameters.ini)
  • a multibyte missconfigured (see mb_string parameters in parameters.ini)
  • a <form> charset problem (check that it is sent as utf-8)
  • a <html> charset problem (where no enctype is set in your html file)
  • a Content-encoding: problem (where the wrong encoding is sent by Apache).
Alain Tiemblo
  • 36,099
  • 17
  • 121
  • 153
  • Thanks for your answer. Problem solved by adding the `SET NAMES` command after the database connection. – s427 Dec 29 '12 at 17:28
2

SET NAMES worked for me.

My issue was in one of my editing pages the field with the foreign characters would not display, on the production web pages there was no problem.

Paul R
  • 21
  • 1
2

I know you already have an answer. That's great. But strangely none of these answers solved my issue. I'd like to share my answer for the benefit of the others who may encounter the same issues.

I also had the same problems as the OP, with regards to French accents in a multi-lingual application.

But I encountered this issue for the first time when I had to pass (French accented) data as segments in AJAX calls.

Yes, we must have the database set to work with UTF8. But the fact that AJAX calls had query strings (in my case segments, since I'm using CodeIgniter), I had to simply encode the French text.

To do this on the client-side, use the Javascript encodeURI() function with your data.

And to reverse it in PHP, just use urldecode($MyStr) where data was received as parameters.

Hope this helps.

itsols
  • 5,406
  • 7
  • 51
  • 95
1

  1. Type something full French signs in your (php) file

  2. Save that file as UTF-8

  3. Paste line beneath into your website header

    header('Content-Type: text/html; charset=utf-8');

Page (file) should look good.

If looks good go here for mysql behavior after (SET_NAMES).

Community
  • 1
  • 1
Xfile
  • 674
  • 8
  • 19