Can't add an UTF-8 string into MySQL?

Question

I can't add an UTF-8 string into MySQL DB. Here's the code:

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    </head>
    <body>
    <?php
        $temp='papà';
        mysql_query("SET NAMES utf8");
        mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'");
        mysql_query("INSERT INTO comment VALUE ('$temp')") or die(mysql_error());
     ?>
    </body>
 </html>

the variable in DB is a CHAR(120) and with a non-utf8 string it works properly. The error is the generic "You have an error in your SQL Syntax ecc."

Thanks in advance for your help.

What's the character? MySQL's UTF8 isn't "real" UTF8. Did you try utf8mb4? — Matt Ball, May 18 '15 at 17:07
Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using PDO, [it's not as hard as you think](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, May 18 '15 at 17:07
Yeah, tried also with utf8mb4 right now, nothing seems to change. The character is, for example, the € (euro symbol) which is saved as â¬ — SiK, May 18 '15 at 17:16
Silly question but is your PHP file actually encoded in UTF-8 by your text editor? — Alastair McCormack, May 18 '15 at 20:00
Please show us the rest of the Syntax error message -- it points to where the error is ! — Rick James, Jun 26 '15 at 17:07
`â¬` implies Mojibake, which implies that you neither had the `SET NAMES`, nor did you have the column defined to be `utf8`. — Rick James, Jun 26 '15 at 17:08

score 0 · Answer 1 · answered May 18 '15 at 17:15

Use the following code:

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

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

        $temp='papà';
        mysqli_query("INSERT INTO comment VALUE ('$temp')") or die(mysqli_error());
     ?>
    </body>
 </html>

stop using mysql_* and start using mysqli_* or use PDO

score 0 · Answer 2 · answered May 19 '15 at 01:07

After converting to mysqli_*, use set_charset. Don't do the rest of the SETs. (PDO has the charset in the dsn.)

Is the column in the table CHARACTER SET utf8?

Are the bytes in the encoded utf8-encoded? In utf8, then hex for à is C3A0. In latin1, it is F0.

It is almost always better to use VARCHAR than CHAR.

If you have more troubles, do SELECT col, HEX(col) FROMcoment...; -- that will probably give the best clue of what still broken.

Can't add an UTF-8 string into MySQL?

2 Answers2