Doctrine 2: getting strange caracters from MySQL table

Question

Im trying to show a text like this:

1ºA

stored in a MySQL table ('level').

After querying the table 'level' using this code:

/**
 * @Route("/list-level", name="list_level")
 */
public function listAction(Request $request)
{
    $em = $this->getDoctrine()->getEntityManager();

    $levels = $em->getRepository('AppBundle:Level')->findAll();
    var_dump($levels[0]->getName());
    die("fasf");

the var_dump() returns this:

Im using Symfony, so the information related to database connection is cached in the file var/cache/prod/appProdProjectContainer. So I have opened it to check the value of charset parameter and I have found this:

protected function getDoctrine_Dbal_DefaultConnectionService()
{
    $a = new \Symfony\Bridge\Doctrine\ContainerAwareEventManager($this);
    $a->addEventListener(array(0 => 'loadClassMetadata'), ${($_ = isset($this->services['doctrine.orm.default_listeners.attach_entity_listeners']) ? $this->services['doctrine.orm.default_listeners.attach_entity_listeners'] : $this->get('doctrine.orm.default_listeners.attach_entity_listeners')) && false ?: '_'});

    return $this->services['doctrine.dbal.default_connection'] = ${($_ = isset($this->services['doctrine.dbal.connection_factory']) ? $this->services['doctrine.dbal.connection_factory'] : $this->get('doctrine.dbal.connection_factory')) && false ?: '_'}->createConnection(array('driver' => 'pdo_mysql', 'host' => 'localhost', 'port' => NULL, 'dbname' => 'my_javiergarpe1979', 'user' => 'javiergarpe1979', 'password' => '*******', 'charset' => 'UTF8', 'driverOptions' => array(), 'defaultTableOptions' => array()), new \Doctrine\DBAL\Configuration(), $a, array());
}

As you can see on that function there is written this: 'charset' => 'UTF8', so.. why am I not getting the correct caracters?

Answer 1

Probably you have to update driverOptions in your var/cache/prod/appProdProjectContainer:

protected function getDoctrine_Dbal_DefaultConnectionService()
{
    $a = new \Symfony\Bridge\Doctrine\ContainerAwareEventManager($this);
    $a->addEventListener(array(0 => 'loadClassMetadata'), ${($_ = isset($this->services['doctrine.orm.default_listeners.attach_entity_listeners']) ? $this->services['doctrine.orm.default_listeners.attach_entity_listeners'] : $this->get('doctrine.orm.default_listeners.attach_entity_listeners')) && false ?: '_'});

    return $this->services['doctrine.dbal.default_connection'] = ${($_ = isset($this->services['doctrine.dbal.connection_factory']) ? $this->services['doctrine.dbal.connection_factory'] : $this->get('doctrine.dbal.connection_factory')) && false ?: '_'}->createConnection(array('driver' => 'pdo_mysql', 'host' => 'localhost', 'port' => NULL, 'dbname' => 'my_javiergarpe1979', 'user' => 'javiergarpe1979', 'password' => '*******', 'charset' => 'UTF8', 'driverOptions' => array('1002'=> "SET NAMES 'UTF8' COLLATE 'utf8_general_ci'"), 'defaultTableOptions' => array()), new \Doctrine\DBAL\Configuration(), $a, array());
}

Set 'driverOptions' => array('1002'=> "SET NAMES 'UTF8' COLLATE 'utf8_general_ci'") instead of 'driverOptions' => array().

Answer 2

Probably it is a browser problem in not having <meta charset=UTF-8> in the header.

See "black diamonds" in http://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored

º is the "MASCULINE ORDINAL INDICATOR".

If you SELECT HEX(...) from the table, you should see C2BA for that character. Or 31C2BA41 for 1ºA. If so, then it was correctly stored as utf8 (or utf8mb4).

Case 1 (original bytes were not UTF-8):

• The bytes to be stored are not encoded as utf8. Fix this.
• The connection (or SET NAMES) for the INSERT and the SELECT was not utf8/utf8mb4. Fix this.
• Also, check that the column in the database is CHARACTER SET utf8 (or utf8mb4).

Case 2 (original bytes were UTF-8):

• The connection (or SET NAMES) for the SELECT was not utf8/utf8mb4. Fix this.
• Also, check that the column in the database is CHARACTER SET utf8 (or utf8mb4).

Please provide SHOW CREATE TABLE if you have other issues.

Answer 3

There are multiple points where this could have gone wrong.

1. Check the character set which you are using on the database. Also check the table because those can have their own encoding.
   See this answer for more detail: https://stackoverflow.com/a/202246/2232127
2. Check the character set which you are using for the connection (you have set this already)
3. Check the character set in which the page is displayed. You can set this via a this meta tag: <meta charset="UTF-8"> in the <head> tag.
4. You have to use a font that supports this character. Most fonts shouldn't show the <?> character but instead may show something similar to ° in this case.