2

I have a wordpress website and for some reason all records values that are in Arabic language change to be like this موقع الطب النÙسي although the database and it's tables collation utf8mb4_unicode_ci.To fix this issue i have decided to make script to select all records from each table and then iconv each value to be for example something like this عنوان باللغة العربية.

Now the problem i have tried everything and it's not working for me

    header('Content-Type: text/html; charset=utf-8');

$sql['host']        = "localhost";
$sql['username']    = "root";
$sql['password']    = "";
$sql['database']    = "nafsynet_database";

$connection = mysqli_connect($sql['host'], $sql['username'], $sql['password'], $sql['database']);

if (!$connection) {
    $out .= "Error: Unable to connect to MySQL." . PHP_EOL;
    $out .= "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    $out .= "Debugging error: " . mysqli_connect_error() . PHP_EOL;

    echo $out;

    die();
}

$sql    = "SELECT * FROM `nfsy_options` WHERE 1 LIMIT 10";
$result = mysqli_query($connection, $sql);
$rows   = mysqli_fetch_all($result,MYSQLI_ASSOC);

foreach($rows as $row) {
    $text =  $row['option_value'];

    echo iconv('windows-1256', 'utf-8',$text);echo'<br>';
}

mysqli_close($connection);
Yasser Moussa
  • 2,209
  • 6
  • 26
  • 39
  • change your db all table column `collation` to `utf-8-bin`. Also you can use `utf-8-bin` encoding in your php code too. – Alive to die - Anant Jan 21 '16 at 12:19
  • For debugging purposes please insert `$result = $connection->query("SHOW VARIABLES LIKE 'collation_%'") or die($connection->error); foreach( $result as $row ) { echo join(': ', $row), "
    \r\n"; } die;` right before `$sql = `, run the script and [add](http://stackoverflow.com/posts/34923472/edit) the output to your question     – VolkerK Jan 21 '16 at 12:20

1 Answers1

2

You also need to specify utf8 charset for your database connection, by calling mysqli_set_charset

$connection = mysqli_connect($sql['host'], $sql['username'], $sql['password'], $sql['database']);

if (!$connection) {
    // ..
}

mysqli_set_charset($connection, 'utf8');

$sql    = "SELECT * FROM `nfsy_options` WHERE 1 LIMIT 10";
dsech
  • 36
  • 6
  • Keep in mind that if that is the problem, you probably already have data in your database that has been interpreted with the wrong encoding. In that case you can/should convert the existing data: http://dev.mysql.com/doc/refman/5.7/en/charset-conversion.html – VolkerK Jan 21 '16 at 12:25
  • If the contents are broken directly in database, unfortunately changing the column collation wouldn't help, as OP already said the collation is utf8. For that it is needed to force an encoding change directly in db on the string, something like this http://stackoverflow.com/a/2379380/5660122 – dsech Jan 21 '16 at 12:34
  • A CONVERT to latin1 followed by MODIFY to utf8 should do the trick - though I've never tried that. – VolkerK Jan 21 '16 at 13:00
  • 1
    Thanks man this solution worked for me i just wanted to share it with you `UPDATE wp_options SET option_value = convert(cast(convert(option_value using latin1) as binary) using utf8) where option_id = 3` – Yasser Moussa Jan 21 '16 at 13:42