Solved

PHP PDO to microsoft access foreign character encoding issues

Posted on 2014-03-06
5
1,904 Views
Last Modified: 2014-03-10
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
0
Comment
Question by:weaverk
  • 3
5 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 
LVL 10

Assisted Solution

by:aboo_s
aboo_s earned 150 total points
Comment Utility
the file with query i it saved in utf8 format?
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 350 total points
Comment Utility
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
 
LVL 1

Author Closing Comment

by:weaverk
Comment Utility
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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now