Save Accents in MySQL Database

Question

I'm trying to save French accents in my database, but they aren't saved like they should in the DB.
For example, a "é" is saved as "Ã©".
I've tried to set my files to "Unicode (utf-8)", the fields in the DB are "utf8_general_ci" as well as the DB itself.
When I look at my data posted through AJAX with Firebug, I see the accent passed as "é", so it's correct.

Thanks and let me know you need more info!

score 60 · Accepted Answer · edited Apr 05 '18 at 12:23

60

Personally I solved the same issue by adding after the MySQL connection code:

mysql_set_charset("utf8");

or for mysqli:

mysqli_set_charset($conn, "utf8");

or the mysqli OOP equivalent:

$conn->set_charset("utf8");

And sometimes you'll have to define the main php charset by adding this code:

mb_internal_encoding('UTF-8');

On the client HTML side you have to add the following header data :

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

In order to use JSON AJAX results (e.g. by using jQuery), you should define the header by adding :

header("Content-type: application/json;charset=utf8");
json_encode(
     some_data
);

This should do the trick

edited Apr 05 '18 at 12:23

deceze

510,633
85
743
889

answered Jan 08 '12 at 19:00

Olivier Kaisin

616
6
2

3

And, if you will use `PDO`, try this: `$db = new PDO("database", "username", "pass", array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'))` – Junior Mayhé Mar 13 '14 at 03:43
4

Just comment that, the function `mysqli_set_charset("utf8")`, needs two parameters, the connection and the charset, so it ends like `mysqli_set_charset($conn, "utf8")`. – Arnau Lacambra Apr 14 '14 at 09:37

score 6 · Answer 2 · answered Jan 31 '11 at 18:17

6

The best bet is that your database connection is not UTF-8 encoded - it is usually ISO-8859-1 by default.

Try sending a query

SET NAMES utf8;

after making the connection.

answered Jan 31 '11 at 18:17

Pekka

442,112
142
972
1,088

1

I was using PHP and the shell_exec command to import an sql file, and this solution was the only one that worked, thanks! – Pierre Dec 22 '16 at 11:33

score 5 · Answer 3 · edited Oct 06 '19 at 21:18

5

mysqli_set_charset($conn, "utf8");

edited Oct 06 '19 at 21:18

N3R4ZZuRR0

2,400
4
18
32

answered Oct 08 '13 at 23:44

Zhanka

51
1
1

score 4 · Answer 4 · answered Jan 31 '11 at 18:40

4

if you use PDO, you must instanciate like that :

new \PDO("mysql:host=$host;dbname=$schema", $username, $password, array(\PDO::MYSQL_ATTR_INIT_COMMAND =>  'SET NAMES utf8') );

answered Jan 31 '11 at 18:40

A.Baudouin

2,855
3
24
28

score 2 · Answer 5 · answered Jul 04 '11 at 15:26

Use UTF8:

Set a meta in your

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

When you connect to your mySQL DB, force encoding so you DONT have to play with your mysql settings

$conn = mysql_connect('server', 'user', 'password') or die('Could not connect to mysql server.');
mysql_select_db('mydb') or die('Could not select database.');
mysql_set_charset('utf8',$conn); //THIS IS THE IMPORTANT PART

If you use AJAX, set you encoding like this:

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

This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. — Vipul Hadiya, Feb 21 '16 at 14:14

score 1 · Answer 6 · answered Jan 31 '11 at 18:18

Have you reviewed http://dev.mysql.com/doc/refman/5.0/en/charset-unicode.html:

Client applications that need to communicate with the server using Unicode should set the client character set accordingly; for example, by issuing a SET NAMES 'utf8' statement. ucs2 cannot be used as a client character set, which means that it does not work for SET NAMES or SET CHARACTER SET. (See Section 9.1.4, “Connection Character Sets and Collations”.)

Further to that:

if you get data via php from your mysql-db (everything utf-8) but still get '?' for some special characters in your browser (), try this:

after mysql_connect() , and mysql_select_db() add this lines: mysql_query("SET NAMES utf8");

worked for me. i tried first with the utf8_encode, but this only worked for äüöéè... and so on, but not for kyrillic and other chars.

score 0 · Answer 7 · answered May 12 '14 at 13:43

PHP(.net) advises against setting charsets after connecting using a query like SET NAMES utf8 because your functionality for escaping data inside MySQL statements might not work as intended.

Do not use SET NAMES utf8 but use the appropriate ..._set_charset() function (or method) instead, in case you are using PHP.

score 0 · Answer 8 · answered Jan 31 '11 at 18:18

You need to a) make sure your tables are using a character encoding that can encode such characters (UTF-8 tends to be the go-to encoding these days) and b) make sure that your form submissions are being sent to the database in the same character encoding. You do this by saving your HTML/PHP/whatever files as UTF-8, and by including a meta tag in the head that tells the browser to use UTF-8 encoding.

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

Oh, and don't forget C, when connecting to the database, make sure you're actually using the correct character set by executing a SET NAMES charset=utf8 (might not be the correct syntax, I'll have to look up what it should be, but it will be along those lines)

user2267379 · Answer 9 · 2022-09-13T19:02:36.917

Ok I have found a working solution for me :

Run this mysql command

show variables like 'char%';

Here you have many variables : "character_set_server", "character_set_system" etc.

To work I have to change "character_set_server" value from "utf8mb4" to "latin1".

All my correct value are :

And other values are :

With theses values the wrong database accent are corrected and well displayed by the server.

But each case can be different.

Save Accents in MySQL Database

9 Answers9

Linked

Related