[PHP] Configure PDO to avoid encoding errors

On PHP, if you use MySQL as a database, you can’t miss PDO (PHP Data Objects), which allows you to bridge the gap between your application and your data.

Generally, and also according to the PHP documentation, a classic connection to your database is done in the following way:

$dbhost = 'localhost';
$dbname = 'my-db';
$user = 'my-user';
$pass = 'my-pass';

$pdo = new PDO(
    dsn: "mysql:host=$host;dbname=$dbname",
    username: $user,
    password: $pass,
);

So we have a fairly simple connection code, which only takes into account our basic connection parameters. Eventually, to handle the most obvious, but the most fatal error – the error in one of these parameters, and therefore the impossibility of accessing our data – we can proceed in this way to catch the error which would occur:

try {
    $dbhost = 'localhost';
    $dbname = 'my-db';
    $user = 'my-user';
    $pass = 'my-pass';

    $pdo = new PDO(
        dsn: "mysql:host=$host;dbname=$dbname",
        username: $user,
        password: $pass,
    );
} catch (PDOException $e) {
    die('Connexion à la base de données impossible.');
}

We can thus catch any error of the PDOException instance, and inform the user that the link is impossible, while not disclosing the why and how to the end user, which would obviously be a security breach.

However, apart from this connection problem, which will only appear in the event of force majeure (MySQL server down, Update of credentials…), you can come across an anomaly which is quite disturbing, which is the encoding error.

Indeed any database has its encoding, and it must be defined upstream. If your connection via PDO is declared with an encoding different from that of your database (or if you do not specify it, the default one), there is a good chance that the translations of the characters saved in your database will not be not the ones you expected.

This is all the more true in for instance the French language. It certainly brings the happiness of having a very flowery language, but also full of characters that are not often native and accepted in many encodings (é, à, ç, ù, ê, ï…).

To overcome this problem, and knowing that the encoding that supports the greatest number of these cases is UTF-8, here is how to proceed to get rid of it:

try {
    $dbhost = 'localhost';
    $dbname = 'my-db';
    $user = 'my-user';
    $pass = 'my-pass';

    // le set names utf8 est super important pour ne pas avoir de problèmes d'accents par la suite :)
    $pdo = new PDO(
        dsn: "mysql:host=$host;dbname=$dbname",
        username: $user,
        password: $pass,
        options: array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8')
    );
} catch (PDOException $e) {
    die('Connexion à la base de données impossible.');
}

Let’s say you want to write the Spanish word “Señor”. Chances are that if your encoding doesn’t match, you’ll see sluggish and unreadable characters in your browser such as:

• Señor
• Se�or
• Se???or
• …

By simply adding an array of options specifying your encoding to the database, MySQL will be able to translate your characters as you wish.

And that’s it, isn’t it easy?