Wrong encoding with MySql

Question

I have a mysql table structure like this

CREATE TABLE `table_contenu` (
  `id_contenu` int(10) UNSIGNED NOT NULL,
  `id_rubrique` int(10) UNSIGNED NOT NULL,
  `id_membre` varchar(250) NOT NULL,
  `id_contenu_type` int(10) UNSIGNED NOT NULL,
  `titre_fr` varchar(128) NOT NULL,
  `titre_en` varchar(128) NOT NULL,
  `resume_fr` text NOT NULL,
  `resume_en` text NOT NULL,
  `texte_fr` text NOT NULL,
  `texte_en` text NOT NULL,
  `date_start` date NOT NULL,
  `date_end` date NOT NULL,
  `calendrier` varchar(5) NOT NULL DEFAULT 'false',
  `une` varchar(5) NOT NULL DEFAULT 'false',
  `ordre` smallint(5) UNSIGNED NOT NULL,
  `flag` varchar(5) NOT NULL DEFAULT 'true',
  `date_maj` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

On my web site I have a form to submit data

The Charset on my page is <meta charset="utf-8"> and 'texte_fr' has the Collocation utf8_general_ci (I tried with utf8_unicode_ci but it's the same !)

Why when I submit the word 'séances' this word looks 'sÃ©ances' in the data base ?

Thanks for your help...

1. The SQL-connection can be set to a specific charset (different depending on which API you are using - mysqli_ or PDO?). 2. PHP header needs to be set, too: header('Content-Type: text/html; charset=utf-8');. — Qirel
– Qirel, Commented Dec 11, 2015 at 15:53
Please post your db code because it could be you aren't setting the collation via php: stackoverflow.com/questions/2159434/set-names-utf8-in-mysql — Paul Carlton
– Paul Carlton, Commented Dec 11, 2015 at 15:55

Community · Accepted Answer · 2017-05-23 12:30:55Z

4

It's important that your entire line code has the same charset to avoid issues where characters displays incorrectly.

Here's a little list of things that has to be set to a specific charset.

Headers

Setting the charset in both HTML and PHP headers to UTF-8
- PHP: header('Content-Type: text/html; charset=utf-8');
  (PHP headers has to be placed before any kind output (echo, whitespace, HTML))

Connection

You also need to specify the charset in the connection itself.
- PDO (specified in the object itself):
  $handler = new PDO('mysql:host=localhost;dbname=database;charset=utf8', 'username', 'password', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET UTF8"));
- MySQLi: (placed directly after creating the connection)
  * For OOP: $mysqli->set_charset("utf8");
  * For procedural: mysqli_set_charset($mysqli, "utf8");
  (where $mysqli is the MySQLi connection)
- MySQL (depricated, you should convert to PDO or MySQLi): (placed directly after creating the connection)
  mysql_set_charset("utf8");

Database

Your database and all its tables has to be set to UTF-8. Note that charset is not the same as collation.

You can do that by running the queries below once for each database and tables (for example in phpMyAdmin)

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

File-encoding

It's also important that the .php file itself is UTF-8 encoded. If you're using Notepad++ to write your code, this can be done in the "Format" drop-down on the taskbar. You should use the charset UTF-8 w/o BOM.

You can also take a look at this StackOverflow post: UTF-8 all the way through.

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Dec 11, 2015 at 15:56

Qirel

26.6k7 gold badges54 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Chris Over a year ago

Thanks for your feedback. I'm using MySql and I'm going to try your answer ;))

Qirel Over a year ago

@Chris If you're using mysql_, I strongly suggest you convert to mysqli_ or PDO for better security ;-) mysql_ is outdated and deprecated, not to mentioned removed completely in PHP7.

Chris Over a year ago

Yes I know but before i try to solve the Charset. I just put mysql_set_charset("utf8") but now when i want to modify the data I have 'sÃ©ances' in my textarea form . How can I convert all these characters directly in the database before using mysql_set_charset("utf8")

Qirel Over a year ago

I'm not sure I understand what you mean - are you talking about existing values in the database, that weren't properly encoded before? You want to change those?

Chris Over a year ago

Yes all existing values in the database that weren't properly encoded before @Qirel !

|

Collectives™ on Stack Overflow

Wrong encoding with MySql

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related