Mysql database in UTF8, PHP shows latin1 (iso-8859-1)

I've converted a latin1 database I have to utf8. The process has been:

# mysqldump -u root -p --default-character-set=latin1 -c --insert-ignore
--skip-set-charset mydb > mydb.sql

# iconv -f ISO-8859-1 -t UTF-8 mydb.sql > mydb_utf8.sql

[then i replaced all CHARSET=latin1 to CHARSET=utf8 in the file, and
checked in the editor that non default characters as accents were viewed
correctly using UTF8 charset]

mysql> CREATE DATABASE mydb_utf8 CHARACTER SET utf8 COLLATE utf8_general_ci;

# mysql -u root -p --default-character-set=utf8 mydb_utf8 < mydb_utf8.sql


I made a simple script in PHP to dump data from tables which had non
standard characters. But the resulting page is viewed correctly in
iso-8859-1 encoding and not in UTF-8.

At this moment i'm clueless were the problem lies.


..alex

Re: Mysql database in UTF8, PHP shows latin1 (iso-8859-1)

Am Fri, 04 Apr 2008 10:11:16 +0200 schrieb alex:
> I made a simple script in PHP to dump data from tables which had non
> standard characters. But the resulting page is viewed correctly in
> iso-8859-1 encoding and not in UTF-8.

http://dev.mysql.com/doc/refman/5.0/en/charset-connection.ht ml

I guess using "set names utf8" should help.

Norbert

Re: Mysql database in UTF8, PHP shows latin1 (iso-8859-1)

En/na Norbert Tretkowski ha escrit:
> Am Fri, 04 Apr 2008 10:11:16 +0200 schrieb alex:
>> I made a simple script in PHP to dump data from tables which had non
>> standard characters. But the resulting page is viewed correctly in
>> iso-8859-1 encoding and not in UTF-8.
>
> http://dev.mysql.com/doc/refman/5.0/en/charset-connection.ht ml
>
> I guess using "set names utf8" should help.
>

Yes, it helps, but why should i change my app?

I mean, if the database is set to utf8, the tables are utf8 too (and
just to check, i've also set under [client], [mysqld] and [server] the
value default_character_set=utf8), shouldn't php return me the string as
utf8?

Isn't this a bit bugged?

Re: Mysql database in UTF8, PHP shows latin1 (iso-8859-1)

..oO(alex)

>En/na Norbert Tretkowski ha escrit:
>> Am Fri, 04 Apr 2008 10:11:16 +0200 schrieb alex:
>>> I made a simple script in PHP to dump data from tables which had non
>>> standard characters. But the resulting page is viewed correctly in
>>> iso-8859-1 encoding and not in UTF-8.
>>
>> http://dev.mysql.com/doc/refman/5.0/en/charset-connection.ht ml
>>
>> I guess using "set names utf8" should help.
>>
>
>Yes, it helps, but why should i change my app?
>
>I mean, if the database is set to utf8, the tables are utf8 too (and
>just to check, i've also set under [client], [mysqld] and [server] the
>value default_character_set=utf8), shouldn't php return me the string as
>utf8?
>
>Isn't this a bit bugged?

Just storing the data as UTF-8 is not enough. You also have to set the
connection between MySQL and your script to UTF-8 and PHP has to send a
correct HTTP header back to the browser.

Micha

Re: Mysql database in UTF8, PHP shows latin1 (iso-8859-1)

En/na Michael Fesser ha escrit:

>
> Just storing the data as UTF-8 is not enough. You also have to set the
> connection between MySQL and your script to UTF-8 and PHP has to send a
> correct HTTP header back to the browser.

Ok, i know that i must send correct headers, but still having this
variables:

+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

The output is ISO, and that is what freaks me out. :P

Re: Mysql database in UTF8, PHP shows latin1 (iso-8859-1)

En/na Jerry Stuckle ha escrit:

>
> Check Micha's comment again:
>
> "...and PHP has to send a correct HTTP header back to the browser."
>
> The dataset connection and charset have nothing to do with the charset
> sent in the page header. You can set the correct charset for the page
> in your Apache server or in PHP.


It's not a http header problem as i have in my script:



it seems a php problem as when i execute "show variables" in an mysql
console i get:

character_set_client=utf8

but from php i get:

character_set_client=latin1

when the default character set is utf8 (in my.cnf) and i have not
configured it for latin1 anywhere.

I've got 2 more apps one in java and the another one in .Net. None of
those had to be modified in order to retrieve the results. But PHP needs
the "set names utf8" in order to get the proper encoding.

Re: Mysql database in UTF8, PHP shows latin1 (iso-8859-1)

alex wrote:
> En/na Michael Fesser ha escrit:
>
>>
>> Just storing the data as UTF-8 is not enough. You also have to set the
>> connection between MySQL and your script to UTF-8 and PHP has to send a
>> correct HTTP header back to the browser.
>
> Ok, i know that i must send correct headers, but still having this
> variables:
>
> +--------------------------+----------------------------+
> | Variable_name | Value |
> +--------------------------+----------------------------+
> | character_set_client | utf8 |
> | character_set_connection | utf8 |
> | character_set_database | utf8 |
> | character_set_filesystem | binary |
> | character_set_results | utf8 |
> | character_set_server | utf8 |
> | character_set_system | utf8 |
> | character_sets_dir | /usr/share/mysql/charsets/ |
> +--------------------------+----------------------------+
>
> The output is ISO, and that is what freaks me out. :P
>

Check Micha's comment again:

"...and PHP has to send a correct HTTP header back to the browser."

The dataset connection and charset have nothing to do with the charset
sent in the page header. You can set the correct charset for the page
in your Apache server or in PHP.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Mysql database in UTF8, PHP shows latin1 (iso-8859-1)

> it seems a php problem as when i execute "show variables" in an mysql
> console i get:
>
> character_set_client=utf8
>
> but from php i get:
>
> character_set_client=latin1

That is a problem I also had. the [mysql] section of my.cnf is for the
command-line client only. A [client] section may or may not be used for
PHP connections. I had to send a "SET NAMES utf8" upon connection to
really convince MySQL to use utf8 for the connection.

This really sucks, off course. It is plain stupid to have to set the
encoding used encoded in the encoding it has to set. It is like sending
a key INSIDE a safe which requires that key to open it.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Re: Mysql database in UTF8, PHP shows latin1 (iso-8859-1)

..oO(Willem Bogaerts)

>> it seems a php problem as when i execute "show variables" in an mysql
>> console i get:
>>
>> character_set_client=utf8
>>
>> but from php i get:
>>
>> character_set_client=latin1
>
>That is a problem I also had. the [mysql] section of my.cnf is for the
>command-line client only. A [client] section may or may not be used for
>PHP connections. I had to send a "SET NAMES utf8" upon connection to
>really convince MySQL to use utf8 for the connection.

That's how it's supposed to be and was already mentioned multiple times.
How else should MySQL know what connection encoding the PHP client and
your scripts prefer?

>This really sucks, off course. It is plain stupid to have to set the
>encoding used encoded in the encoding it has to set. It is like sending
>a key INSIDE a safe which requires that key to open it.

You connect to the DB using the default encoding and then set it to
whatever you want it to be. There's nothing stupid about that.

Micha

Re: Mysql database in UTF8, PHP shows latin1 (iso-8859-1)

alex wrote:
> En/na Jerry Stuckle ha escrit:
>
>>
>> Check Micha's comment again:
>>
>> "...and PHP has to send a correct HTTP header back to the browser."
>>
>> The dataset connection and charset have nothing to do with the charset
>> sent in the page header. You can set the correct charset for the page
>> in your Apache server or in PHP.
>
>
> It's not a http header problem as i have in my script:
>
>
>
> it seems a php problem as when i execute "show variables" in an mysql
> console i get:
>
> character_set_client=utf8
>
> but from php i get:
>
> character_set_client=latin1
>
> when the default character set is utf8 (in my.cnf) and i have not
> configured it for latin1 anywhere.
>
> I've got 2 more apps one in java and the another one in .Net. None of
> those had to be modified in order to retrieve the results. But PHP needs
> the "set names utf8" in order to get the proper encoding.

Have a look here for a good explanation:
http://www.adviesenzo.nl/examples/php_mysql_charset_fix/

Re: Mysql database in UTF8, PHP shows latin1 (iso-8859-1)

>>> character_set_client=latin1
>> That is a problem I also had. the [mysql] section of my.cnf is for the
>> command-line client only. A [client] section may or may not be used for
>> PHP connections. I had to send a "SET NAMES utf8" upon connection to
>> really convince MySQL to use utf8 for the connection.
>
> That's how it's supposed to be and was already mentioned multiple times.
> How else should MySQL know what connection encoding the PHP client and
> your scripts prefer?

You should either be able to configure it reliably OR have a default
encoding. The way it is now, this is not the case. If you send an SQL
file through the command-line client, the default encoding can be
DIFFERENT from the situation where you send the exact same file through
PHP. That is the problem here. The whole command "SET NAMES utf8" is not
even legible if the encoding is set to ucs-2. That is why that encoding
is not possible in MySQL.

Off course, the default encoding should be passed in the connection. Can
you tell me what the default encoding is when connecting? According to
the manual, any "language character" can be used to use for a database
name. So mysql_select_db() will not use latin-1, as you would not be
able to refer to your database. Or is it? I could not find it anywhere.

>
>> This really sucks, off course. It is plain stupid to have to set the
>> encoding used encoded in the encoding it has to set. It is like sending
>> a key INSIDE a safe which requires that key to open it.
>
> You connect to the DB using the default encoding and then set it to
> whatever you want it to be. There's nothing stupid about that.
>
> Micha

The problem with that, off course, is that this is highly vendor,
version AND client-specific. The command-line client behaves differently
than a PHP initiated connection if you do not send it. Now that was the
exact problem of the original poster. And older database versions do not
even understand it.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Re: Mysql database in UTF8, PHP shows latin1 (iso-8859-1)

On Tue, 08 Apr 2008 12:57:29 +0200, Willem Bogaerts wrote:
>>>> character_set_client=latin1
>>> That is a problem I also had. the [mysql] section of my.cnf is for the
>>> command-line client only. A [client] section may or may not be used for
>>> PHP connections. I had to send a "SET NAMES utf8" upon connection to
>>> really convince MySQL to use utf8 for the connection.
>>
>> That's how it's supposed to be and was already mentioned multiple times.
>> How else should MySQL know what connection encoding the PHP client and
>> your scripts prefer?
>
> You should either be able to configure it reliably OR have a default
> encoding. The way it is now, this is not the case. If you send an SQL
> file through the command-line client, the default encoding can be
> DIFFERENT from the situation where you send the exact same file through
> PHP. That is the problem here. The whole command "SET NAMES utf8" is not
> even legible if the encoding is set to ucs-2. That is why that
> encoding is not possible in MySQL.

Don't be silly. ucs2, utf16 and utf32 cannot be used for SET NAMES or SET
CHARACTER SET. It's in the manual, in the section about connection
charsets. You DID read that section, didn't you, before complaining that
connection charsets make no sense?

> Off course, the default encoding should be passed in the connection.
> Can you tell me what the default encoding is when connecting?
> According to the manual, any "language character" can be used to use
> for a database name. So mysql_select_db() will not use latin-1, as you
> would not be able to refer to your database. Or is it? I could not
> find it anywhere.

It's whatever you use on the --default-character-set=charset_name
option, or in the my.cnf file, or latin-1, in that order. This is a lot
less hard if you try reading and knowing the documentation that
accompanies your installation.

>>> This really sucks, off course. It is plain stupid to have to set
>>> the encoding used encoded in the encoding it has to set. It is like
>>> sending a key INSIDE a safe which requires that key to open it.
>>
>> You connect to the DB using the default encoding and then set it to
>> whatever you want it to be. There's nothing stupid about that.
>>
>> Micha
>
> The problem with that, off course, is that this is highly vendor,
> version AND client-specific. The command-line client behaves
> differently than a PHP initiated connection if you do not send it.

That's why you send it, whenever you are unsure of what it is currently
set to. This is as ridiculous as complaining about having to explicitly
open a connection to mysql in PHP as well, and feeling hurt that you
have to specify which database you want to use. "Why not have a sensible
default database and just use that?!?"

> Now that was the exact problem of the original poster. And older
> database versions do not even understand it.

How old a database do you HAVE? The first note on the connection charset
page in the online manual is from 2004.

--
The Web brings people together because no matter what kind of a twisted
sexual mutant you happen to be, you've got millions of pals out there.
Type in 'Find people that have sex with goats that are on fire' and the
computer will ask, 'Specify type of goat.' -- Rich Jeni