| Re: PDO breaking UTF8 characters [message #176176 is a reply to message #176175] |
Wed, 30 November 2011 02:23   |
Peter H. Coffin
Messages: 245
Registered: September 2010
Karma:
|
Senior Member |
|
|
On 11/29/2011 7:19 PM, TnT wrote:
> 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:
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.
> % php utf8.php
> This is a test Ω of the emergency broadcast system
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
tool:
http://software.hixie.ch/utilities/cgi/unicode-decoder/utf8-decoder
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.
> % utf8.sh
> entry
> This is a test Ω of the emergency broadcast system
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.
>
> -----------------------------------------------------------
> % cat utf8.php
> <?php
> // connect
> $db=new PDO("mysql:host=localhost;dbname=testdb", "root", "password");
>
> // force UTF8
> $db->query('SET NAMES utf8');
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.
> // select single row
> $result=$db->query("select entry from employee where id=3640");
> $row=$result->fetch(PDO::FETCH_ASSOC);
>
> // print row
> print $row['entry'] . "\n";
> ?>
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...
> -----------------------------------------------------------
> % cat utf8.sh
> echo "select entry from employee where id=3640" | \
> mysql -h localhost -u root --pass='password' testdb
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.
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]