Write MYSQL data in en / UTF8 format

Question

As I do to save data in the database in Brazilian format with accents, I used it in connection

mysqli_set_charset($conexão, 'utf8');

but now my code is all in PDO as I do to save this data in utf8 in Phpmyadmin database?

<?php

//Pagina de Conexão com o banco de dados

//Define as Variaveis PDO para conexão
define( 'DB_HOST', 'localhost' ); //Local de acesso do banco
define( 'DB_NAME', 'ifsp' ); //Nome do banco de dados
define( 'DB_USER', 'root' ); //Usuario do banco
define( 'DB_PASS', '' ); //Senha do banco de dados

// Tentar conexao
try 
{
    $pdo = new PDO( 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PASS );

    //Se erro ativa o catch 
} catch ( PDOException $e ) 
{
    //Exibe o erro 
    echo "Erro: " . $e->getMessage() . "<br/>";

Answer 1

There are two ways:

• by DNS;
• by command SET NAMES (that can be executed on startup)

The ideal is to use both, because before PHP 5.3.6 the parameter charset of DNS was ignored.

<?php
$dsn = "mysql:host={$host};dbname={$database};charset=utf8"; // PHP >= 5.3.6
$options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', // PHP < 5.3.6
); 
$conexao = new PDO($dsn, $user, $pass, $options);

I prefer to use a condition to execute the command only when necessary:

<?php
$dsn = "mysql:host={$host};dbname={$database};charset=utf8";
$conexao = new PDO($dsn, $user, $pass);
if (version_compare(phpversion(), '5.3.6', '<')) {
    $conexao->exec('SET names utf8;');
}

Answer 2

To set the character set in PDO you must specify in the DSN with the remaining connection parameters.

...
try 
{
    $pdo = new PDO( 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . '; charset=utf8;', DB_USER, DB_PASS );

    //Se erro ativa o catch 
} catch ( PDOException $e ) 
{
...

Or by specifying in the options of PDO.

...
$opc= array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
); 
$dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME;
$dbh = new PDO($dsn, $username, DB_USER, DB_PASS, $opc);
...

---

PDO Mysql DSN - PHP.net

Answer 3

Try it like this:

$pdo = new PDO( 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PASS.':charset=utf8' );

or

$pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass, 
               array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                     PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

Answer 4

From all of you it worked very well Thank you

    <?php

//Pagina de Conexão com o banco de dados

//Define as Variaveis PDO para conexão
define( 'DB_HOST', 'localhost' ); //Local de acesso do banco
define( 'DB_NAME', 'ifsp' ); //Nome do banco de dados
define( 'DB_USER', 'root' ); //Usuario do banco
define( 'DB_PASS', '' ); //Senha do banco de dados

// Tentar conexao
try 
{
    $options= array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
    ); 
    $dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME;
    $pdo = new PDO($dsn, DB_USER, DB_PASS, $options);

    //Se erro ativa o catch 
} catch ( PDOException $e ) 
{
    //Exibe o erro 
    echo "Erro: " . $e->getMessage() . "<br/>";
}