[SOLVED] PHP & MySQL – Problems with UTF-8 encode

Issue

I have a form with a input that accept UTF-8 characters:

<form method="post" action="faz-personagem.php" accept-charset="UTF-8">
  <div class="row">
    <label for="nome">Nome</label>
    <input type="text" name="nome">
  </div>
  ...
  <button type="submit">Enviar</button>
</form>

And a script that send the data to a database:

<?php

header("Content-Type: text/html;charset=UTF-8");

$conexao = mysql_connect('localhost', 'root', 'pass');
mysql_select_db('pan-tactics');

$nome   = $_POST['nome'];

$nome = utf8_encode($nome);

$sql = "INSERT INTO personagens VALUES";
$sql .= "('$nome')";

$resultado = mysql_query($sql);

echo 'Personagem criado com sucesso.';

mysql_close($conexao);

?>

I also have specified in the creation of the database the collation utf8_unicode_ci and yet what I get is wrong special characters:

What can I do to fix it?

Solution

Keep in mind that collation is not the same as charset. You need to set your database and table to be in UTF-8 encoding. This can be done with running this SQL command (only need to be done once).

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

Furthermore, you should set the mysql_* connection to UFT-8. This code should be placed directly after connecting to your database.

mysql_set_charset("utf8");

I see you already set the PHP header to UTF-8 as well, so that’s good.

Keep in mind that usage of mysql_* functions are deprecated and no longer maintained; you should switch to PDO or MySQLi for security reasons.

If neither of these steps helped you, you may need to save the document itself as UTF-8 w/o BOM, this can be done in Notepad++ as Format -> Convert to UFT-8 w/o BOM.

Answered By – Qirel

Answer Checked By – Pedro (BugsFixing Volunteer)