Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PHP PDO to microsoft access foreign character encoding issues

Posted on 2014-03-06
5
Medium Priority
?
2,410 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 111

Expert Comment

by:Ray Paseur
ID: 39909215
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 600 total points
ID: 39909228
the file with query i it saved in utf8 format?
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1400 total points
ID: 39910472
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
ID: 39917052
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 111

Expert Comment

by:Ray Paseur
ID: 39917124
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first step to building an amazing About page is to figure out what you want the page to say about your company. You then must grab the attention of the reader, boast a bit, tell a story and let others brag about you. With a little bit of thought…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

916 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