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?

Can you confirm that it is stored as `1ºA` in the table? @ziiweb — Sagar V, Jul 10 '17 at 11:49
Have you checked if the font you are using in the output has a definition for º? On top of that, what is the output if you query the database through console or i.e. phpmyadmin? — Harrie, Jul 10 '17 at 11:58
What is the character_set for the database and specific table? If it isn't stored as UTF-8 there you might run into problems — JensV, Jul 10 '17 at 14:57
And please confirm that on the output for your page you have defined the charset as UTF-8 in the meta tags — JensV, Jul 10 '17 at 14:59
How did you confirm that it was stored correctly? The only sure way is to fetch the hex and get `31 C2BA 41`. — Rick James, Jul 10 '17 at 21:55

score 3 · Answer 1 · answered Jul 10 '17 at 21:59

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().

score 2 · Answer 2 · answered Jul 10 '17 at 21:53

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.

what exactly should I execute? I have tried `SELECT HEX('C2BA')` and I get `43324241`. Is that a correct result? — tirenweb, Jul 11 '17 at 11:26
`SELECT HEX(col) FROM tbl WHERE ...` as suggested in my link. — Rick James, Jul 11 '17 at 18:38

score 1 · Answer 3 · answered Jul 10 '17 at 15:07

There are multiple points where this could have gone wrong.

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
Check the character set which you are using for the connection (you have set this already)
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.
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.

score 1 · Answer 4 · answered Jul 17 '17 at 07:25

1

Check the table's character set in database. May be its not UTF-8.

answered Jul 17 '17 at 07:25

Girish

213
2
13

Doctrine 2: getting strange caracters from MySQL table

4 Answers4