PHP MySQL utf-8 Euro symbol shown as questionmark on a diamond

Question

So after a whole day of googling and debugging I end up here.

MySQL

set to the following encoding:

db:  utf8_general_ci
table: utf8_general_ci
column: utf8_general_ci, TEXT

I put in some euro symbols and some other weird characters

acentuação €€€€€

PHP (codeigniter)

config

$config['charset'] = 'UTF-8';

dsn

char_set=utf8,dbcollat=utf8_general_ci

I made some queries to compare

model

$query = $this->db->query("SET NAMES latin1");
$query = $this->db->query("SELECT shortdesc,HEX(shortdesc) FROM `contracttypes` WHERE id = 4");
$ret['latin1'] =  $query->row();
$query = $this->db->query("SET NAMES utf8");
$query = $this->db->query("SELECT shortdesc,HEX(shortdesc) FROM `contracttypes` WHERE id = 4");
$ret['utf8'] =  $query->row();
return $ret;;

controller

public function utfhell()  {
var_dump($this->campagne_model->utfhell());
}

This outputs

array (size=2)
'latin1' => 
object(stdClass)[34]
public 'shortdesc' => string 'acentua��o �����' (length=16)
public 'HEX(shortdesc)' => string '6163656E747561C3A7C3A36F20E282ACE282ACE282ACE282ACE282AC' (length=56)
'utf8' => 
object(stdClass)[33]
public 'shortdesc' => string 'acentuação €€€€€' (length=28)
public 'HEX(shortdesc)' => string '6163656E747561C3A7C3A36F20E282ACE282ACE282ACE282ACE282AC' (length=56)

So far so good, on to a

view

<?php header('Content-Type: text/html; charset="utf-8"', true); ?>
<!doctype html>
<html>
<head>
<title>UTFhell</title>
<link rel="stylesheet" href="../assets/css/style.css"/>
<meta charset="utf-8">
<meta http-equiv="Content-Type" content="text/html;charset=utf-8">
...
<?php
echo 'Original : ', $campagne_info->contractName->shortdesc."<br />";
echo 'UTF8 Encode : ', utf8_encode($campagne_info->contractName->shortdesc)."<br />";
echo 'UTF8 Decode : ', utf8_decode($campagne_info->contractName->shortdesc)."<br />";
echo 'TRANSLIT : ', iconv("ISO-8859-1", "UTF-8//TRANSLIT", $campagne_info->contractName->shortdesc)."<br />";
echo 'IGNORE TRANSLIT : ', iconv("ISO-8859-1", "UTF-8//IGNORE//TRANSLIT", $campagne_info->contractName->shortdesc)."<br />";
echo 'IGNORE   : ', iconv("ISO-8859-1", "UTF-8//IGNORE", $campagne_info->contractName->shortdesc)."<br />";
echo 'Plain: ', iconv("ISO-8859-1", "UTF-8", $campagne_info->contractName->shortdesc)."<br />";
echo '€€€€€€€€€€<br>'; 
?>

None of these now show me a normal euro symbol except the final echo statement, they all give me questionmark diamonds for the eurosymbols

store them in DB with base64_encode() and use them with base64_decode — Daniel Krom, Aug 05 '15 at 07:32
What do you get when you run your query in a MySQL editor or phpmyadmin ? SELECT shortdesc,HEX(shortdesc) FROM `contracttypes` WHERE id = 4 — Serhat Akay, Aug 05 '15 at 07:35
I get shortdesc HEX(shortdesc) acentuação €€€€€ 6163656E747561C3A7C3A36F20E282ACE282ACE282ACE282ACE282AC So that works — Olivier Langelaar, Aug 05 '15 at 07:37
Actually, since you see it correctly in the `var_dump()` output, that's irrelevant ... sorry. Other than `$campagne_info->contractName->shortdesc` not being quite the same as what you have in your `utfhell()` method, I don't see why it could go wrong. — Narf, Aug 05 '15 at 12:07
It comes down to the same, Maybe this is a HTML or apache issue — Olivier Langelaar, Aug 05 '15 at 12:55
Yes saved the view as utf8 with atom. maybe the index.php is different. should that be utf8 as well? — Olivier Langelaar, Aug 05 '15 at 20:40
So I put $this->db->query("SET NAMES utf8"); in my model contructor. That works. Why isnt CI pdo mysql doing what its supposed to and do that for me? — Olivier Langelaar, Aug 06 '15 at 08:49

score 1 · Answer 1 · answered Aug 28 '15 at 02:55

The HEX is the utf8 encoding for that string. So the data is in the table 'correctly'.

The black diamond (�) is the browser's way of saying wtf. It comes from having latin1 characters, but telling the browser to display utf8 characters.

You could tell the browser to display "Western", that is avoiding the underlying problems. Remember, the goal is to really use utf8.

Sometimes this occurs together with Question Marks, in which case you must start over.

The cause (probably):

The bytes you had were encoded latin1. You acquired them from somewhere -- file dump online input, etc.
The connection parameters said latin1.
The column/table is declared to be CHARACTER SET said utf8, so during INSERT, they were correctly converted.
When SELECTing, the seting in step 2 was again latin1, so they were converted back to latin1.
When displaying text in a web page, the page's header said that the bytes were utf8.

Solution, Plan A: (Sloppy, but probably workable)

Change #5 so say the appropriate equivalent of latin1.

Solution, Plan B:

Fix the source to be utf8-encoded
query("SET NAMES utf8") (unless there is a way to set it at connect time)
Leave the table/column at CHARACTER SET utf8
Step 2 cover this.
Leave <meta ... UTF-*>.