PDO breaking UTF8 characters

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have been trying to track down an issue with certain Greek characters
getting jumbled in my web app, and I am stumped. I have the issue isolated
to the characters getting messed up between the MySQL database and PDO. If
I select from the database via the MySQL command line it comes out right. If
I use PHP/PDO it gets jumbled. Any ideas? Here is an example of the issue,
using the Ω character:

% php utf8.php
This is a test Ω of the emergency broadcast system

% utf8.sh
This is a test Ω of the emergency broadcast system

% cat utf8.php
// connect
$db=new PDO("mysql:host=localhost;dbname=testdb", "root", "password");

// force UTF8
$db->query('SET NAMES utf8');

// select single row
$result=$db->query("select entry from employee where id=3640");

// print row
print $row['entry'] . "\n";
% cat utf8.sh
echo "select entry from employee where id=3640" | \
    mysql -h localhost -u root --pass='password' testdb


Re: PDO breaking UTF8 characters

On 11/29/2011 7:19 PM, TnT wrote:
Quoted text here. Click to load it

Okay, let's start with one key thing that will unlock the whole mess:
UTF-8 is not a character set. It is an encoding. Which means in order to
see the right character, you have to decode it in order to represent it.

Quoted text here. Click to load it

incorrectly represented omega still in UTF-8. Or possibly re-encoded the
binary output from PHP to match your LOCALE. Hard to know that without
digging deeply into your environment. But, I can point you at a handy
Copy/paste the output above into the tool, leaving it set on "Embedded",
and you'll get exactly the output you're hoping for. Nothing's jumbled
at all, it's just not representing right.

Quoted text here. Click to load it

Correctly represented omega. Something, probably the LOCALE settings in
your csh(? I think that's what uses percent for a user prompt...)
session in cooperation with echo, decoded exactly the same bytes above
and correctly represented it.

Quoted text here. Click to load it

Note that at this point, the mysql server know that it's getting stuff
from PHP stuff in UTF-8. PHP does not know it's GETTING stuff in UTF-8,
nor does MySQL know explicitly what charset to send things back in. And,
yes, you sure can send your commands in UTF-8 and get back stuff in (for
example) big5. This is why using a passed-through SET NAMES is
specifically deprecated in the PHP manual. See
http://php.net/manual/en/function.mysql-set-charset.php for the right
way to do it.

Quoted text here. Click to load it

Now, there may be some other stuff to fuss with to get it to interact
with stdio in your shell to show the output how it's coming out of
MySQL, but it's fixable. Which you'll get to discard entirely when PHP6
rolls around, but...

Quoted text here. Click to load it

For this one, the LOCALE is kicking in and working with the my.cnf to
set up appropriate connection encoding. Er, I think, anyway. We're back
to that whole "I don't know your whole environment" thing.

Re: PDO breaking UTF8 characters


try with a:

ini_set('default_charset', 'UTF-8');

before doing anything else.


On 30/11/2011 02:19, TnT wrote:
Quoted text here. Click to load it

Site Timeline