How to convert String with “ (ISO-8859-1) characters to normal (UTF-8)characters?

Question

<li>Jain R.K. and Iyengar S.R.K., “Advanced Engineering Mathematicsâ€, Narosa Publications,</li>

i have lot a raw html string in database. all the text have these weird characters. how can i convert to normal text for saving back it back in database.

$final = '<li>Jain R.K. and Iyengar S.R.K., “Advanced Engineering Mathematicsâ€, Narosa Publications,</li>';
$final = utf8_encode($final);

$final = htmlspecialchars_decode($final);

$final = html_entity_decode($final, ENT_QUOTES, "UTF-8");

$final = utf8_decode($final);

echo $final;

i tried above code, it displays correctly in web browser but still saving the same weird characters in database.

the charset of database is utf-8

Answer 1

$final = '<li>Jain R.K. and Iyengar S.R.K., “Advanced Engineering Mathematicsâ€, Narosa Publications,</li>';

$final = str_replace("Â", "", $final);
$final = str_replace("’", "'", $final);
$final = str_replace("“", '"', $final);
$final = str_replace('–', '-', $final);
$final = str_replace('â€', '"', $final);

for past datas, i replaced the weird characters with UTF-8 characters.

for future datas, i made the charset to utf8 in php, html and databases connections.

Answer 2

“ is "Mojibake" for “. You could try to avoid the non-ascii quotes, but that would only delay getting back into trouble.

You need to use utf8mb4 in your tables and connections. See this for the likely causes of Mojibake.

Answer 3

It safer to use ftfy tool to fix texts https://ftfy.readthedocs.io/en/latest/

Answer 4

I know this has already been answered, but I have encountered the same issue and fix it by fixing the charset in table for future input data.

I am using SQL Server 2017 and Collation is set to SQL_Latin1_General_CP1_CI_AS

For existing characters I have written a script to pull the data from db and search each characters that match and fix those characters.

I have created two csv one contains the data with stray characters(email_templates.csv") and other contains clean html templates(clean_templates.csv).

To find the stray characters in your text/html you can use this online tool as well this is very helpful.

https://freetools.textmagic.com/unicode-detector

UTF-8 Encoding Debugging Chart

https://www.i18nqa.com/debug/utf8-debug.html

HTML codes and HTML special characters

https://psdtowp.net/html-codes-special-characters.html

To verify the same in VS Code I have used extension Render Special Characters below is the link.

https://marketplace.visualstudio.com/items?itemName=miku3920.vscode-render-special-chars

PHP Script to verify and Fix the issue

$fileName = "email_templates.csv";
$ofileName = "clean_templates.csv";

try {

    $stray_chars = array(
        '—' => '-',
        '–' => '-',
        '‘'=> '\'',
        '’' => '\'',
        '“' => '"' ,
        'â€' => '"',
        'Â'=>'',
        'ó'=> "ó",
        "ñ" => "ñ",
        "Ã" => "í",
        "á"=> "á",
        "" => '',
    );
    
    $contents = getEmailTemplateContent();

    $handle = fopen($fileName, "w") or die('Unable to open file');
    $ohandle = fopen($ofileName, "w") or die('Unable to open file');
    $data = [];
    $cleaned = [];
    $i = 0;

    $html = '';


    foreach($contents as $content) {

        $html = $content['html'];
        $clean = str_replace(array_keys($stray_chars), array_values($stray_chars), $html);

        $cleaned[$i] = $content['id']."-xxxx-".$clean;
        $data[$i] = $content['id']."-xxxx-".$html;
        //Fix stray characters in database
        updateEmailTemplateContent($clean, $content['id']);
        $i++;
    }

    fputcsv($handle, $data);
    fputcsv($ohandle, $cleaned);
    fclose($handle);
    fclose($ohandle);
}
catch(\PDOException $e) {
    $jobStatus = 'E';
    $jobError = $e->getMessage();
}