PHP PDO to microsoft access foreign character encoding issues

Hi,
im using PDO to write to an access .mdb file, and overall it works as i want, however if i enter a value like "Ouham-Pendé" it will be stored in the database as "Ouham-Pendé"

the db connection -
...
try{
            $connection = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$dbName; Uid=$dbUsername; Pwd=$dbPassword;");
        }
        catch(PDOException $e){
            return $e->getMessage();
        }
...

Open in new window


the query -

...
$x = $connection->prepare('INSERT INTO table(id, area, title) VALUES (?, ?, ?)');
$x->execute(array($y,84,"Pendé"));
...

Open in new window


-I  have tried setting the encoding like this, but didnt seem to make a difference...

$connection->exec("set names utf8");

Open in new window


Any magic solutions to make this work as it should will be very much appreciated!

~weaverk
LVL 1
weaverkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
This is a character encoding collision.  Please read this article to understand the issue and to find the correct ways to handle the different character sets.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11880-Unicode-PHP-and-Character-Collisions.html
0
aboo_sCommented:
the file with query i it saved in utf8 format?
0
Ray PaseurCommented:
I will be updating the article about Character Collisions soon, and that will cause it to be automatically unpublished until an editor can review it.  I don't have an Access data base to test with, but I can show you the principles I used with PHP, PDO and MySQL.

First of all, make sure that your text editor or IDE can save the script file in UTF-8 format.  I use Textpad, and it must be told to use UTF-8.

In the script here, the important part for PHP starts around line #30 where we set the internal and output encoding.

We tell PDO that it is UTF-8 at line #39 and #51.  Note that we use utf8mb4 to tell MySQL that we want full four-byte capability.  YMMV for Access

The table is created with a default character set of utf8mb4 on line #74

And when the data is printed out, we can see that the character count and the byte count are not the same because of the multi-byte UTF-8 characters near line #145

<?php // EE_PDO_latin1_to_utf8.php
error_reporting(E_ALL);
echo '<pre>';


// EXTENDED-ASCII CHARACTERS COLLIDE WITH UTF-8 ENCODINGS AND CANNOT BE RENDERED CORRECTLY
// DEMONSTRATE HOW TO USE PDO WITH A DATA BASE TABLE IN BOTH EXTENDED-ASCII AND UTF-8


// CHOOSE A BROWSER-DISPLAY CHARSET VALUE FROM THE URL ARGUMENT utf-8, windows-1252, iso-8859-1, iso-8859-15, etc.
$charset = !empty($_GET['charset']) ? $_GET['charset'] : 'utf-8';
$self    = $_SERVER['PHP_SELF'];
echo <<<EOD
<meta charset="$charset" />
VIEWING THE DATA WITH BROWSER CHARSET = $charset
<a href="$self?charset=iso-8859-1">ISO-8859-1</a>
<a href="$self?charset=utf-8">UTF-8</a>

EOD;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";



// SWITCH PHP ENCODING TO UTF-8
$mbie = "utf-8";
mb_internal_encoding($mbie);
mb_http_output($mbie);
echo PHP_EOL . "MULTIBYTE ENCODING($mbie)";
echo PHP_EOL;


// OPEN A NEW CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$dsn = "mysql:host=$db_host;dbname=$db_name;charset=utf8mb4";
try
{
    $pdo = new PDO($dsn, $db_user, $db_word);
}
catch(PDOException $e)
{
    var_dump($e);
    die(' NO PDO Connection');
}

// SET PDO TO USE UTF-8
$pdo->setAttribute( PDO::MYSQL_ATTR_INIT_COMMAND, 'SET NAMES utf8mb4');

// SET PDO TO TELL US ABOUT WARNINGS OR TO THROW EXCEPTIONS
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );


// CREATE THE TEST DATA IN UTF-8
$iso
= array
( '1' => 'Öh löök, umlauts in the Encyclopædia!'
, '2' => 'At Ðe lónlí blú bojs concert, Françoise ƒlew a paper airplane'
, '3' => 'For €3 (or £2) you can order ½ martini with ± 1 olive: Ý'
)
;

// CREATING A TABLE FOR OUR TEST DATA - NOTE THE CHARACTER SET
$sql
=
"
CREATE TEMPORARY TABLE iso_table
( id    INT          NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(128) NOT NULL DEFAULT ''
) CHARACTER SET=utf8mb4
"
;
// RUN QUERY TO CREATE THE TABLE
try
{
    $pdos = $pdo->query($sql);
}
catch(PDOException $e)
{
    var_dump($e);
}


// PREPARE THE QUERY TO LOAD THE DATA ("PREPARE" ONLY NEEDED ONCE)
$sql
=
"INSERT INTO iso_table
(  fname ) VALUES
( :fname )
"
;
try {
    $pdos = $pdo->prepare($sql);
}
catch(PDOException $e)
{
    var_dump($e);
}


// LOADING OUR DATA INTO THE TABLE
foreach ($iso as $fname)
{
    // USE THE ARRAY OF KEYWORD => VALUE TO ATTACH fname STRING
    try
    {
        // RUN THE QUERY TO INSERT THE ROW
        $pdos->execute( array('fname' => $fname) );
    }
    catch(PDOException $e)
    {
        var_dump($e);
        die();
    }
    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $pdo->lastInsertId();
    echo "PDO INSERTED A ROW CONTAINING <b>" . $fname . "</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}


// CONSTRUCT AND RUN A SELECT QUERY
$sql = "SELECT fname FROM iso_table ORDER BY id";
try
{
    $pdos = $pdo->query($sql);
}
catch(PDOException $e)
{
    var_dump($e);
}


// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
echo "USING PDOStatement::FetchAll(PDO::FETCH_OBJ) WITH $mbie: ";
echo PHP_EOL;
while ($rows = $pdos->fetchAll(PDO::FETCH_OBJ))
{
    // ROW BY ROW PROCESSING IS DONE HERE
    foreach ($rows as $obj)
    {
         $chars = mb_strlen($obj->fname);
         $bytes = strlen($obj->fname);
         echo PHP_EOL . "$chars $mbie CHARACTERS; $bytes BYTES: ";
         echo PHP_EOL . '1...5...10...15...20...25...30...35...40...45...50...55...60...' . PHP_EOL;
         print_r($obj->fname);
         echo PHP_EOL;
    }
}
echo PHP_EOL;

Open in new window

HTH, ~Ray
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
weaverkAuthor Commented:
strangely when i saved the file containing the queries as ANSI, the content was then stored in the database correctly.... i'm hoping this means that the problem is resolved for good, although i have not yet tested extensively,
thanks for the help!
0
Ray PaseurCommented:
Thanks for the points.  I think the "take-away" messages is that character encoding must be consistent across the input data, the PHP scripts, the data base and the output document.  If that's done, you'll likely get good character representations.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.