Here is my code...
$mysqli = new mysqli(HOST, USER, PASSWORD, DATABASE);
mysql_set_charset('utf8');
$sql = "SELECT product_name FROM my_table";
$result = mysqli_query($mysqli, $sql) or trigger_error(mysql_error());
while($row = mysqli_fetch_assoc($result)) {
foreach($row as $key => $value) {
//here, $value is populated but may contain a bad character
$value = replaceAccents($value);
//here, $value is now empty if it contained a bad character
}
}
I have created all my tables with...
DEFAULT CHARSET=utf8
...and I have also called...
ALTER DATABASE my_database CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Most data is returned fine from the table, but if any field contains an accented character (e.g., Crème
) then an unusable value is returned.
I haven't seen a problem like this - only vaguely similar ones, which have led to the above code / changes being implemented.
What is going wrong and how to fix??
Incidentally, if I do the above sql
search in phpmyadmin
, the problematic value (containing Crème
) displays fine.
For completeness, here is my replaceAccents function...
function replaceAccents($str) {
$str = iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $str);
return $str;
}