How to Retrieve UTF-8 Accented Characters from Access via PDO_ODBC?

Unable to Retrieve UTF-8 Accented Characters from Access via PDO_ODBC

The Problem

PDO_ODBC and the native PHP ODBC features return text that is not UTF-8 encoded, regardless of how it is stored in the Access database. This can lead to problems when displaying data in web pages or using other PHP functions that expect UTF-8 input.

Incomplete Fixes

One approach to resolve this issue is to use the utf8_encode() function to convert the returned text to UTF-8. However, this only works for characters encoded in ISO-8859-1. For characters encoded in other encodings, such as Windows-1252, this method will not work.

A more comprehensive solution is to use the mb_convert_encoding() function to convert the returned text to UTF-8. This function can handle a wider range of encodings, including Windows-1252. However, it still does not address the issue of characters that are not stored in the database as Unicode.

The Complete Fix

To fully resolve the problem, COM with ADODB Connection and Recordset objects must be used. These objects allow for the explicit specification of the UTF-8 code page. By opening the connection and recordset with the CP_UTF8 option, all data retrieved from the database will be automatically converted to UTF-8, regardless of how it is stored.

Example Code

Here is an example code snippet that demonstrates how to use the complete fix with ADODB:

<code class="php"><?php header('Content-Type: text/html; charset=utf-8');
?>


<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Access character test</title>


<?php $connStr = 
        'Driver={Microsoft Access Driver (*.mdb)};' .
        'Dbq=C:\Users\Public\__SO\28311687.mdb';
$con = new COM("ADODB.Connection", NULL, CP_UTF8);  // specify UTF-8 code page
$con->Open($connStr);

$rst = new COM("ADODB.Recordset");
$sql = "SELECT Team FROM Teams";
$rst->Open($sql, $con, 3, 3);  // adOpenStatic, adLockOptimistic
while (!$rst->EOF) {
    $s = $rst->Fields("Team");
    echo $s . "<br>\n";
    $rst->MoveNext;
}
$rst->Close();
$con->Close();
?>

</code>

The above is the detailed content of How to Retrieve UTF-8 Accented Characters from Access via PDO_ODBC?. For more information, please follow other related articles on the PHP Chinese website!