2

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...

Chris
  • 435
  • 1
  • 8
  • 21
  • 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 Dec 11 '15 at 15:53
  • Please post your db code because it could be you aren't setting the collation via php: http://stackoverflow.com/questions/2159434/set-names-utf8-in-mysql – Paul Carlton Dec 11 '15 at 15:55

1 Answers1

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.

Community
  • 1
  • 1
Qirel
  • 25,449
  • 7
  • 45
  • 62
  • Thanks for your feedback. I'm using MySql and I'm going to try your answer ;)) – Chris Dec 11 '15 at 16:02
  • 1
    @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. – Qirel Dec 11 '15 at 16:03
  • 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") – Chris Dec 11 '15 at 16:09
  • 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? – Qirel Dec 11 '15 at 16:11
  • Yes all existing values in the database that weren't properly encoded before @Qirel ! – Chris Dec 11 '15 at 16:14
  • Those were inserted with the wrong charset, so I don't think there's an easy fix to it. You could possibly create a function that selects all records, loop over them, replace the broken characters with the right one, and update those. Honestly I don't know, anything I can think of is changing them manually. – Qirel Dec 11 '15 at 16:18
  • OK Thanks ! I found this https://github.com/neitanod/forceutf8 and I going to loop thru all records and change them ;)) – Chris Dec 11 '15 at 16:22