By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,650 Members | 1,785 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,650 IT Pros & Developers. It's quick & easy.

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

P: n/a
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]

mysqlCREATE 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
Apr 4 '08 #1
Share this Question
Share on Google+
39 Replies


P: n/a
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/...onnection.html

I guess using "set names utf8" should help.

Norbert
Apr 4 '08 #2

P: n/a
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/...onnection.html

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?
Apr 4 '08 #3

P: n/a
..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/...onnection.html

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
Apr 4 '08 #4

P: n/a
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
Apr 4 '08 #5

P: n/a
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.
js*******@attglobal.net
==================

Apr 4 '08 #6

P: n/a
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/
Apr 4 '08 #7

P: n/a
..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
Apr 4 '08 #8

P: n/a
alex schrieb:
>
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.
Hello,

well, first of all, I'm newbie with MYSQL & Co., I have read all answers
and followed the link reading them all carefully. I suppose I understand
the matter.

I have apparently the same problem as Alex has. But my connection is in
UTF-8 and I need ISO-8859-1.

The problem accrued as I've moved from my webspace to a vServer.

The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:

character set client utf8
(Globaler Wert) latin1
character set connection utf8
(Globaler Wert) latin1
character set database latin1
character set results utf8
(Globaler Wert) latin1
character set server latin1
character set system utf8
collation connection utf8_general_ci
(Globaler Wert) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
exactly the same, the I set "SET NAMES latin1" via mysql-shell.

BUT:

Making "SHOW VARIABLES" at mysql-shell gives:

character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_filesystem binary
character set results latin1
character set server latin1
character set system utf8
collation connection latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

asking for the values by PHP gives:

character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci

I need the character_set_client and character_set_connection in latin1.

The HTTP header sends to the browser ISO-8859-15. That should stay so,
because about 2.000 html pages are coded in this. I don't care for the
DB contect, then I have it local in some programm. My only wish is to
establish a working-encoding-connection between the PHP and DB.

Can You please tell what exactly I have to do? Please, understand I
can't reprogramm the application, I even don't want to, because it
worked before. I'm quit sure it's not correct setted up.

Thanks a lot in advance!!!

Regards

Marc
Jun 27 '08 #9

P: n/a
I'm sorry, I should mention, I use:

Debian Etch stable
Apache2
PHP 5
Jun 27 '08 #10

P: n/a
Marc wrote:
alex schrieb:
>>
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.

Hello,

well, first of all, I'm newbie with MYSQL & Co., I have read all answers
and followed the link reading them all carefully. I suppose I understand
the matter.

I have apparently the same problem as Alex has. But my connection is in
UTF-8 and I need ISO-8859-1.

The problem accrued as I've moved from my webspace to a vServer.

The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:

character set client utf8
(Globaler Wert) latin1
character set connection utf8
(Globaler Wert) latin1
character set database latin1
character set results utf8
(Globaler Wert) latin1
character set server latin1
character set system utf8
collation connection utf8_general_ci
(Globaler Wert) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
exactly the same, the I set "SET NAMES latin1" via mysql-shell.

BUT:

Making "SHOW VARIABLES" at mysql-shell gives:

character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_filesystem binary
character set results latin1
character set server latin1
character set system utf8
collation connection latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

asking for the values by PHP gives:

character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci

I need the character_set_client and character_set_connection in latin1.

The HTTP header sends to the browser ISO-8859-15. That should stay so,
because about 2.000 html pages are coded in this. I don't care for the
DB contect, then I have it local in some programm. My only wish is to
establish a working-encoding-connection between the PHP and DB.

Can You please tell what exactly I have to do? Please, understand I
can't reprogramm the application, I even don't want to, because it
worked before. I'm quit sure it's not correct setted up.

Thanks a lot in advance!!!

Regards

Marc
Marc,

Look at the differences in your settings. Then look at the fact these
are MySQL settings, not PHP. And look at the questions you're asking -
about the character set in the connection and client.

These are all MySQL items - which should be a clue that you should be
asking in comp.databases.mysql, not here.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Jun 27 '08 #11

P: n/a
Marc wrote:
alex schrieb:
>>
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.

Hello,

well, first of all, I'm newbie with MYSQL & Co., I have read all answers
and followed the link reading them all carefully. I suppose I understand
the matter.

I have apparently the same problem as Alex has. But my connection is in
UTF-8 and I need ISO-8859-1.

The problem accrued as I've moved from my webspace to a vServer.

The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:

character set client utf8
(Globaler Wert) latin1
character set connection utf8
(Globaler Wert) latin1
character set database latin1
character set results utf8
(Globaler Wert) latin1
character set server latin1
character set system utf8
collation connection utf8_general_ci
(Globaler Wert) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
exactly the same, the I set "SET NAMES latin1" via mysql-shell.

BUT:

Making "SHOW VARIABLES" at mysql-shell gives:

character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_filesystem binary
character set results latin1
character set server latin1
character set system utf8
collation connection latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

asking for the values by PHP gives:

character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci

I need the character_set_client and character_set_connection in latin1.

The HTTP header sends to the browser ISO-8859-15. That should stay so,
because about 2.000 html pages are coded in this. I don't care for the
DB contect, then I have it local in some programm. My only wish is to
establish a working-encoding-connection between the PHP and DB.

Can You please tell what exactly I have to do? Please, understand I
can't reprogramm the application, I even don't want to, because it
worked before. I'm quit sure it's not correct setted up.

Thanks a lot in advance!!!

Regards

Marc
Oops, I didn't notice you crossposted this to comp.databases.mysql.

You indicated you had "non-standard" characters in the database. The
first question is - what charset is the data in the database in? And
exactly what are these "non-standard" characters?

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

Jun 27 '08 #12

P: n/a
Greetings, Marc.
In reply to Your message dated Sunday, June 8, 2008, 23:06:35,
>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.
Hello,
well, first of all, I'm newbie with MYSQL & Co., I have read all answers
and followed the link reading them all carefully. I suppose I understand
the matter.
I have apparently the same problem as Alex has. But my connection is in
UTF-8 and I need ISO-8859-1.
The problem accrued as I've moved from my webspace to a vServer.
The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:
character set client utf8
(Globaler Wert) latin1
character set connection utf8
(Globaler Wert) latin1
character set database latin1
character set results utf8
(Globaler Wert) latin1
character set server latin1
character set system utf8
collation connection utf8_general_ci
(Globaler Wert) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci
The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
exactly the same, the I set "SET NAMES latin1" via mysql-shell.
BUT:
Making "SHOW VARIABLES" at mysql-shell gives:
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_filesystem binary
character set results latin1
character set server latin1
character set system utf8
collation connection latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci
asking for the values by PHP gives:
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
I need the character_set_client and character_set_connection in latin1.
The HTTP header sends to the browser ISO-8859-15. That should stay so,
because about 2.000 html pages are coded in this. I don't care for the
DB contect, then I have it local in some programm. My only wish is to
establish a working-encoding-connection between the PHP and DB.
Can You please tell what exactly I have to do? Please, understand I
can't reprogramm the application, I even don't want to, because it
worked before. I'm quit sure it's not correct setted up.
You have the answer, but apparently lost it in your explanation. I'll quote:
The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
exactly the same, the I set "SET NAMES latin1" via mysql-shell.
You must tell to server, what encoding you want, every time you connecting to
database.
Just add execution of 'SET NAMES latin1 COLLATE whatever_collation_you_need'
statement right after the moment you have connected to database in your script.

P.S.
I've read somewhere that it is possible to configure MySQL user to have
specific encoding/collation without recompiling whole server/playing with
variables every time user connecting to the server.
But I've lost that manual or sumply misread it.
All I can remember for now, it is something like
CREATE USER 'user' WITH CHARACTER SET charset;

Can anyone confirm it or tell more about it?
--
Sincerely Yours, AnrDaemon <an*******@freemail.ru>

Jun 27 '08 #13

P: n/a
Jerry Stuckle schrieb:
Marc wrote:
>alex schrieb:
>>>
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.

Hello,

well, first of all, I'm newbie with MYSQL & Co., I have read all
answers and followed the link reading them all carefully. I suppose I
understand the matter.

I have apparently the same problem as Alex has. But my connection is
in UTF-8 and I need ISO-8859-1.

The problem accrued as I've moved from my webspace to a vServer.

The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:

character set client utf8
(Globaler Wert) latin1
character set connection utf8
(Globaler Wert) latin1
character set database latin1
character set results utf8
(Globaler Wert) latin1
character set server latin1
character set system utf8
collation connection utf8_general_ci
(Globaler Wert) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
exactly the same, the I set "SET NAMES latin1" via mysql-shell.

BUT:

Making "SHOW VARIABLES" at mysql-shell gives:

character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_filesystem binary
character set results latin1
character set server latin1
character set system utf8
collation connection latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

asking for the values by PHP gives:

character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci

I need the character_set_client and character_set_connection in latin1.

The HTTP header sends to the browser ISO-8859-15. That should stay so,
because about 2.000 html pages are coded in this. I don't care for the
DB contect, then I have it local in some programm. My only wish is to
establish a working-encoding-connection between the PHP and DB.

Can You please tell what exactly I have to do? Please, understand I
can't reprogramm the application, I even don't want to, because it
worked before. I'm quit sure it's not correct setted up.

Thanks a lot in advance!!!

Regards

Marc

Oops, I didn't notice you crossposted this to comp.databases.mysql.

You indicated you had "non-standard" characters in the database. The
first question is - what charset is the data in the database in? And
exactly what are these "non-standard" characters?
Hello Jerry,

sorry for crossposting. I think it's not only MYSQL problem, but also
PHP problem.

I have German umlauts in there. I guess the old DB used latin1 and the
new one uses UTF-8 for internal corresponding.

As I have found it, it acctually doesn't matter how the DB corresponds
internal, then it's able to encode by request sent by client. And that's
the point, I guess PHP doesn't say which encoding it would like to use.
Or maybe it's enough to tell DB which encoding it should normally use
talking to anything from outside.

Right?

So, I'm sorry for crossposting but I guess it's a mysql-php-problem.

Thanks a lot for Your responce.
Regards,

Marc
Jun 27 '08 #14

P: n/a
Hello AnrDaemon,

AnrDaemon schrieb:
Greetings, Marc.
In reply to Your message dated Sunday, June 8, 2008, 23:06:35,
>>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.
>Hello,
>well, first of all, I'm newbie with MYSQL & Co., I have read all answers
and followed the link reading them all carefully. I suppose I understand
the matter.
>I have apparently the same problem as Alex has. But my connection is in
UTF-8 and I need ISO-8859-1.
>The problem accrued as I've moved from my webspace to a vServer.
>The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:
>character set client utf8
(Globaler Wert) latin1
character set connection utf8
(Globaler Wert) latin1
character set database latin1
character set results utf8
(Globaler Wert) latin1
character set server latin1
character set system utf8
collation connection utf8_general_ci
(Globaler Wert) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci
>The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
exactly the same, the I set "SET NAMES latin1" via mysql-shell.
>BUT:
>Making "SHOW VARIABLES" at mysql-shell gives:
>character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_filesystem binary
character set results latin1
character set server latin1
character set system utf8
collation connection latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci
>asking for the values by PHP gives:
>character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
>I need the character_set_client and character_set_connection in latin1.
>The HTTP header sends to the browser ISO-8859-15. That should stay so,
because about 2.000 html pages are coded in this. I don't care for the
DB contect, then I have it local in some programm. My only wish is to
establish a working-encoding-connection between the PHP and DB.
>Can You please tell what exactly I have to do? Please, understand I
can't reprogramm the application, I even don't want to, because it
worked before. I'm quit sure it's not correct setted up.

You have the answer, but apparently lost it in your explanation. I'll quote:
>The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
exactly the same, the I set "SET NAMES latin1" via mysql-shell.

You must tell to server, what encoding you want, every time you connecting to
database.
Just add execution of 'SET NAMES latin1 COLLATE whatever_collation_you_need'
statement right after the moment you have connected to database in your script.
thanks a lot for this advice. But it's not possible to change the whole
project for this. That's why I would like to set it generally up. On the
other side, I don't need any other encoding.
>
P.S.
I've read somewhere that it is possible to configure MySQL user to have
specific encoding/collation without recompiling whole server/playing with
variables every time user connecting to the server.
But I've lost that manual or sumply misread it.
All I can remember for now, it is something like
CREATE USER 'user' WITH CHARACTER SET charset;

Can anyone confirm it or tell more about it?

I have read somewhere it should be necessary to recompile the whole
server... well, I have no idea about it yet.

I'm gonna look around for any advice for configuring MySQL user to have
specific encoding.

But still, isn't it possible to make general settings for this??

Thanks a lot for Your hints.

Regards,

Marc
Jun 27 '08 #15

P: n/a
Greetings, Marc.
In reply to Your message dated Monday, June 9, 2008, 19:31:01,
>You have the answer, but apparently lost it in your explanation. I'll quote:
>>The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
exactly the same, the I set "SET NAMES latin1" via mysql-shell.

You must tell to server, what encoding you want, every time you connecting to
database.
Just add execution of 'SET NAMES latin1 COLLATE whatever_collation_you_need'
statement right after the moment you have connected to database in your script.
thanks a lot for this advice. But it's not possible to change the whole
project for this. That's why I would like to set it generally up. On the
other side, I don't need any other encoding.
You lost the point.
It is not "changing the whole project", it is the action that project must do
once in only one script - one that establishing connection to database.
It is, literally, one line of code to add to whole your project, and it will
serve you to the end of times.
--
Sincerely Yours, AnrDaemon <an*******@freemail.ru>

Jun 27 '08 #16

P: n/a
Marc wrote:
Jerry Stuckle schrieb:
>Marc wrote:
>>alex schrieb:

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.

Hello,

well, first of all, I'm newbie with MYSQL & Co., I have read all
answers and followed the link reading them all carefully. I suppose I
understand the matter.

I have apparently the same problem as Alex has. But my connection is
in UTF-8 and I need ISO-8859-1.

The problem accrued as I've moved from my webspace to a vServer.

The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:

character set client utf8
(Globaler Wert) latin1
character set connection utf8
(Globaler Wert) latin1
character set database latin1
character set results utf8
(Globaler Wert) latin1
character set server latin1
character set system utf8
collation connection utf8_general_ci
(Globaler Wert) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
exactly the same, the I set "SET NAMES latin1" via mysql-shell.

BUT:

Making "SHOW VARIABLES" at mysql-shell gives:

character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_filesystem binary
character set results latin1
character set server latin1
character set system utf8
collation connection latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

asking for the values by PHP gives:

character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci

I need the character_set_client and character_set_connection in latin1.

The HTTP header sends to the browser ISO-8859-15. That should stay
so, because about 2.000 html pages are coded in this. I don't care
for the DB contect, then I have it local in some programm. My only
wish is to establish a working-encoding-connection between the PHP
and DB.

Can You please tell what exactly I have to do? Please, understand I
can't reprogramm the application, I even don't want to, because it
worked before. I'm quit sure it's not correct setted up.

Thanks a lot in advance!!!

Regards

Marc

Oops, I didn't notice you crossposted this to comp.databases.mysql.

You indicated you had "non-standard" characters in the database. The
first question is - what charset is the data in the database in? And
exactly what are these "non-standard" characters?

Hello Jerry,

sorry for crossposting. I think it's not only MYSQL problem, but also
PHP problem.

I have German umlauts in there. I guess the old DB used latin1 and the
new one uses UTF-8 for internal corresponding.

As I have found it, it acctually doesn't matter how the DB corresponds
internal, then it's able to encode by request sent by client. And that's
the point, I guess PHP doesn't say which encoding it would like to use.
Or maybe it's enough to tell DB which encoding it should normally use
talking to anything from outside.

Right?

So, I'm sorry for crossposting but I guess it's a mysql-php-problem.

Thanks a lot for Your responce.
Regards,

Marc
Marc,

No, it's my fault for not noticing the cross-posting in the first place.

You probably now have a mismatch between your connection and your table.
Besides the charset used by the table, you have a charset used by the
connection. I suspect you have a mismatch here. After connecting, call:

mysql_query("SET NAMES 'latin1'");

This should also set your connection to latin1.

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

Jun 27 '08 #17

P: n/a
Jerry Stuckle schrieb:
Marc wrote:
>Jerry Stuckle schrieb:
>>Marc wrote:
alex schrieb:
>
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.

Hello,

well, first of all, I'm newbie with MYSQL & Co., I have read all
answers and followed the link reading them all carefully. I suppose
I understand the matter.

I have apparently the same problem as Alex has. But my connection is
in UTF-8 and I need ISO-8859-1.

The problem accrued as I've moved from my webspace to a vServer.

The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:

character set client utf8
(Globaler Wert) latin1
character set connection utf8
(Globaler Wert) latin1
character set database latin1
character set results utf8
(Globaler Wert) latin1
character set server latin1
character set system utf8
collation connection utf8_general_ci
(Globaler Wert) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
exactly the same, the I set "SET NAMES latin1" via mysql-shell.

BUT:

Making "SHOW VARIABLES" at mysql-shell gives:

character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_filesystem binary
character set results latin1
character set server latin1
character set system utf8
collation connection latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

asking for the values by PHP gives:

character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci

I need the character_set_client and character_set_connection in latin1.

The HTTP header sends to the browser ISO-8859-15. That should stay
so, because about 2.000 html pages are coded in this. I don't care
for the DB contect, then I have it local in some programm. My only
wish is to establish a working-encoding-connection between the PHP
and DB.

Can You please tell what exactly I have to do? Please, understand I
can't reprogramm the application, I even don't want to, because it
worked before. I'm quit sure it's not correct setted up.

Thanks a lot in advance!!!

Regards

Marc
Oops, I didn't notice you crossposted this to comp.databases.mysql.

You indicated you had "non-standard" characters in the database. The
first question is - what charset is the data in the database in? And
exactly what are these "non-standard" characters?

Hello Jerry,

sorry for crossposting. I think it's not only MYSQL problem, but also
PHP problem.

I have German umlauts in there. I guess the old DB used latin1 and the
new one uses UTF-8 for internal corresponding.

As I have found it, it acctually doesn't matter how the DB corresponds
internal, then it's able to encode by request sent by client. And
that's the point, I guess PHP doesn't say which encoding it would like
to use. Or maybe it's enough to tell DB which encoding it should
normally use talking to anything from outside.

Right?

So, I'm sorry for crossposting but I guess it's a mysql-php-problem.

Thanks a lot for Your responce.
Regards,

Marc

Marc,

No, it's my fault for not noticing the cross-posting in the first place.

You probably now have a mismatch between your connection and your table.
Besides the charset used by the table, you have a charset used by the
connection. I suspect you have a mismatch here. After connecting, call:

mysql_query("SET NAMES 'latin1'");

This should also set your connection to latin1.
Jerry,

that's what I guess the problem is.

I've tried to call the command at the mysql-shell... and got error 1064
(42000). Should it be called via the php script perhaps? That's not that
comfortable way for me...

Regards,

Marc
Jun 27 '08 #18

P: n/a
AnrDaemon schrieb:
Greetings, Marc.
In reply to Your message dated Monday, June 9, 2008, 19:31:01,
>>You have the answer, but apparently lost it in your explanation. I'll quote:

The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
exactly the same, the I set "SET NAMES latin1" via mysql-shell.
You must tell to server, what encoding you want, every time you connecting to
database.
Just add execution of 'SET NAMES latin1 COLLATE whatever_collation_you_need'
statement right after the moment you have connected to database in your script.
>thanks a lot for this advice. But it's not possible to change the whole
project for this. That's why I would like to set it generally up. On the
other side, I don't need any other encoding.

You lost the point.
It is not "changing the whole project", it is the action that project must do
once in only one script - one that establishing connection to database.
It is, literally, one line of code to add to whole your project, and it will
serve you to the end of times.

AnrDaemon,

that seems that easy, but it cannot be the cleanest way. I don't feel so
comfortable about it. It has worked already. I just would like to set
everything right up.

I'm sending the data from some external DB by one php script and I'm
trying to add this simple line to it... It's gonna take some time, I to
new on this stuff. I'm gonna report.

Thanks a lot.

Regards,

Marc
Jun 27 '08 #19

P: n/a
Marc wrote:
Jerry Stuckle schrieb:
>Marc wrote:
>>Jerry Stuckle schrieb:
Marc wrote:
alex schrieb:
>>
>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.
>
Hello,
>
well, first of all, I'm newbie with MYSQL & Co., I have read all
answers and followed the link reading them all carefully. I suppose
I understand the matter.
>
I have apparently the same problem as Alex has. But my connection
is in UTF-8 and I need ISO-8859-1.
>
The problem accrued as I've moved from my webspace to a vServer.
>
The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:
>
character set client utf8
(Globaler Wert) latin1
character set connection utf8
(Globaler Wert) latin1
character set database latin1
character set results utf8
(Globaler Wert) latin1
character set server latin1
character set system utf8
collation connection utf8_general_ci
(Globaler Wert) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci
>
The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
exactly the same, the I set "SET NAMES latin1" via mysql-shell.
>
BUT:
>
Making "SHOW VARIABLES" at mysql-shell gives:
>
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_filesystem binary
character set results latin1
character set server latin1
character set system utf8
collation connection latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci
>
asking for the values by PHP gives:
>
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
>
I need the character_set_client and character_set_connection in
latin1.
>
The HTTP header sends to the browser ISO-8859-15. That should stay
so, because about 2.000 html pages are coded in this. I don't care
for the DB contect, then I have it local in some programm. My only
wish is to establish a working-encoding-connection between the PHP
and DB.
>
Can You please tell what exactly I have to do? Please, understand I
can't reprogramm the application, I even don't want to, because it
worked before. I'm quit sure it's not correct setted up.
>
Thanks a lot in advance!!!
>
Regards
>
Marc
>

Oops, I didn't notice you crossposted this to comp.databases.mysql.

You indicated you had "non-standard" characters in the database.
The first question is - what charset is the data in the database in?
And exactly what are these "non-standard" characters?
Hello Jerry,

sorry for crossposting. I think it's not only MYSQL problem, but also
PHP problem.

I have German umlauts in there. I guess the old DB used latin1 and
the new one uses UTF-8 for internal corresponding.

As I have found it, it acctually doesn't matter how the DB
corresponds internal, then it's able to encode by request sent by
client. And that's the point, I guess PHP doesn't say which encoding
it would like to use. Or maybe it's enough to tell DB which encoding
it should normally use talking to anything from outside.

Right?

So, I'm sorry for crossposting but I guess it's a mysql-php-problem.

Thanks a lot for Your responce.
Regards,

Marc

Marc,

No, it's my fault for not noticing the cross-posting in the first place.

You probably now have a mismatch between your connection and your
table. Besides the charset used by the table, you have a charset
used by the connection. I suspect you have a mismatch here. After
connecting, call:

mysql_query("SET NAMES 'latin1'");

This should also set your connection to latin1.

Jerry,

that's what I guess the problem is.

I've tried to call the command at the mysql-shell... and got error 1064
(42000). Should it be called via the php script perhaps? That's not that
comfortable way for me...

Regards,

Marc
This statement is connection specific. You need to call it after
connecting, and every time after connecting. Calling it from the
command line only sets it for that connection; it won't set it for your
PHP script, since it uses a different connection.

But your error message by itself is meaningless. What is the statement
you're entering, and what is the entire error message you get? It works
fine for me.

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

Jun 27 '08 #20

P: n/a
Jerry Stuckle schrieb:
Marc wrote:
>Jerry Stuckle schrieb:
>>Marc wrote:
Jerry Stuckle schrieb:
Marc wrote:
>alex schrieb:
>>>
>>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.
>>
>Hello,
>>
>well, first of all, I'm newbie with MYSQL & Co., I have read all
>answers and followed the link reading them all carefully. I
>suppose I understand the matter.
>>
>I have apparently the same problem as Alex has. But my connection
>is in UTF-8 and I need ISO-8859-1.
>>
>The problem accrued as I've moved from my webspace to a vServer.
>>
>The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:
>>
>character set client utf8
>(Globaler Wert) latin1
>character set connection utf8
>(Globaler Wert) latin1
>character set database latin1
>character set results utf8
>(Globaler Wert) latin1
>character set server latin1
>character set system utf8
>collation connection utf8_general_ci
>(Globaler Wert) latin1_swedish_ci
>collation database latin1_swedish_ci
>collation server latin1_swedish_ci
>>
>The new configuration shown by phpMyAdmin on my new MySQL 5.0.32
>is exactly the same, the I set "SET NAMES latin1" via mysql-shell.
>>
>BUT:
>>
>Making "SHOW VARIABLES" at mysql-shell gives:
>>
>character_set_client latin1
>character_set_connection latin1
>character_set_database latin1
>character_set_filesystem binary
>character set results latin1
>character set server latin1
>character set system utf8
>collation connection latin1_swedish_ci
>collation database latin1_swedish_ci
>collation server latin1_swedish_ci
>>
>asking for the values by PHP gives:
>>
>character_set_client utf8
>character_set_connection utf8
>character_set_database latin1
>character_set_filesystem binary
>character_set_results utf8
>character_set_server latin1
>character_set_system utf8
>collation_connection utf8_general_ci
>collation_database latin1_swedish_ci
>collation_server latin1_swedish_ci
>>
>I need the character_set_client and character_set_connection in
>latin1.
>>
>The HTTP header sends to the browser ISO-8859-15. That should stay
>so, because about 2.000 html pages are coded in this. I don't care
>for the DB contect, then I have it local in some programm. My only
>wish is to establish a working-encoding-connection between the PHP
>and DB.
>>
>Can You please tell what exactly I have to do? Please, understand
>I can't reprogramm the application, I even don't want to, because
>it worked before. I'm quit sure it's not correct setted up.
>>
>Thanks a lot in advance!!!
>>
>Regards
>>
>Marc
>>
>
Oops, I didn't notice you crossposted this to comp.databases.mysql.
>
You indicated you had "non-standard" characters in the database.
The first question is - what charset is the data in the database
in? And exactly what are these "non-standard" characters?
>

Hello Jerry,

sorry for crossposting. I think it's not only MYSQL problem, but
also PHP problem.

I have German umlauts in there. I guess the old DB used latin1 and
the new one uses UTF-8 for internal corresponding.

As I have found it, it acctually doesn't matter how the DB
corresponds internal, then it's able to encode by request sent by
client. And that's the point, I guess PHP doesn't say which encoding
it would like to use. Or maybe it's enough to tell DB which encoding
it should normally use talking to anything from outside.

Right?

So, I'm sorry for crossposting but I guess it's a mysql-php-problem.

Thanks a lot for Your responce.
Regards,

Marc
Marc,

No, it's my fault for not noticing the cross-posting in the first place.

You probably now have a mismatch between your connection and your
table. Besides the charset used by the table, you have a charset
used by the connection. I suspect you have a mismatch here. After
connecting, call:

mysql_query("SET NAMES 'latin1'");

This should also set your connection to latin1.

Jerry,

that's what I guess the problem is.

I've tried to call the command at the mysql-shell... and got error
1064 (42000). Should it be called via the php script perhaps? That's
not that comfortable way for me...

Regards,

Marc

This statement is connection specific. You need to call it after
connecting, and every time after connecting. Calling it from the
command line only sets it for that connection; it won't set it for your
PHP script, since it uses a different connection.

But your error message by itself is meaningless. What is the statement
you're entering, and what is the entire error message you get? It works
fine for me.

I have connected with my server via putty, have logged in. Then logged
in into mysql:

# mysql --password=mypassword --user=myuser [enter]

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 246
Server version: 5.0.32-Debian_7etch5-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysqlmysql_query("SET NAMES 'latin1'"); [enter]

ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die korrekte Syntax
im Handbuch nachschlagen bei 'mysql_query("SET NAMES 'latin1'")' in Zeile 1
mysql>

How can I change the connection permamently for php? Is it possible?
Without changing the scripts...
Jun 27 '08 #21

P: n/a
Marc wrote:
Jerry Stuckle schrieb:
>Marc wrote:
>>Jerry Stuckle schrieb:
Marc wrote:
Jerry Stuckle schrieb:
>Marc wrote:
>>alex schrieb:
>>>>
>>>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.
>>>
>>Hello,
>>>
>>well, first of all, I'm newbie with MYSQL & Co., I have read all
>>answers and followed the link reading them all carefully. I
>>suppose I understand the matter.
>>>
>>I have apparently the same problem as Alex has. But my connection
>>is in UTF-8 and I need ISO-8859-1.
>>>
>>The problem accrued as I've moved from my webspace to a vServer.
>>>
>>The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:
>>>
>>character set client utf8
>>(Globaler Wert) latin1
>>character set connection utf8
>>(Globaler Wert) latin1
>>character set database latin1
>>character set results utf8
>>(Globaler Wert) latin1
>>character set server latin1
>>character set system utf8
>>collation connection utf8_general_ci
>>(Globaler Wert) latin1_swedish_ci
>>collation database latin1_swedish_ci
>>collation server latin1_swedish_ci
>>>
>>The new configuration shown by phpMyAdmin on my new MySQL 5.0.32
>>is exactly the same, the I set "SET NAMES latin1" via mysql-shell.
>>>
>>BUT:
>>>
>>Making "SHOW VARIABLES" at mysql-shell gives:
>>>
>>character_set_client latin1
>>character_set_connection latin1
>>character_set_database latin1
>>character_set_filesystem binary
>>character set results latin1
>>character set server latin1
>>character set system utf8
>>collation connection latin1_swedish_ci
>>collation database latin1_swedish_ci
>>collation server latin1_swedish_ci
>>>
>>asking for the values by PHP gives:
>>>
>>character_set_client utf8
>>character_set_connection utf8
>>character_set_database latin1
>>character_set_filesystem binary
>>character_set_results utf8
>>character_set_server latin1
>>character_set_system utf8
>>collation_connection utf8_general_ci
>>collation_database latin1_swedish_ci
>>collation_server latin1_swedish_ci
>>>
>>I need the character_set_client and character_set_connection in
>>latin1.
>>>
>>The HTTP header sends to the browser ISO-8859-15. That should
>>stay so, because about 2.000 html pages are coded in this. I
>>don't care for the DB contect, then I have it local in some
>>programm. My only wish is to establish a
>>working-encoding-connection between the PHP and DB.
>>>
>>Can You please tell what exactly I have to do? Please, understand
>>I can't reprogramm the application, I even don't want to, because
>>it worked before. I'm quit sure it's not correct setted up.
>>>
>>Thanks a lot in advance!!!
>>>
>>Regards
>>>
>>Marc
>>>
>>
>Oops, I didn't notice you crossposted this to comp.databases.mysql.
>>
>You indicated you had "non-standard" characters in the database.
>The first question is - what charset is the data in the database
>in? And exactly what are these "non-standard" characters?
>>
>
Hello Jerry,
>
sorry for crossposting. I think it's not only MYSQL problem, but
also PHP problem.
>
I have German umlauts in there. I guess the old DB used latin1 and
the new one uses UTF-8 for internal corresponding.
>
As I have found it, it acctually doesn't matter how the DB
corresponds internal, then it's able to encode by request sent by
client. And that's the point, I guess PHP doesn't say which
encoding it would like to use. Or maybe it's enough to tell DB
which encoding it should normally use talking to anything from
outside.
>
Right?
>
So, I'm sorry for crossposting but I guess it's a mysql-php-problem.
>
Thanks a lot for Your responce.
>
>
Regards,
>
Marc
>

Marc,

No, it's my fault for not noticing the cross-posting in the first
place.

You probably now have a mismatch between your connection and your
table. Besides the charset used by the table, you have a charset
used by the connection. I suspect you have a mismatch here. After
connecting, call:

mysql_query("SET NAMES 'latin1'");

This should also set your connection to latin1.
Jerry,

that's what I guess the problem is.

I've tried to call the command at the mysql-shell... and got error
1064 (42000). Should it be called via the php script perhaps? That's
not that comfortable way for me...

Regards,

Marc

This statement is connection specific. You need to call it after
connecting, and every time after connecting. Calling it from the
command line only sets it for that connection; it won't set it for
your PHP script, since it uses a different connection.

But your error message by itself is meaningless. What is the
statement you're entering, and what is the entire error message you
get? It works fine for me.


I have connected with my server via putty, have logged in. Then logged
in into mysql:

# mysql --password=mypassword --user=myuser [enter]

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 246
Server version: 5.0.32-Debian_7etch5-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysqlmysql_query("SET NAMES 'latin1'"); [enter]

ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die korrekte Syntax
im Handbuch nachschlagen bei 'mysql_query("SET NAMES 'latin1'")' in Zeile 1
mysql>

How can I change the connection permamently for php? Is it possible?
Without changing the scripts...
No, mysql_query() is a PHP function call, not a MySQL statement. What
you need is just

mysql>SET NAMES 'latin1';

The easiest way to change startup values is to go into MySQL
Administrator, go to Service Control and click on the Configure Service
tab. Then set the Localization section to the charset you wish.

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

Jun 27 '08 #22

P: n/a
Greetings, Marc.
In reply to Your message dated Monday, June 9, 2008, 21:59:24,
AnrDaemon schrieb:
>Greetings, Marc.
In reply to Your message dated Monday, June 9, 2008, 19:31:01,
>>>You have the answer, but apparently lost it in your explanation. I'll quote:

The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
exactly the same, the I set "SET NAMES latin1" via mysql-shell.
You must tell to server, what encoding you want, every time you connecting to
database.
Just add execution of 'SET NAMES latin1 COLLATE whatever_collation_you_need'
statement right after the moment you have connected to database in your script.
>>thanks a lot for this advice. But it's not possible to change the whole
project for this. That's why I would like to set it generally up. On the
other side, I don't need any other encoding.

You lost the point.
It is not "changing the whole project", it is the action that project must do
once in only one script - one that establishing connection to database.
It is, literally, one line of code to add to whole your project, and it will
serve you to the end of times.
that seems that easy, but it cannot be the cleanest way. I don't feel so
comfortable about it. It has worked already. I just would like to set
everything right up.
It has worked already just because server was configured to work that way.
It was a 4.0 server most likely, or server config have had an init_connect
option set. Your argument "it was worked already" pointless, because
environment have changed.

You have only two ways to solve your problem.
1. I think it is right way.
Adapt environment for your needs (by changing connection encoding every time
you connect to database, it is one line of code in one single file, or in few
files, if you are not using some sort of abstraction layer) - it will work as
long as you are using MySQL servers for your database. They may have any
settings on their own, but you'll have what you need in your script all the
time.

2. Hard and one that I not considering acceptable for myself.
Change your scripts to handle current database output encoding. It will strike
just about every line of your code, in simple words - it will require to
rewrite about half of your project.
I'm sending the data from some external DB by one php script and I'm
trying to add this simple line to it... It's gonna take some time, I to
new on this stuff. I'm gonna report.
It's simple as find-replace :)
Locate the point in your (key word - your) script, where you are using
mysql_connect().
Add
mysql_query('SET NAMES latin1');
Right after calling mysql_connect().

It should work.

If you are using some sort of abstraction layer, it may be a bit different,
but not harder.

--
Sincerely Yours, AnrDaemon <an*******@freemail.ru>

Jun 27 '08 #23

P: n/a
AnrDaemon schrieb:
Greetings, Marc.
In reply to Your message dated Monday, June 9, 2008, 21:59:24,
>AnrDaemon schrieb:
>>Greetings, Marc.
In reply to Your message dated Monday, June 9, 2008, 19:31:01,

You have the answer, but apparently lost it in your explanation. I'll quote:
>
>The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
>exactly the same, the I set "SET NAMES latin1" via mysql-shell.
You must tell to server, what encoding you want, every time you connecting to
database.
Just add execution of 'SET NAMES latin1 COLLATE whatever_collation_you_need'
statement right after the moment you have connected to database in your script.
thanks a lot for this advice. But it's not possible to change the whole
project for this. That's why I would like to set it generally up. On the
other side, I don't need any other encoding.
You lost the point.
It is not "changing the whole project", it is the action that project must do
once in only one script - one that establishing connection to database.
It is, literally, one line of code to add to whole your project, and it will
serve you to the end of times.
>that seems that easy, but it cannot be the cleanest way. I don't feel so
comfortable about it. It has worked already. I just would like to set
everything right up.

It has worked already just because server was configured to work that way.
It was a 4.0 server most likely, or server config have had an init_connect
option set. Your argument "it was worked already" pointless, because
environment have changed.

You have only two ways to solve your problem.
1. I think it is right way.
Adapt environment for your needs (by changing connection encoding every time
you connect to database, it is one line of code in one single file, or in few
files, if you are not using some sort of abstraction layer) - it will work as
long as you are using MySQL servers for your database. They may have any
settings on their own, but you'll have what you need in your script all the
time.

2. Hard and one that I not considering acceptable for myself.
Change your scripts to handle current database output encoding. It will strike
just about every line of your code, in simple words - it will require to
rewrite about half of your project.
>I'm sending the data from some external DB by one php script and I'm
trying to add this simple line to it... It's gonna take some time, I to
new on this stuff. I'm gonna report.

It's simple as find-replace :)
Locate the point in your (key word - your) script, where you are using
mysql_connect().
Add
mysql_query('SET NAMES latin1');
Right after calling mysql_connect().

It should work.

If you are using some sort of abstraction layer, it may be a bit different,
but not harder.

Adding mysql_query didn't work, I mean it didn't change anything. The
Umlauts are still wrong.

I'm really fed up. It makes me sick.

Thanks a lot for Your hints!
Jun 27 '08 #24

P: n/a
Jerry Stuckle schrieb:
Marc wrote:
>Jerry Stuckle schrieb:
>>Marc wrote:
Jerry Stuckle schrieb:
Marc wrote:
>Jerry Stuckle schrieb:
>>Marc wrote:
>>>alex schrieb:
>>>>>
>>>>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.
>>>>
>>>Hello,
>>>>
>>>well, first of all, I'm newbie with MYSQL & Co., I have read all
>>>answers and followed the link reading them all carefully. I
>>>suppose I understand the matter.
>>>>
>>>I have apparently the same problem as Alex has. But my
>>>connection is in UTF-8 and I need ISO-8859-1.
>>>>
>>>The problem accrued as I've moved from my webspace to a vServer.
>>>>
>>>The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:
>>>>
>>>character set client utf8
>>>(Globaler Wert) latin1
>>>character set connection utf8
>>>(Globaler Wert) latin1
>>>character set database latin1
>>>character set results utf8
>>>(Globaler Wert) latin1
>>>character set server latin1
>>>character set system utf8
>>>collation connection utf8_general_ci
>>>(Globaler Wert) latin1_swedish_ci
>>>collation database latin1_swedish_ci
>>>collation server latin1_swedish_ci
>>>>
>>>The new configuration shown by phpMyAdmin on my new MySQL 5.0.32
>>>is exactly the same, the I set "SET NAMES latin1" via mysql-shell.
>>>>
>>>BUT:
>>>>
>>>Making "SHOW VARIABLES" at mysql-shell gives:
>>>>
>>>character_set_client latin1
>>>character_set_connection latin1
>>>character_set_database latin1
>>>character_set_filesystem binary
>>>character set results latin1
>>>character set server latin1
>>>character set system utf8
>>>collation connection latin1_swedish_ci
>>>collation database latin1_swedish_ci
>>>collation server latin1_swedish_ci
>>>>
>>>asking for the values by PHP gives:
>>>>
>>>character_set_client utf8
>>>character_set_connection utf8
>>>character_set_database latin1
>>>character_set_filesystem binary
>>>character_set_results utf8
>>>character_set_server latin1
>>>character_set_system utf8
>>>collation_connection utf8_general_ci
>>>collation_database latin1_swedish_ci
>>>collation_server latin1_swedish_ci
>>>>
>>>I need the character_set_client and character_set_connection in
>>>latin1.
>>>>
>>>The HTTP header sends to the browser ISO-8859-15. That should
>>>stay so, because about 2.000 html pages are coded in this. I
>>>don't care for the DB contect, then I have it local in some
>>>programm. My only wish is to establish a
>>>working-encoding-connection between the PHP and DB.
>>>>
>>>Can You please tell what exactly I have to do? Please,
>>>understand I can't reprogramm the application, I even don't want
>>>to, because it worked before. I'm quit sure it's not correct
>>>setted up.
>>>>
>>>Thanks a lot in advance!!!
>>>>
>>>Regards
>>>>
>>>Marc
>>>>
>>>
>>Oops, I didn't notice you crossposted this to comp.databases.mysql.
>>>
>>You indicated you had "non-standard" characters in the database.
>>The first question is - what charset is the data in the database
>>in? And exactly what are these "non-standard" characters?
>>>
>>
>Hello Jerry,
>>
>sorry for crossposting. I think it's not only MYSQL problem, but
>also PHP problem.
>>
>I have German umlauts in there. I guess the old DB used latin1 and
>the new one uses UTF-8 for internal corresponding.
>>
>As I have found it, it acctually doesn't matter how the DB
>corresponds internal, then it's able to encode by request sent by
>client. And that's the point, I guess PHP doesn't say which
>encoding it would like to use. Or maybe it's enough to tell DB
>which encoding it should normally use talking to anything from
>outside.
>>
>Right?
>>
>So, I'm sorry for crossposting but I guess it's a mysql-php-problem.
>>
>Thanks a lot for Your responce.
>>
>>
>Regards,
>>
>Marc
>>
>
Marc,
>
No, it's my fault for not noticing the cross-posting in the first
place.
>
You probably now have a mismatch between your connection and your
table. Besides the charset used by the table, you have a charset
used by the connection. I suspect you have a mismatch here. After
connecting, call:
>
mysql_query("SET NAMES 'latin1'");
>
This should also set your connection to latin1.
>

Jerry,

that's what I guess the problem is.

I've tried to call the command at the mysql-shell... and got error
1064 (42000). Should it be called via the php script perhaps? That's
not that comfortable way for me...

Regards,

Marc
This statement is connection specific. You need to call it after
connecting, and every time after connecting. Calling it from the
command line only sets it for that connection; it won't set it for
your PHP script, since it uses a different connection.

But your error message by itself is meaningless. What is the
statement you're entering, and what is the entire error message you
get? It works fine for me.


I have connected with my server via putty, have logged in. Then logged
in into mysql:

# mysql --password=mypassword --user=myuser [enter]

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 246
Server version: 5.0.32-Debian_7etch5-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysqlmysql_query("SET NAMES 'latin1'"); [enter]

ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die korrekte
Syntax im Handbuch nachschlagen bei 'mysql_query("SET NAMES
'latin1'")' in Zeile 1
mysql>

How can I change the connection permamently for php? Is it possible?
Without changing the scripts...

No, mysql_query() is a PHP function call, not a MySQL statement. What
you need is just

mysql>SET NAMES 'latin1';

The easiest way to change startup values is to go into MySQL
Administrator, go to Service Control and click on the Configure Service
tab. Then set the Localization section to the charset you wish.
I have added this call to my php script. It didn't work!

I use MySQL on Linux! So there is no Service Control...

I don't understand it, the MySQL docs say the standard install comes
with latin1... But it seems to be not a rule...

Maybe I should recompile MySQL?!

Thanks a lot for Your hints!
Jun 27 '08 #25

P: n/a
Greetings, Marc.
In reply to Your message dated Tuesday, June 10, 2008, 01:45:36,
>>>>>You have the answer, but apparently lost it in your explanation. I'll quote:
>>
>>The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
>>exactly the same, the I set "SET NAMES latin1" via mysql-shell.
>You must tell to server, what encoding you want, every time you connecting to
>database.
>Just add execution of 'SET NAMES latin1 COLLATE whatever_collation_you_need'
>statement right after the moment you have connected to database in your script.
thanks a lot for this advice. But it's not possible to change the whole
project for this. That's why I would like to set it generally up. On the
other side, I don't need any other encoding.
You lost the point.
It is not "changing the whole project", it is the action that project must do
once in only one script - one that establishing connection to database.
It is, literally, one line of code to add to whole your project, and it will
serve you to the end of times.
>>that seems that easy, but it cannot be the cleanest way. I don't feel so
comfortable about it. It has worked already. I just would like to set
everything right up.

It has worked already just because server was configured to work that way.
It was a 4.0 server most likely, or server config have had an init_connect
option set. Your argument "it was worked already" pointless, because
environment have changed.

You have only two ways to solve your problem.
1. I think it is right way.
Adapt environment for your needs (by changing connection encoding every time
you connect to database, it is one line of code in one single file, or in few
files, if you are not using some sort of abstraction layer) - it will work as
long as you are using MySQL servers for your database. They may have any
settings on their own, but you'll have what you need in your script all the
time.

2. Hard and one that I not considering acceptable for myself.
Change your scripts to handle current database output encoding. It will strike
just about every line of your code, in simple words - it will require to
rewrite about half of your project.
>>I'm sending the data from some external DB by one php script and I'm
trying to add this simple line to it... It's gonna take some time, I to
new on this stuff. I'm gonna report.

It's simple as find-replace :)
Locate the point in your (key word - your) script, where you are using
mysql_connect().
Add
mysql_query('SET NAMES latin1');
Right after calling mysql_connect().

It should work.

If you are using some sort of abstraction layer, it may be a bit different,
but not harder.
Adding mysql_query didn't work, I mean it didn't change anything. The
Umlauts are still wrong.
You're stating your troubles without any confirmation.
Please show us few lines of your code where you have changed it, and explain,
how are you tested that it "didn't change anything".

I think you have checked it with your other script, which have not been
changed, thus not showing you what you want.
I'm really fed up. It makes me sick.
I have feeling of your anger, but that's not what makes man wise.
--
Sincerely Yours, AnrDaemon <an*******@freemail.ru>

Jun 27 '08 #26

P: n/a
Marc wrote:
Jerry Stuckle schrieb:
>Marc wrote:
>>Jerry Stuckle schrieb:
Marc wrote:
Jerry Stuckle schrieb:
>Marc wrote:
>>Jerry Stuckle schrieb:
>>>Marc wrote:
>>>>alex schrieb:
>>>>>>
>>>>>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.
>>>>>
>>>>Hello,
>>>>>
>>>>well, first of all, I'm newbie with MYSQL & Co., I have read
>>>>all answers and followed the link reading them all carefully. I
>>>>suppose I understand the matter.
>>>>>
>>>>I have apparently the same problem as Alex has. But my
>>>>connection is in UTF-8 and I need ISO-8859-1.
>>>>>
>>>>The problem accrued as I've moved from my webspace to a vServer.
>>>>>
>>>>The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:
>>>>>
>>>>character set client utf8
>>>>(Globaler Wert) latin1
>>>>character set connection utf8
>>>>(Globaler Wert) latin1
>>>>character set database latin1
>>>>character set results utf8
>>>>(Globaler Wert) latin1
>>>>character set server latin1
>>>>character set system utf8
>>>>collation connection utf8_general_ci
>>>>(Globaler Wert) latin1_swedish_ci
>>>>collation database latin1_swedish_ci
>>>>collation server latin1_swedish_ci
>>>>>
>>>>The new configuration shown by phpMyAdmin on my new MySQL
>>>>5.0.32 is exactly the same, the I set "SET NAMES latin1" via
>>>>mysql-shell.
>>>>>
>>>>BUT:
>>>>>
>>>>Making "SHOW VARIABLES" at mysql-shell gives:
>>>>>
>>>>character_set_client latin1
>>>>character_set_connection latin1
>>>>character_set_database latin1
>>>>character_set_filesystem binary
>>>>character set results latin1
>>>>character set server latin1
>>>>character set system utf8
>>>>collation connection latin1_swedish_ci
>>>>collation database latin1_swedish_ci
>>>>collation server latin1_swedish_ci
>>>>>
>>>>asking for the values by PHP gives:
>>>>>
>>>>character_set_client utf8
>>>>character_set_connection utf8
>>>>character_set_database latin1
>>>>character_set_filesystem binary
>>>>character_set_results utf8
>>>>character_set_server latin1
>>>>character_set_system utf8
>>>>collation_connection utf8_general_ci
>>>>collation_database latin1_swedish_ci
>>>>collation_server latin1_swedish_ci
>>>>>
>>>>I need the character_set_client and character_set_connection in
>>>>latin1.
>>>>>
>>>>The HTTP header sends to the browser ISO-8859-15. That should
>>>>stay so, because about 2.000 html pages are coded in this. I
>>>>don't care for the DB contect, then I have it local in some
>>>>programm. My only wish is to establish a
>>>>working-encoding-connection between the PHP and DB.
>>>>>
>>>>Can You please tell what exactly I have to do? Please,
>>>>understand I can't reprogramm the application, I even don't
>>>>want to, because it worked before. I'm quit sure it's not
>>>>correct setted up.
>>>>>
>>>>Thanks a lot in advance!!!
>>>>>
>>>>Regards
>>>>>
>>>>Marc
>>>>>
>>>>
>>>Oops, I didn't notice you crossposted this to comp.databases.mysql.
>>>>
>>>You indicated you had "non-standard" characters in the
>>>database. The first question is - what charset is the data in
>>>the database in? And exactly what are these "non-standard"
>>>characters?
>>>>
>>>
>>Hello Jerry,
>>>
>>sorry for crossposting. I think it's not only MYSQL problem, but
>>also PHP problem.
>>>
>>I have German umlauts in there. I guess the old DB used latin1
>>and the new one uses UTF-8 for internal corresponding.
>>>
>>As I have found it, it acctually doesn't matter how the DB
>>corresponds internal, then it's able to encode by request sent by
>>client. And that's the point, I guess PHP doesn't say which
>>encoding it would like to use. Or maybe it's enough to tell DB
>>which encoding it should normally use talking to anything from
>>outside.
>>>
>>Right?
>>>
>>So, I'm sorry for crossposting but I guess it's a mysql-php-problem.
>>>
>>Thanks a lot for Your responce.
>>>
>>>
>>Regards,
>>>
>>Marc
>>>
>>
>Marc,
>>
>No, it's my fault for not noticing the cross-posting in the first
>place.
>>
>You probably now have a mismatch between your connection and your
>table. Besides the charset used by the table, you have a charset
>used by the connection. I suspect you have a mismatch here. After
>connecting, call:
>>
>mysql_query("SET NAMES 'latin1'");
>>
>This should also set your connection to latin1.
>>
>
Jerry,
>
that's what I guess the problem is.
>
I've tried to call the command at the mysql-shell... and got error
1064 (42000). Should it be called via the php script perhaps?
That's not that comfortable way for me...
>
Regards,
>
Marc
>

This statement is connection specific. You need to call it after
connecting, and every time after connecting. Calling it from the
command line only sets it for that connection; it won't set it for
your PHP script, since it uses a different connection.

But your error message by itself is meaningless. What is the
statement you're entering, and what is the entire error message you
get? It works fine for me.

I have connected with my server via putty, have logged in. Then
logged in into mysql:

# mysql --password=mypassword --user=myuser [enter]

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 246
Server version: 5.0.32-Debian_7etch5-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysqlmysql_query("SET NAMES 'latin1'"); [enter]

ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die korrekte
Syntax im Handbuch nachschlagen bei 'mysql_query("SET NAMES
'latin1'")' in Zeile 1
mysql>

How can I change the connection permamently for php? Is it possible?
Without changing the scripts...

No, mysql_query() is a PHP function call, not a MySQL statement. What
you need is just

mysql>SET NAMES 'latin1';

The easiest way to change startup values is to go into MySQL
Administrator, go to Service Control and click on the Configure
Service tab. Then set the Localization section to the charset you wish.

I have added this call to my php script. It didn't work!

I use MySQL on Linux! So there is no Service Control...

I don't understand it, the MySQL docs say the standard install comes
with latin1... But it seems to be not a rule...

Maybe I should recompile MySQL?!

Thanks a lot for Your hints!
Marc,

Since you changed servers, the other possibility is the charset the web
page itself is sent as. What is the character encoding your web server
is sending for the page?

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

Jun 27 '08 #27

P: n/a
Jerry Stuckle schrieb:
Marc wrote:
>Jerry Stuckle schrieb:
>>Marc wrote:
Jerry Stuckle schrieb:
Marc wrote:
>Jerry Stuckle schrieb:
>>Marc wrote:
>>>Jerry Stuckle schrieb:
>>>>Marc wrote:
>>>>>alex schrieb:
>>>>>>>
>>>>>>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.
>>>>>>
>>>>>Hello,
>>>>>>
>>>>>well, first of all, I'm newbie with MYSQL & Co., I have read
>>>>>all answers and followed the link reading them all carefully.
>>>>>I suppose I understand the matter.
>>>>>>
>>>>>I have apparently the same problem as Alex has. But my
>>>>>connection is in UTF-8 and I need ISO-8859-1.
>>>>>>
>>>>>The problem accrued as I've moved from my webspace to a vServer.
>>>>>>
>>>>>The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:
>>>>>>
>>>>>character set client utf8
>>>>>(Globaler Wert) latin1
>>>>>character set connection utf8
>>>>>(Globaler Wert) latin1
>>>>>character set database latin1
>>>>>character set results utf8
>>>>>(Globaler Wert) latin1
>>>>>character set server latin1
>>>>>character set system utf8
>>>>>collation connection utf8_general_ci
>>>>>(Globaler Wert) latin1_swedish_ci
>>>>>collation database latin1_swedish_ci
>>>>>collation server latin1_swedish_ci
>>>>>>
>>>>>The new configuration shown by phpMyAdmin on my new MySQL
>>>>>5.0.32 is exactly the same, the I set "SET NAMES latin1" via
>>>>>mysql-shell.
>>>>>>
>>>>>BUT:
>>>>>>
>>>>>Making "SHOW VARIABLES" at mysql-shell gives:
>>>>>>
>>>>>character_set_client latin1
>>>>>character_set_connection latin1
>>>>>character_set_database latin1
>>>>>character_set_filesystem binary
>>>>>character set results latin1
>>>>>character set server latin1
>>>>>character set system utf8
>>>>>collation connection latin1_swedish_ci
>>>>>collation database latin1_swedish_ci
>>>>>collation server latin1_swedish_ci
>>>>>>
>>>>>asking for the values by PHP gives:
>>>>>>
>>>>>character_set_client utf8
>>>>>character_set_connection utf8
>>>>>character_set_database latin1
>>>>>character_set_filesystem binary
>>>>>character_set_results utf8
>>>>>character_set_server latin1
>>>>>character_set_system utf8
>>>>>collation_connection utf8_general_ci
>>>>>collation_database latin1_swedish_ci
>>>>>collation_server latin1_swedish_ci
>>>>>>
>>>>>I need the character_set_client and character_set_connection
>>>>>in latin1.
>>>>>>
>>>>>The HTTP header sends to the browser ISO-8859-15. That should
>>>>>stay so, because about 2.000 html pages are coded in this. I
>>>>>don't care for the DB contect, then I have it local in some
>>>>>programm. My only wish is to establish a
>>>>>working-encoding-connection between the PHP and DB.
>>>>>>
>>>>>Can You please tell what exactly I have to do? Please,
>>>>>understand I can't reprogramm the application, I even don't
>>>>>want to, because it worked before. I'm quit sure it's not
>>>>>correct setted up.
>>>>>>
>>>>>Thanks a lot in advance!!!
>>>>>>
>>>>>Regards
>>>>>>
>>>>>Marc
>>>>>>
>>>>>
>>>>Oops, I didn't notice you crossposted this to
>>>>comp.databases.mysql.
>>>>>
>>>>You indicated you had "non-standard" characters in the
>>>>database. The first question is - what charset is the data in
>>>>the database in? And exactly what are these "non-standard"
>>>>characters?
>>>>>
>>>>
>>>Hello Jerry,
>>>>
>>>sorry for crossposting. I think it's not only MYSQL problem, but
>>>also PHP problem.
>>>>
>>>I have German umlauts in there. I guess the old DB used latin1
>>>and the new one uses UTF-8 for internal corresponding.
>>>>
>>>As I have found it, it acctually doesn't matter how the DB
>>>corresponds internal, then it's able to encode by request sent
>>>by client. And that's the point, I guess PHP doesn't say which
>>>encoding it would like to use. Or maybe it's enough to tell DB
>>>which encoding it should normally use talking to anything from
>>>outside.
>>>>
>>>Right?
>>>>
>>>So, I'm sorry for crossposting but I guess it's a
>>>mysql-php-problem.
>>>>
>>>Thanks a lot for Your responce.
>>>>
>>>>
>>>Regards,
>>>>
>>>Marc
>>>>
>>>
>>Marc,
>>>
>>No, it's my fault for not noticing the cross-posting in the first
>>place.
>>>
>>You probably now have a mismatch between your connection and your
>>table. Besides the charset used by the table, you have a
>>charset used by the connection. I suspect you have a mismatch
>>here. After connecting, call:
>>>
>>mysql_query("SET NAMES 'latin1'");
>>>
>>This should also set your connection to latin1.
>>>
>>
>Jerry,
>>
>that's what I guess the problem is.
>>
>I've tried to call the command at the mysql-shell... and got error
>1064 (42000). Should it be called via the php script perhaps?
>That's not that comfortable way for me...
>>
>Regards,
>>
>Marc
>>
>
This statement is connection specific. You need to call it after
connecting, and every time after connecting. Calling it from the
command line only sets it for that connection; it won't set it for
your PHP script, since it uses a different connection.
>
But your error message by itself is meaningless. What is the
statement you're entering, and what is the entire error message you
get? It works fine for me.
>
I have connected with my server via putty, have logged in. Then
logged in into mysql:

# mysql --password=mypassword --user=myuser [enter]

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 246
Server version: 5.0.32-Debian_7etch5-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysqlmysql_query("SET NAMES 'latin1'"); [enter]

ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die korrekte
Syntax im Handbuch nachschlagen bei 'mysql_query("SET NAMES
'latin1'")' in Zeile 1
mysql>

How can I change the connection permamently for php? Is it possible?
Without changing the scripts...
No, mysql_query() is a PHP function call, not a MySQL statement.
What you need is just

mysql>SET NAMES 'latin1';

The easiest way to change startup values is to go into MySQL
Administrator, go to Service Control and click on the Configure
Service tab. Then set the Localization section to the charset you wish.

I have added this call to my php script. It didn't work!

I use MySQL on Linux! So there is no Service Control...

I don't understand it, the MySQL docs say the standard install comes
with latin1... But it seems to be not a rule...

Maybe I should recompile MySQL?!

Thanks a lot for Your hints!

Marc,

Since you changed servers, the other possibility is the charset the web
page itself is sent as. What is the character encoding your web server
is sending for the page?

At the beginning there was file 'charset' with AddDefaultCharset UTF-8
at my apache2 config dir. So I didn't get it I have this problem. But
then I mentioned umlaut problems at the admin backend, so I've begun to
look for the source of it. After deleting this file, I have find out,
that DB data won't be shown correctly.

The HTML pages have a HEADER with ISO-8859-15.

I have set a new 'charset' with ISO encoding - no result.
Jun 27 '08 #28

P: n/a
Marc wrote:
Jerry Stuckle schrieb:
>Marc wrote:
>>Jerry Stuckle schrieb:
Marc wrote:
Jerry Stuckle schrieb:
>Marc wrote:
>>Jerry Stuckle schrieb:
>>>Marc wrote:
>>>>Jerry Stuckle schrieb:
>>>>>Marc wrote:
>>>>>>alex schrieb:
>>>>>>>>
>>>>>>>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.
>>>>>>>
>>>>>>Hello,
>>>>>>>
>>>>>>well, first of all, I'm newbie with MYSQL & Co., I have read
>>>>>>all answers and followed the link reading them all carefully.
>>>>>>I suppose I understand the matter.
>>>>>>>
>>>>>>I have apparently the same problem as Alex has. But my
>>>>>>connection is in UTF-8 and I need ISO-8859-1.
>>>>>>>
>>>>>>The problem accrued as I've moved from my webspace to a vServer.
>>>>>>>
>>>>>>The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:
>>>>>>>
>>>>>>character set client utf8
>>>>>>(Globaler Wert) latin1
>>>>>>character set connection utf8
>>>>>>(Globaler Wert) latin1
>>>>>>character set database latin1
>>>>>>character set results utf8
>>>>>>(Globaler Wert) latin1
>>>>>>character set server latin1
>>>>>>character set system utf8
>>>>>>collation connection utf8_general_ci
>>>>>>(Globaler Wert) latin1_swedish_ci
>>>>>>collation database latin1_swedish_ci
>>>>>>collation server latin1_swedish_ci
>>>>>>>
>>>>>>The new configuration shown by phpMyAdmin on my new MySQL
>>>>>>5.0.32 is exactly the same, the I set "SET NAMES latin1" via
>>>>>>mysql-shell.
>>>>>>>
>>>>>>BUT:
>>>>>>>
>>>>>>Making "SHOW VARIABLES" at mysql-shell gives:
>>>>>>>
>>>>>>character_set_client latin1
>>>>>>character_set_connection latin1
>>>>>>character_set_database latin1
>>>>>>character_set_filesystem binary
>>>>>>character set results latin1
>>>>>>character set server latin1
>>>>>>character set system utf8
>>>>>>collation connection latin1_swedish_ci
>>>>>>collation database latin1_swedish_ci
>>>>>>collation server latin1_swedish_ci
>>>>>>>
>>>>>>asking for the values by PHP gives:
>>>>>>>
>>>>>>character_set_client utf8
>>>>>>character_set_connection utf8
>>>>>>character_set_database latin1
>>>>>>character_set_filesystem binary
>>>>>>character_set_results utf8
>>>>>>character_set_server latin1
>>>>>>character_set_system utf8
>>>>>>collation_connection utf8_general_ci
>>>>>>collation_database latin1_swedish_ci
>>>>>>collation_server latin1_swedish_ci
>>>>>>>
>>>>>>I need the character_set_client and character_set_connection
>>>>>>in latin1.
>>>>>>>
>>>>>>The HTTP header sends to the browser ISO-8859-15. That should
>>>>>>stay so, because about 2.000 html pages are coded in this. I
>>>>>>don't care for the DB contect, then I have it local in some
>>>>>>programm. My only wish is to establish a
>>>>>>working-encoding-connection between the PHP and DB.
>>>>>>>
>>>>>>Can You please tell what exactly I have to do? Please,
>>>>>>understand I can't reprogramm the application, I even don't
>>>>>>want to, because it worked before. I'm quit sure it's not
>>>>>>correct setted up.
>>>>>>>
>>>>>>Thanks a lot in advance!!!
>>>>>>>
>>>>>>Regards
>>>>>>>
>>>>>>Marc
>>>>>>>
>>>>>>
>>>>>Oops, I didn't notice you crossposted this to
>>>>>comp.databases.mysql.
>>>>>>
>>>>>You indicated you had "non-standard" characters in the
>>>>>database. The first question is - what charset is the data in
>>>>>the database in? And exactly what are these "non-standard"
>>>>>characters?
>>>>>>
>>>>>
>>>>Hello Jerry,
>>>>>
>>>>sorry for crossposting. I think it's not only MYSQL problem,
>>>>but also PHP problem.
>>>>>
>>>>I have German umlauts in there. I guess the old DB used latin1
>>>>and the new one uses UTF-8 for internal corresponding.
>>>>>
>>>>As I have found it, it acctually doesn't matter how the DB
>>>>corresponds internal, then it's able to encode by request sent
>>>>by client. And that's the point, I guess PHP doesn't say which
>>>>encoding it would like to use. Or maybe it's enough to tell DB
>>>>which encoding it should normally use talking to anything from
>>>>outside.
>>>>>
>>>>Right?
>>>>>
>>>>So, I'm sorry for crossposting but I guess it's a
>>>>mysql-php-problem.
>>>>>
>>>>Thanks a lot for Your responce.
>>>>>
>>>>>
>>>>Regards,
>>>>>
>>>>Marc
>>>>>
>>>>
>>>Marc,
>>>>
>>>No, it's my fault for not noticing the cross-posting in the
>>>first place.
>>>>
>>>You probably now have a mismatch between your connection and
>>>your table. Besides the charset used by the table, you have a
>>>charset used by the connection. I suspect you have a mismatch
>>>here. After connecting, call:
>>>>
>>>mysql_query("SET NAMES 'latin1'");
>>>>
>>>This should also set your connection to latin1.
>>>>
>>>
>>Jerry,
>>>
>>that's what I guess the problem is.
>>>
>>I've tried to call the command at the mysql-shell... and got
>>error 1064 (42000). Should it be called via the php script
>>perhaps? That's not that comfortable way for me...
>>>
>>Regards,
>>>
>>Marc
>>>
>>
>This statement is connection specific. You need to call it after
>connecting, and every time after connecting. Calling it from the
>command line only sets it for that connection; it won't set it for
>your PHP script, since it uses a different connection.
>>
>But your error message by itself is meaningless. What is the
>statement you're entering, and what is the entire error message
>you get? It works fine for me.
>>
>
>
I have connected with my server via putty, have logged in. Then
logged in into mysql:
>
# mysql --password=mypassword --user=myuser [enter]
>
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 246
Server version: 5.0.32-Debian_7etch5-log Debian etch distribution
>
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
mysqlmysql_query("SET NAMES 'latin1'"); [enter]
>
ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die korrekte
Syntax im Handbuch nachschlagen bei 'mysql_query("SET NAMES
'latin1'")' in Zeile 1
mysql>
>
How can I change the connection permamently for php? Is it
possible? Without changing the scripts...
>

No, mysql_query() is a PHP function call, not a MySQL statement.
What you need is just

mysql>SET NAMES 'latin1';

The easiest way to change startup values is to go into MySQL
Administrator, go to Service Control and click on the Configure
Service tab. Then set the Localization section to the charset you
wish.
I have added this call to my php script. It didn't work!

I use MySQL on Linux! So there is no Service Control...

I don't understand it, the MySQL docs say the standard install comes
with latin1... But it seems to be not a rule...

Maybe I should recompile MySQL?!

Thanks a lot for Your hints!

Marc,

Since you changed servers, the other possibility is the charset the
web page itself is sent as. What is the character encoding your web
server is sending for the page?


At the beginning there was file 'charset' with AddDefaultCharset UTF-8
at my apache2 config dir. So I didn't get it I have this problem. But
then I mentioned umlaut problems at the admin backend, so I've begun to
look for the source of it. After deleting this file, I have find out,
that DB data won't be shown correctly.

The HTML pages have a HEADER with ISO-8859-15.

I have set a new 'charset' with ISO encoding - no result.
OK, let's back up a bit. What was the charset you used on the previous
system? Was it actually latin1? Or could it have been something else?

Also, how did you move your data from one system to another?

Finally - to set the charset permanently, put it in your my.cfg file
under the [mysqld] section, i.e.

character_set_client = latin1

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

Jun 27 '08 #29

P: n/a
AnrDaemon schrieb:
Greetings, Marc.
In reply to Your message dated Tuesday, June 10, 2008, 01:45:36,
>>>>>>You have the answer, but apparently lost it in your explanation. I'll quote:
>>>
>>>The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
>>>exactly the same, the I set "SET NAMES latin1" via mysql-shell.
>>You must tell to server, what encoding you want, every time you connecting to
>>database.
>>Just add execution of 'SET NAMES latin1 COLLATE whatever_collation_you_need'
>>statement right after the moment you have connected to database in your script.
>thanks a lot for this advice. But it's not possible to change the whole
>project for this. That's why I would like to set it generally up. On the
>other side, I don't need any other encoding.
You lost the point.
It is not "changing the whole project", it is the action that project must do
once in only one script - one that establishing connection to database.
It is, literally, one line of code to add to whole your project, and it will
serve you to the end of times.
that seems that easy, but it cannot be the cleanest way. I don't feel so
comfortable about it. It has worked already. I just would like to set
everything right up.
It has worked already just because server was configured to work that way.
It was a 4.0 server most likely, or server config have had an init_connect
option set. Your argument "it was worked already" pointless, because
environment have changed.

You have only two ways to solve your problem.
1. I think it is right way.
Adapt environment for your needs (by changing connection encoding every time
you connect to database, it is one line of code in one single file, or in few
files, if you are not using some sort of abstraction layer) - it will work as
long as you are using MySQL servers for your database. They may have any
settings on their own, but you'll have what you need in your script all the
time.

2. Hard and one that I not considering acceptable for myself.
Change your scripts to handle current database output encoding. It will strike
just about every line of your code, in simple words - it will require to
rewrite about half of your project.

I'm sending the data from some external DB by one php script and I'm
trying to add this simple line to it... It's gonna take some time, I to
new on this stuff. I'm gonna report.
It's simple as find-replace :)
Locate the point in your (key word - your) script, where you are using
mysql_connect().
Add
mysql_query('SET NAMES latin1');
Right after calling mysql_connect().

It should work.

If you are using some sort of abstraction layer, it may be a bit different,
but not harder.

>Adding mysql_query didn't work, I mean it didn't change anything. The
Umlauts are still wrong.

You're stating your troubles without any confirmation.
Please show us few lines of your code where you have changed it, and explain,
how are you tested that it "didn't change anything".

I think you have checked it with your other script, which have not been
changed, thus not showing you what you want.
>I'm really fed up. It makes me sick.

I have feeling of your anger, but that's not what makes man wise.

I'm really sorry for giving my angery.

I use xt commerce. There are only 2 files calling mysql_connect(). So I
have done what You have told, I have three times added mysql_query...

1st: mysql_query('SET NAMES latin1');
2nd: mysql_query('SET NAMES latin1',@link);
3rd: mysql_query('SET NAMES latin1',@@link);

I'm attaching the original files.
So I have done it like this:

O R I G I N A L:
function xtc_db_connect($server = DB_SERVER, $username =
DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database =
DB_DATABASE, $link = 'db_link') {
global $$link;

if (USE_PCONNECT == 'true') {
$$link = mysql_pconnect($server, $username, $password);
} else {
$$link = mysql_connect($server, $username, $password);

}

if ($$link) mysql_select_db($database);

return $$link;
}
C H A N G E D:
function xtc_db_connect($server = DB_SERVER, $username =
DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database =
DB_DATABASE, $link = 'db_link') {
global $$link;

if (USE_PCONNECT == 'true') {
$$link = mysql_pconnect($server, $username, $password);
} else {
$$link = mysql_connect($server, $username, $password);
mysql_query('SET NAMES latin1');

}

if ($$link) mysql_select_db($database);

return $$link;
}
My can reach my site at: http://www.multitasking-berlin.de

Going on any product you can't see the umlauts, but switching the
browser to UTF-8 you can.

The point is, I don't need that much the data, I have it offline and can
upload it any time, but uploading works only via PHP and this way causes
the umlauts problem...




*********************
1st file in original:
*********************

<?php
// include(DIR_WS_CLASSES.'/adodb/adodb.inc.php');
function xtc_db_connect($server = DB_SERVER, $username =
DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database =
DB_DATABASE, $link = 'db_link') {
global $$link;

if (USE_PCONNECT == 'true') {
$$link = mysql_pconnect($server, $username, $password);
} else {
$$link = mysql_connect($server, $username, $password);

}

if ($$link) mysql_select_db($database);

return $$link;
}
?>


*********************
2nd file in original:
*********************

<?php
defined( '_VALID_XTC' ) or die( 'Direct Access to this location is not
allowed.' );
function xtc_db_connect($server = DB_SERVER, $username =
DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database =
DB_DATABASE, $link = 'db_link') {
global $$link;

if (USE_PCONNECT == 'true') {
$$link = mysql_pconnect($server, $username, $password);
} else {
$$link = mysql_connect($server, $username, $password);
}

if ($$link) mysql_select_db($database);

return $$link;
}

// db connection for Servicedatabase
function service_xtc_db_connect($server_service = SERVICE_DB_SERVER,
$username_service = SERVICE_DB_SERVER_USERNAME, $password_service =
SERVICE_DB_SERVER_PASSWORD, $database_service = SERVICE_DB_DATABASE,
$link_service = 'db_link_service') {
global $$link_service;

if (SERVICE_USE_PCONNECT == 'true') {
$$link_service = mysql_pconnect($server_service,
$username_service, $password_service);
} else {
$$link_service = mysql_connect($server_service,
$username_service, $password_service);
}

if ($$link_service) mysql_select_db($database_service);

return $$link_service;
}

function xtc_db_close($link = 'db_link') {
global $$link;

return mysql_close($$link);
}

// db connection for Servicedatabase
function service_xtc_db_close($link_service = 'db_link_service') {
global $$link_service;

return mysql_close($$link_service);
}
function xtc_db_error($query, $errno, $error) {
die('<font color="#000000"><b>' . $errno . ' - ' . $error .
'<br><br>' . $query . '<br><br><small><font color="#ff0000">[TEP
STOP]</font></small><br><br></b></font>');
}

function xtc_db_query($query, $link = 'db_link') {
global $$link, $logger;

if (STORE_DB_TRANSACTIONS == 'true') {
if (!is_object($logger)) $logger = new logger;
$logger->write($query, 'QUERY');
}

$result = mysql_query($query, $$link) or xtc_db_error($query,
mysql_errno(), mysql_error());

if (STORE_DB_TRANSACTIONS == 'true') {
if (mysql_error()) $logger->write(mysql_error(), 'ERROR');
}

return $result;
}

// db connection for Servicedatabase
function service_xtc_db_query($query, $link_service =
'db_link_service') {
global $$link_service, $logger_service;

if (STORE_DB_TRANSACTIONS == 'true') {
if (!is_object($logger_service)) $logger_service = new
logger_service;
$logger_service->write($query, 'QUERY');
}

$result = mysql_query($query, $$link_service) or
xtc_db_error($query, mysql_errno(), mysql_error());

if (STORE_DB_TRANSACTIONS == 'true') {
if (mysql_error()) $logger_service->write(mysql_error(), 'ERROR');
}

return $result;
}

function xtc_db_perform($table, $data, $action = 'insert',
$parameters = '', $link = 'db_link') {
reset($data);
if ($action == 'insert') {
$query = 'insert into ' . $table . ' (';
while (list($columns, ) = each($data)) {
$query .= $columns . ', ';
}
$query = substr($query, 0, -2) . ') values (';
reset($data);
while (list(, $value) = each($data)) {
switch ((string)$value) {
case 'now()':
$query .= 'now(), ';
break;
case 'null':
$query .= 'null, ';
break;
default:
$query .= '\'' . xtc_db_input($value) . '\', ';
break;
}
}
$query = substr($query, 0, -2) . ')';
} elseif ($action == 'update') {
$query = 'update ' . $table . ' set ';
while (list($columns, $value) = each($data)) {
switch ((string)$value) {
case 'now()':
$query .= $columns . ' = now(), ';
break;
case 'null':
$query .= $columns .= ' = null, ';
break;
default:
$query .= $columns . ' = \'' . xtc_db_input($value) . '\', ';
break;
}
}
$query = substr($query, 0, -2) . ' where ' . $parameters;
}

return xtc_db_query($query, $link);
}

function xtc_db_fetch_array($db_query) {
return mysql_fetch_array($db_query, MYSQL_ASSOC);
}

function xtc_db_result($result, $row, $field = '') {
return mysql_result($result, $row, $field);
}

function xtc_db_num_rows($db_query) {
return mysql_num_rows($db_query);
}

function xtc_db_data_seek($db_query, $row_number) {
return mysql_data_seek($db_query, $row_number);
}

function xtc_db_insert_id() {
return mysql_insert_id();
}

function xtc_db_free_result($db_query) {
return mysql_free_result($db_query);
}

function xtc_db_fetch_fields($db_query) {
return mysql_fetch_field($db_query);
}

function xtc_db_output($string) {
return htmlspecialchars($string);
}

function xtc_db_input($string) {
return addslashes($string);
}

function xtc_db_prepare_input($string) {
if (is_string($string)) {
return trim(stripslashes($string));
} elseif (is_array($string)) {
reset($string);
while (list($key, $value) = each($string)) {
$string[$key] = xtc_db_prepare_input($value);
}
return $string;
} else {
return $string;
}
}
?>
Jun 27 '08 #30

P: n/a
Jerry Stuckle schrieb:
Marc wrote:
>Jerry Stuckle schrieb:
>>Marc wrote:
Jerry Stuckle schrieb:
Marc wrote:
>Jerry Stuckle schrieb:
>>Marc wrote:
>>>Jerry Stuckle schrieb:
>>>>Marc wrote:
>>>>>Jerry Stuckle schrieb:
>>>>>>Marc wrote:
>>>>>>>alex schrieb:
>>>>>>>>>
>>>>>>>>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.
>>>>>>>>
>>>>>>>Hello,
>>>>>>>>
>>>>>>>well, first of all, I'm newbie with MYSQL & Co., I have read
>>>>>>>all answers and followed the link reading them all
>>>>>>>carefully. I suppose I understand the matter.
>>>>>>>>
>>>>>>>I have apparently the same problem as Alex has. But my
>>>>>>>connection is in UTF-8 and I need ISO-8859-1.
>>>>>>>>
>>>>>>>The problem accrued as I've moved from my webspace to a
>>>>>>>vServer.
>>>>>>>>
>>>>>>>The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:
>>>>>>>>
>>>>>>>character set client utf8
>>>>>>>(Globaler Wert) latin1
>>>>>>>character set connection utf8
>>>>>>>(Globaler Wert) latin1
>>>>>>>character set database latin1
>>>>>>>character set results utf8
>>>>>>>(Globaler Wert) latin1
>>>>>>>character set server latin1
>>>>>>>character set system utf8
>>>>>>>collation connection utf8_general_ci
>>>>>>>(Globaler Wert) latin1_swedish_ci
>>>>>>>collation database latin1_swedish_ci
>>>>>>>collation server latin1_swedish_ci
>>>>>>>>
>>>>>>>The new configuration shown by phpMyAdmin on my new MySQL
>>>>>>>5.0.32 is exactly the same, the I set "SET NAMES latin1" via
>>>>>>>mysql-shell.
>>>>>>>>
>>>>>>>BUT:
>>>>>>>>
>>>>>>>Making "SHOW VARIABLES" at mysql-shell gives:
>>>>>>>>
>>>>>>>character_set_client latin1
>>>>>>>character_set_connection latin1
>>>>>>>character_set_database latin1
>>>>>>>character_set_filesystem binary
>>>>>>>character set results latin1
>>>>>>>character set server latin1
>>>>>>>character set system utf8
>>>>>>>collation connection latin1_swedish_ci
>>>>>>>collation database latin1_swedish_ci
>>>>>>>collation server latin1_swedish_ci
>>>>>>>>
>>>>>>>asking for the values by PHP gives:
>>>>>>>>
>>>>>>>character_set_client utf8
>>>>>>>character_set_connection utf8
>>>>>>>character_set_database latin1
>>>>>>>character_set_filesystem binary
>>>>>>>character_set_results utf8
>>>>>>>character_set_server latin1
>>>>>>>character_set_system utf8
>>>>>>>collation_connection utf8_general_ci
>>>>>>>collation_database latin1_swedish_ci
>>>>>>>collation_server latin1_swedish_ci
>>>>>>>>
>>>>>>>I need the character_set_client and character_set_connection
>>>>>>>in latin1.
>>>>>>>>
>>>>>>>The HTTP header sends to the browser ISO-8859-15. That
>>>>>>>should stay so, because about 2.000 html pages are coded in
>>>>>>>this. I don't care for the DB contect, then I have it local
>>>>>>>in some programm. My only wish is to establish a
>>>>>>>working-encoding-connection between the PHP and DB.
>>>>>>>>
>>>>>>>Can You please tell what exactly I have to do? Please,
>>>>>>>understand I can't reprogramm the application, I even don't
>>>>>>>want to, because it worked before. I'm quit sure it's not
>>>>>>>correct setted up.
>>>>>>>>
>>>>>>>Thanks a lot in advance!!!
>>>>>>>>
>>>>>>>Regards
>>>>>>>>
>>>>>>>Marc
>>>>>>>>
>>>>>>>
>>>>>>Oops, I didn't notice you crossposted this to
>>>>>>comp.databases.mysql.
>>>>>>>
>>>>>>You indicated you had "non-standard" characters in the
>>>>>>database. The first question is - what charset is the data
>>>>>>in the database in? And exactly what are these "non-standard"
>>>>>>characters?
>>>>>>>
>>>>>>
>>>>>Hello Jerry,
>>>>>>
>>>>>sorry for crossposting. I think it's not only MYSQL problem,
>>>>>but also PHP problem.
>>>>>>
>>>>>I have German umlauts in there. I guess the old DB used latin1
>>>>>and the new one uses UTF-8 for internal corresponding.
>>>>>>
>>>>>As I have found it, it acctually doesn't matter how the DB
>>>>>corresponds internal, then it's able to encode by request sent
>>>>>by client. And that's the point, I guess PHP doesn't say which
>>>>>encoding it would like to use. Or maybe it's enough to tell DB
>>>>>which encoding it should normally use talking to anything from
>>>>>outside.
>>>>>>
>>>>>Right?
>>>>>>
>>>>>So, I'm sorry for crossposting but I guess it's a
>>>>>mysql-php-problem.
>>>>>>
>>>>>Thanks a lot for Your responce.
>>>>>>
>>>>>>
>>>>>Regards,
>>>>>>
>>>>>Marc
>>>>>>
>>>>>
>>>>Marc,
>>>>>
>>>>No, it's my fault for not noticing the cross-posting in the
>>>>first place.
>>>>>
>>>>You probably now have a mismatch between your connection and
>>>>your table. Besides the charset used by the table, you have a
>>>>charset used by the connection. I suspect you have a mismatch
>>>>here. After connecting, call:
>>>>>
>>>>mysql_query("SET NAMES 'latin1'");
>>>>>
>>>>This should also set your connection to latin1.
>>>>>
>>>>
>>>Jerry,
>>>>
>>>that's what I guess the problem is.
>>>>
>>>I've tried to call the command at the mysql-shell... and got
>>>error 1064 (42000). Should it be called via the php script
>>>perhaps? That's not that comfortable way for me...
>>>>
>>>Regards,
>>>>
>>>Marc
>>>>
>>>
>>This statement is connection specific. You need to call it after
>>connecting, and every time after connecting. Calling it from the
>>command line only sets it for that connection; it won't set it
>>for your PHP script, since it uses a different connection.
>>>
>>But your error message by itself is meaningless. What is the
>>statement you're entering, and what is the entire error message
>>you get? It works fine for me.
>>>
>>
>>
>I have connected with my server via putty, have logged in. Then
>logged in into mysql:
>>
># mysql --password=mypassword --user=myuser [enter]
>>
>Welcome to the MySQL monitor. Commands end with ; or \g.
>Your MySQL connection id is 246
>Server version: 5.0.32-Debian_7etch5-log Debian etch distribution
>>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>>
>mysqlmysql_query("SET NAMES 'latin1'"); [enter]
>>
>ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die korrekte
>Syntax im Handbuch nachschlagen bei 'mysql_query("SET NAMES
>'latin1'")' in Zeile 1
>mysql>
>>
>How can I change the connection permamently for php? Is it
>possible? Without changing the scripts...
>>
>
No, mysql_query() is a PHP function call, not a MySQL statement.
What you need is just
>
mysql>SET NAMES 'latin1';
>
The easiest way to change startup values is to go into MySQL
Administrator, go to Service Control and click on the Configure
Service tab. Then set the Localization section to the charset you
wish.
>

I have added this call to my php script. It didn't work!

I use MySQL on Linux! So there is no Service Control...

I don't understand it, the MySQL docs say the standard install comes
with latin1... But it seems to be not a rule...

Maybe I should recompile MySQL?!

Thanks a lot for Your hints!
Marc,

Since you changed servers, the other possibility is the charset the
web page itself is sent as. What is the character encoding your web
server is sending for the page?


At the beginning there was file 'charset' with AddDefaultCharset UTF-8
at my apache2 config dir. So I didn't get it I have this problem. But
then I mentioned umlaut problems at the admin backend, so I've begun
to look for the source of it. After deleting this file, I have find
out, that DB data won't be shown correctly.

The HTML pages have a HEADER with ISO-8859-15.

I have set a new 'charset' with ISO encoding - no result.

OK, let's back up a bit. What was the charset you used on the previous
system? Was it actually latin1? Or could it have been something else?

Also, how did you move your data from one system to another?

Finally - to set the charset permanently, put it in your my.cfg file
under the [mysqld] section, i.e.

character_set_client = latin1
Well, to say the truth I have no idea what the previous system was set
like. I can access it only via Plesk, and the MySQL via phpMyAdmin
2.8.2.4. The system variables received via phpMyAdmin I have posted
already. If there is anything else I can find out, please tell me what
and where.

I made an export by phpMyAdmin and then called following on the
mysql-shell at the new system:

/usr/bin/mysql --host=localhost --password=password --user=user DBname <
/home/user/DBname.sql

Viewing the export file via SuperEdi all umlauts are correct and it
seems all data is latin1-encoded, there is always this statement in
there: CREATE DATABASE `shop` DEFAULT CHARACTER SET latin1 COLLATE
latin1_swedish_ci; or DEFAULT CHARSET=latin1 in the tables.
Viewing the old DB all umlauts are OK, doing it at the new one, none is OK.
I have tried to set the charset permanently, but the server says
following after restart:
Jun 10 10:13:15 v213880518 mysqld_safe[20556]: started
Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 [Warning]
/usr/sbin/mysqld: ignoring option '--character-set-client$
Jun 10 10:13:15 v213880518 mysqld[20563]:
Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 InnoDB:
Started; log sequence number 0 2196292
Jun 10 10:13:16 v213880518 mysqld[20563]: 080610 10:13:16 [Note]
/usr/sbin/mysqld: bereit für Verbindungen.
Jun 10 10:13:16 v213880518 mysqld[20563]: Version:
'5.0.32-Debian_7etch5-log' Socket: '/var/run/mysqld/mysqld.sock' Port: 3$
So, I habe no idea why it doesn't work.
Jun 27 '08 #31

P: n/a
Marc wrote:
Jerry Stuckle schrieb:
>Marc wrote:
>>Jerry Stuckle schrieb:
Marc wrote:
Jerry Stuckle schrieb:
>Marc wrote:
>>Jerry Stuckle schrieb:
>>>Marc wrote:
>>>>Jerry Stuckle schrieb:
>>>>>Marc wrote:
>>>>>>Jerry Stuckle schrieb:
>>>>>>>Marc wrote:
>>>>>>>>alex schrieb:
>>>>>>>>>>
>>>>>>>>>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.
>>>>>>>>>
>>>>>>>>Hello,
>>>>>>>>>
>>>>>>>>well, first of all, I'm newbie with MYSQL & Co., I have
>>>>>>>>read all answers and followed the link reading them all
>>>>>>>>carefully. I suppose I understand the matter.
>>>>>>>>>
>>>>>>>>I have apparently the same problem as Alex has. But my
>>>>>>>>connection is in UTF-8 and I need ISO-8859-1.
>>>>>>>>>
>>>>>>>>The problem accrued as I've moved from my webspace to a
>>>>>>>>vServer.
>>>>>>>>>
>>>>>>>>The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:
>>>>>>>>>
>>>>>>>>character set client utf8
>>>>>>>>(Globaler Wert) latin1
>>>>>>>>character set connection utf8
>>>>>>>>(Globaler Wert) latin1
>>>>>>>>character set database latin1
>>>>>>>>character set results utf8
>>>>>>>>(Globaler Wert) latin1
>>>>>>>>character set server latin1
>>>>>>>>character set system utf8
>>>>>>>>collation connection utf8_general_ci
>>>>>>>>(Globaler Wert) latin1_swedish_ci
>>>>>>>>collation database latin1_swedish_ci
>>>>>>>>collation server latin1_swedish_ci
>>>>>>>>>
>>>>>>>>The new configuration shown by phpMyAdmin on my new MySQL
>>>>>>>>5.0.32 is exactly the same, the I set "SET NAMES latin1"
>>>>>>>>via mysql-shell.
>>>>>>>>>
>>>>>>>>BUT:
>>>>>>>>>
>>>>>>>>Making "SHOW VARIABLES" at mysql-shell gives:
>>>>>>>>>
>>>>>>>>character_set_client latin1
>>>>>>>>character_set_connection latin1
>>>>>>>>character_set_database latin1
>>>>>>>>character_set_filesystem binary
>>>>>>>>character set results latin1
>>>>>>>>character set server latin1
>>>>>>>>character set system utf8
>>>>>>>>collation connection latin1_swedish_ci
>>>>>>>>collation database latin1_swedish_ci
>>>>>>>>collation server latin1_swedish_ci
>>>>>>>>>
>>>>>>>>asking for the values by PHP gives:
>>>>>>>>>
>>>>>>>>character_set_client utf8
>>>>>>>>character_set_connection utf8
>>>>>>>>character_set_database latin1
>>>>>>>>character_set_filesystem binary
>>>>>>>>character_set_results utf8
>>>>>>>>character_set_server latin1
>>>>>>>>character_set_system utf8
>>>>>>>>collation_connection utf8_general_ci
>>>>>>>>collation_database latin1_swedish_ci
>>>>>>>>collation_server latin1_swedish_ci
>>>>>>>>>
>>>>>>>>I need the character_set_client and
>>>>>>>>character_set_connection in latin1.
>>>>>>>>>
>>>>>>>>The HTTP header sends to the browser ISO-8859-15. That
>>>>>>>>should stay so, because about 2.000 html pages are coded in
>>>>>>>>this. I don't care for the DB contect, then I have it local
>>>>>>>>in some programm. My only wish is to establish a
>>>>>>>>working-encoding-connection between the PHP and DB.
>>>>>>>>>
>>>>>>>>Can You please tell what exactly I have to do? Please,
>>>>>>>>understand I can't reprogramm the application, I even don't
>>>>>>>>want to, because it worked before. I'm quit sure it's not
>>>>>>>>correct setted up.
>>>>>>>>>
>>>>>>>>Thanks a lot in advance!!!
>>>>>>>>>
>>>>>>>>Regards
>>>>>>>>>
>>>>>>>>Marc
>>>>>>>>>
>>>>>>>>
>>>>>>>Oops, I didn't notice you crossposted this to
>>>>>>>comp.databases.mysql.
>>>>>>>>
>>>>>>>You indicated you had "non-standard" characters in the
>>>>>>>database. The first question is - what charset is the data
>>>>>>>in the database in? And exactly what are these
>>>>>>>"non-standard" characters?
>>>>>>>>
>>>>>>>
>>>>>>Hello Jerry,
>>>>>>>
>>>>>>sorry for crossposting. I think it's not only MYSQL problem,
>>>>>>but also PHP problem.
>>>>>>>
>>>>>>I have German umlauts in there. I guess the old DB used
>>>>>>latin1 and the new one uses UTF-8 for internal corresponding.
>>>>>>>
>>>>>>As I have found it, it acctually doesn't matter how the DB
>>>>>>corresponds internal, then it's able to encode by request
>>>>>>sent by client. And that's the point, I guess PHP doesn't say
>>>>>>which encoding it would like to use. Or maybe it's enough to
>>>>>>tell DB which encoding it should normally use talking to
>>>>>>anything from outside.
>>>>>>>
>>>>>>Right?
>>>>>>>
>>>>>>So, I'm sorry for crossposting but I guess it's a
>>>>>>mysql-php-problem.
>>>>>>>
>>>>>>Thanks a lot for Your responce.
>>>>>>>
>>>>>>>
>>>>>>Regards,
>>>>>>>
>>>>>>Marc
>>>>>>>
>>>>>>
>>>>>Marc,
>>>>>>
>>>>>No, it's my fault for not noticing the cross-posting in the
>>>>>first place.
>>>>>>
>>>>>You probably now have a mismatch between your connection and
>>>>>your table. Besides the charset used by the table, you have
>>>>>a charset used by the connection. I suspect you have a
>>>>>mismatch here. After connecting, call:
>>>>>>
>>>>>mysql_query("SET NAMES 'latin1'");
>>>>>>
>>>>>This should also set your connection to latin1.
>>>>>>
>>>>>
>>>>Jerry,
>>>>>
>>>>that's what I guess the problem is.
>>>>>
>>>>I've tried to call the command at the mysql-shell... and got
>>>>error 1064 (42000). Should it be called via the php script
>>>>perhaps? That's not that comfortable way for me...
>>>>>
>>>>Regards,
>>>>>
>>>>Marc
>>>>>
>>>>
>>>This statement is connection specific. You need to call it
>>>after connecting, and every time after connecting. Calling it
>>>from the command line only sets it for that connection; it won't
>>>set it for your PHP script, since it uses a different connection.
>>>>
>>>But your error message by itself is meaningless. What is the
>>>statement you're entering, and what is the entire error message
>>>you get? It works fine for me.
>>>>
>>>
>>>
>>I have connected with my server via putty, have logged in. Then
>>logged in into mysql:
>>>
>># mysql --password=mypassword --user=myuser [enter]
>>>
>>Welcome to the MySQL monitor. Commands end with ; or \g.
>>Your MySQL connection id is 246
>>Server version: 5.0.32-Debian_7etch5-log Debian etch distribution
>>>
>>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>>>
>>mysqlmysql_query("SET NAMES 'latin1'"); [enter]
>>>
>>ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die korrekte
>>Syntax im Handbuch nachschlagen bei 'mysql_query("SET NAMES
>>'latin1'")' in Zeile 1
>>mysql>
>>>
>>How can I change the connection permamently for php? Is it
>>possible? Without changing the scripts...
>>>
>>
>No, mysql_query() is a PHP function call, not a MySQL statement.
>What you need is just
>>
>mysql>SET NAMES 'latin1';
>>
>The easiest way to change startup values is to go into MySQL
>Administrator, go to Service Control and click on the Configure
>Service tab. Then set the Localization section to the charset you
>wish.
>>
>
I have added this call to my php script. It didn't work!
>
I use MySQL on Linux! So there is no Service Control...
>
I don't understand it, the MySQL docs say the standard install
comes with latin1... But it seems to be not a rule...
>
Maybe I should recompile MySQL?!
>
Thanks a lot for Your hints!
>

Marc,

Since you changed servers, the other possibility is the charset the
web page itself is sent as. What is the character encoding your web
server is sending for the page?

At the beginning there was file 'charset' with AddDefaultCharset
UTF-8 at my apache2 config dir. So I didn't get it I have this
problem. But then I mentioned umlaut problems at the admin backend,
so I've begun to look for the source of it. After deleting this file,
I have find out, that DB data won't be shown correctly.

The HTML pages have a HEADER with ISO-8859-15.

I have set a new 'charset' with ISO encoding - no result.

OK, let's back up a bit. What was the charset you used on the
previous system? Was it actually latin1? Or could it have been
something else?

Also, how did you move your data from one system to another?

Finally - to set the charset permanently, put it in your my.cfg file
under the [mysqld] section, i.e.

character_set_client = latin1

Well, to say the truth I have no idea what the previous system was set
like. I can access it only via Plesk, and the MySQL via phpMyAdmin
2.8.2.4. The system variables received via phpMyAdmin I have posted
already. If there is anything else I can find out, please tell me what
and where.

I made an export by phpMyAdmin and then called following on the
mysql-shell at the new system:

/usr/bin/mysql --host=localhost --password=password --user=user DBname <
/home/user/DBname.sql

Viewing the export file via SuperEdi all umlauts are correct and it
seems all data is latin1-encoded, there is always this statement in
there: CREATE DATABASE `shop` DEFAULT CHARACTER SET latin1 COLLATE
latin1_swedish_ci; or DEFAULT CHARSET=latin1 in the tables.
Viewing the old DB all umlauts are OK, doing it at the new one, none is OK.
Yes, but chances are you used utf-8 to do the import, since that was the
default for the connection.
>
I have tried to set the charset permanently, but the server says
following after restart:
Jun 10 10:13:15 v213880518 mysqld_safe[20556]: started
Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 [Warning]
/usr/sbin/mysqld: ignoring option '--character-set-client$
Jun 10 10:13:15 v213880518 mysqld[20563]:
Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 InnoDB:
Started; log sequence number 0 2196292
Jun 10 10:13:16 v213880518 mysqld[20563]: 080610 10:13:16 [Note]
/usr/sbin/mysqld: bereit für Verbindungen.
Jun 10 10:13:16 v213880518 mysqld[20563]: Version:
'5.0.32-Debian_7etch5-log' Socket: '/var/run/mysqld/mysqld.sock' Port: 3$
So, I habe no idea why it doesn't work.
How did you try to change the charset? And what *exactly* did you
enter? Again - a message without knowing exactly what you did is worthless.

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

Jun 27 '08 #32

P: n/a
Jerry Stuckle schrieb:
Marc wrote:
>Jerry Stuckle schrieb:
>>Marc wrote:
Jerry Stuckle schrieb:
Marc wrote:
>Jerry Stuckle schrieb:
>>Marc wrote:
>>>Jerry Stuckle schrieb:
>>>>Marc wrote:
>>>>>Jerry Stuckle schrieb:
>>>>>>Marc wrote:
>>>>>>>Jerry Stuckle schrieb:
>>>>>>>>Marc wrote:
>>>>>>>>>alex schrieb:
>>>>>>>>>>>
>>>>>>>>>>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.
>>>>>>>>>>
>>>>>>>>>Hello,
>>>>>>>>>>
>>>>>>>>>well, first of all, I'm newbie with MYSQL & Co., I have
>>>>>>>>>read all answers and followed the link reading them all
>>>>>>>>>carefully. I suppose I understand the matter.
>>>>>>>>>>
>>>>>>>>>I have apparently the same problem as Alex has. But my
>>>>>>>>>connection is in UTF-8 and I need ISO-8859-1.
>>>>>>>>>>
>>>>>>>>>The problem accrued as I've moved from my webspace to a
>>>>>>>>>vServer.
>>>>>>>>>>
>>>>>>>>>The old configuration shown by phpMyAdmin on MySQL 4.1.13 is:
>>>>>>>>>>
>>>>>>>>>character set client utf8
>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>character set connection utf8
>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>character set database latin1
>>>>>>>>>character set results utf8
>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>character set server latin1
>>>>>>>>>character set system utf8
>>>>>>>>>collation connection utf8_general_ci
>>>>>>>>>(Globaler Wert) latin1_swedish_ci
>>>>>>>>>collation database latin1_swedish_ci
>>>>>>>>>collation server latin1_swedish_ci
>>>>>>>>>>
>>>>>>>>>The new configuration shown by phpMyAdmin on my new MySQL
>>>>>>>>>5.0.32 is exactly the same, the I set "SET NAMES latin1"
>>>>>>>>>via mysql-shell.
>>>>>>>>>>
>>>>>>>>>BUT:
>>>>>>>>>>
>>>>>>>>>Making "SHOW VARIABLES" at mysql-shell gives:
>>>>>>>>>>
>>>>>>>>>character_set_client latin1
>>>>>>>>>character_set_connection latin1
>>>>>>>>>character_set_database latin1
>>>>>>>>>character_set_filesystem binary
>>>>>>>>>character set results latin1
>>>>>>>>>character set server latin1
>>>>>>>>>character set system utf8
>>>>>>>>>collation connection latin1_swedish_ci
>>>>>>>>>collation database latin1_swedish_ci
>>>>>>>>>collation server latin1_swedish_ci
>>>>>>>>>>
>>>>>>>>>asking for the values by PHP gives:
>>>>>>>>>>
>>>>>>>>>character_set_client utf8
>>>>>>>>>character_set_connection utf8
>>>>>>>>>character_set_database latin1
>>>>>>>>>character_set_filesystem binary
>>>>>>>>>character_set_results utf8
>>>>>>>>>character_set_server latin1
>>>>>>>>>character_set_system utf8
>>>>>>>>>collation_connection utf8_general_ci
>>>>>>>>>collation_database latin1_swedish_ci
>>>>>>>>>collation_server latin1_swedish_ci
>>>>>>>>>>
>>>>>>>>>I need the character_set_client and
>>>>>>>>>character_set_connection in latin1.
>>>>>>>>>>
>>>>>>>>>The HTTP header sends to the browser ISO-8859-15. That
>>>>>>>>>should stay so, because about 2.000 html pages are coded
>>>>>>>>>in this. I don't care for the DB contect, then I have it
>>>>>>>>>local in some programm. My only wish is to establish a
>>>>>>>>>working-encoding-connection between the PHP and DB.
>>>>>>>>>>
>>>>>>>>>Can You please tell what exactly I have to do? Please,
>>>>>>>>>understand I can't reprogramm the application, I even
>>>>>>>>>don't want to, because it worked before. I'm quit sure
>>>>>>>>>it's not correct setted up.
>>>>>>>>>>
>>>>>>>>>Thanks a lot in advance!!!
>>>>>>>>>>
>>>>>>>>>Regards
>>>>>>>>>>
>>>>>>>>>Marc
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>Oops, I didn't notice you crossposted this to
>>>>>>>>comp.databases.mysql.
>>>>>>>>>
>>>>>>>>You indicated you had "non-standard" characters in the
>>>>>>>>database. The first question is - what charset is the data
>>>>>>>>in the database in? And exactly what are these
>>>>>>>>"non-standard" characters?
>>>>>>>>>
>>>>>>>>
>>>>>>>Hello Jerry,
>>>>>>>>
>>>>>>>sorry for crossposting. I think it's not only MYSQL problem,
>>>>>>>but also PHP problem.
>>>>>>>>
>>>>>>>I have German umlauts in there. I guess the old DB used
>>>>>>>latin1 and the new one uses UTF-8 for internal corresponding.
>>>>>>>>
>>>>>>>As I have found it, it acctually doesn't matter how the DB
>>>>>>>corresponds internal, then it's able to encode by request
>>>>>>>sent by client. And that's the point, I guess PHP doesn't
>>>>>>>say which encoding it would like to use. Or maybe it's
>>>>>>>enough to tell DB which encoding it should normally use
>>>>>>>talking to anything from outside.
>>>>>>>>
>>>>>>>Right?
>>>>>>>>
>>>>>>>So, I'm sorry for crossposting but I guess it's a
>>>>>>>mysql-php-problem.
>>>>>>>>
>>>>>>>Thanks a lot for Your responce.
>>>>>>>>
>>>>>>>>
>>>>>>>Regards,
>>>>>>>>
>>>>>>>Marc
>>>>>>>>
>>>>>>>
>>>>>>Marc,
>>>>>>>
>>>>>>No, it's my fault for not noticing the cross-posting in the
>>>>>>first place.
>>>>>>>
>>>>>>You probably now have a mismatch between your connection and
>>>>>>your table. Besides the charset used by the table, you have
>>>>>>a charset used by the connection. I suspect you have a
>>>>>>mismatch here. After connecting, call:
>>>>>>>
>>>>>>mysql_query("SET NAMES 'latin1'");
>>>>>>>
>>>>>>This should also set your connection to latin1.
>>>>>>>
>>>>>>
>>>>>Jerry,
>>>>>>
>>>>>that's what I guess the problem is.
>>>>>>
>>>>>I've tried to call the command at the mysql-shell... and got
>>>>>error 1064 (42000). Should it be called via the php script
>>>>>perhaps? That's not that comfortable way for me...
>>>>>>
>>>>>Regards,
>>>>>>
>>>>>Marc
>>>>>>
>>>>>
>>>>This statement is connection specific. You need to call it
>>>>after connecting, and every time after connecting. Calling it
>>>>from the command line only sets it for that connection; it
>>>>won't set it for your PHP script, since it uses a different
>>>>connection.
>>>>>
>>>>But your error message by itself is meaningless. What is the
>>>>statement you're entering, and what is the entire error message
>>>>you get? It works fine for me.
>>>>>
>>>>
>>>>
>>>I have connected with my server via putty, have logged in. Then
>>>logged in into mysql:
>>>>
>>># mysql --password=mypassword --user=myuser [enter]
>>>>
>>>Welcome to the MySQL monitor. Commands end with ; or \g.
>>>Your MySQL connection id is 246
>>>Server version: 5.0.32-Debian_7etch5-log Debian etch distribution
>>>>
>>>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>>>>
>>>mysqlmysql_query("SET NAMES 'latin1'"); [enter]
>>>>
>>>ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die korrekte
>>>Syntax im Handbuch nachschlagen bei 'mysql_query("SET NAMES
>>>'latin1'")' in Zeile 1
>>>mysql>
>>>>
>>>How can I change the connection permamently for php? Is it
>>>possible? Without changing the scripts...
>>>>
>>>
>>No, mysql_query() is a PHP function call, not a MySQL statement.
>>What you need is just
>>>
>>mysql>SET NAMES 'latin1';
>>>
>>The easiest way to change startup values is to go into MySQL
>>Administrator, go to Service Control and click on the Configure
>>Service tab. Then set the Localization section to the charset
>>you wish.
>>>
>>
>I have added this call to my php script. It didn't work!
>>
>I use MySQL on Linux! So there is no Service Control...
>>
>I don't understand it, the MySQL docs say the standard install
>comes with latin1... But it seems to be not a rule...
>>
>Maybe I should recompile MySQL?!
>>
>Thanks a lot for Your hints!
>>
>
Marc,
>
Since you changed servers, the other possibility is the charset the
web page itself is sent as. What is the character encoding your
web server is sending for the page?
>
At the beginning there was file 'charset' with AddDefaultCharset
UTF-8 at my apache2 config dir. So I didn't get it I have this
problem. But then I mentioned umlaut problems at the admin backend,
so I've begun to look for the source of it. After deleting this
file, I have find out, that DB data won't be shown correctly.

The HTML pages have a HEADER with ISO-8859-15.

I have set a new 'charset' with ISO encoding - no result.
OK, let's back up a bit. What was the charset you used on the
previous system? Was it actually latin1? Or could it have been
something else?

Also, how did you move your data from one system to another?

Finally - to set the charset permanently, put it in your my.cfg file
under the [mysqld] section, i.e.

character_set_client = latin1

Well, to say the truth I have no idea what the previous system was set
like. I can access it only via Plesk, and the MySQL via phpMyAdmin
2.8.2.4. The system variables received via phpMyAdmin I have posted
already. If there is anything else I can find out, please tell me what
and where.

I made an export by phpMyAdmin and then called following on the
mysql-shell at the new system:

/usr/bin/mysql --host=localhost --password=password --user=user DBname
< /home/user/DBname.sql

Viewing the export file via SuperEdi all umlauts are correct and it
seems all data is latin1-encoded, there is always this statement in
there: CREATE DATABASE `shop` DEFAULT CHARACTER SET latin1 COLLATE
latin1_swedish_ci; or DEFAULT CHARSET=latin1 in the tables.
Viewing the old DB all umlauts are OK, doing it at the new one, none
is OK.

Yes, but chances are you used utf-8 to do the import, since that was the
default for the connection.
Yes, that's probabely the point. I still don't know how to set it to latin1.
>>
I have tried to set the charset permanently, but the server says
following after restart:
Jun 10 10:13:15 v213880518 mysqld_safe[20556]: started
Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 [Warning]
/usr/sbin/mysqld: ignoring option '--character-set-client$
Jun 10 10:13:15 v213880518 mysqld[20563]:
Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 InnoDB:
Started; log sequence number 0 2196292
Jun 10 10:13:16 v213880518 mysqld[20563]: 080610 10:13:16 [Note]
/usr/sbin/mysqld: bereit für Verbindungen.
Jun 10 10:13:16 v213880518 mysqld[20563]: Version:
'5.0.32-Debian_7etch5-log' Socket: '/var/run/mysqld/mysqld.sock'
Port: 3$
So, I habe no idea why it doesn't work.

How did you try to change the charset? And what *exactly* did you
enter? Again - a message without knowing exactly what you did is
worthless.
I'm sorry.

I connected to my vServer via putty.

I opened my.cnf entering: mcedit /etc/mysql/my.cnf

I added to my.cnf, as You told me, as follows

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/german
skip-external-locking
character_set_client = latin1
#character_set_connection = latin1 # i tried it once with this,
but it didn't work at all...
By the way, I also set default-character-set at the [client], see:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
#set names = latin1
#character-set-client = latin1 doesn't work, so pitty!
#character-set-connection = latin1 doesn't work, so pitty!
default-character-set = latin1
#default-collation = latin1_german2_ci doesn't work
I hope it's a better posting. I'm sorry once more.
Jun 27 '08 #33

P: n/a
Marc wrote:
Jerry Stuckle schrieb:
>Marc wrote:
>>Jerry Stuckle schrieb:
Marc wrote:
Jerry Stuckle schrieb:
>Marc wrote:
>>Jerry Stuckle schrieb:
>>>Marc wrote:
>>>>Jerry Stuckle schrieb:
>>>>>Marc wrote:
>>>>>>Jerry Stuckle schrieb:
>>>>>>>Marc wrote:
>>>>>>>>Jerry Stuckle schrieb:
>>>>>>>>>Marc wrote:
>>>>>>>>>>alex schrieb:
>>>>>>>>>>>>
>>>>>>>>>>>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.
>>>>>>>>>>>
>>>>>>>>>>Hello,
>>>>>>>>>>>
>>>>>>>>>>well, first of all, I'm newbie with MYSQL & Co., I have
>>>>>>>>>>read all answers and followed the link reading them all
>>>>>>>>>>carefully. I suppose I understand the matter.
>>>>>>>>>>>
>>>>>>>>>>I have apparently the same problem as Alex has. But my
>>>>>>>>>>connection is in UTF-8 and I need ISO-8859-1.
>>>>>>>>>>>
>>>>>>>>>>The problem accrued as I've moved from my webspace to a
>>>>>>>>>>vServer.
>>>>>>>>>>>
>>>>>>>>>>The old configuration shown by phpMyAdmin on MySQL 4.1.13
>>>>>>>>>>is:
>>>>>>>>>>>
>>>>>>>>>>character set client utf8
>>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>>character set connection utf8
>>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>>character set database latin1
>>>>>>>>>>character set results utf8
>>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>>character set server latin1
>>>>>>>>>>character set system utf8
>>>>>>>>>>collation connection utf8_general_ci
>>>>>>>>>>(Globaler Wert) latin1_swedish_ci
>>>>>>>>>>collation database latin1_swedish_ci
>>>>>>>>>>collation server latin1_swedish_ci
>>>>>>>>>>>
>>>>>>>>>>The new configuration shown by phpMyAdmin on my new MySQL
>>>>>>>>>>5.0.32 is exactly the same, the I set "SET NAMES latin1"
>>>>>>>>>>via mysql-shell.
>>>>>>>>>>>
>>>>>>>>>>BUT:
>>>>>>>>>>>
>>>>>>>>>>Making "SHOW VARIABLES" at mysql-shell gives:
>>>>>>>>>>>
>>>>>>>>>>character_set_client latin1
>>>>>>>>>>character_set_connection latin1
>>>>>>>>>>character_set_database latin1
>>>>>>>>>>character_set_filesystem binary
>>>>>>>>>>character set results latin1
>>>>>>>>>>character set server latin1
>>>>>>>>>>character set system utf8
>>>>>>>>>>collation connection latin1_swedish_ci
>>>>>>>>>>collation database latin1_swedish_ci
>>>>>>>>>>collation server latin1_swedish_ci
>>>>>>>>>>>
>>>>>>>>>>asking for the values by PHP gives:
>>>>>>>>>>>
>>>>>>>>>>character_set_client utf8
>>>>>>>>>>character_set_connection utf8
>>>>>>>>>>character_set_database latin1
>>>>>>>>>>character_set_filesystem binary
>>>>>>>>>>character_set_results utf8
>>>>>>>>>>character_set_server latin1
>>>>>>>>>>character_set_system utf8
>>>>>>>>>>collation_connection utf8_general_ci
>>>>>>>>>>collation_database latin1_swedish_ci
>>>>>>>>>>collation_server latin1_swedish_ci
>>>>>>>>>>>
>>>>>>>>>>I need the character_set_client and
>>>>>>>>>>character_set_connection in latin1.
>>>>>>>>>>>
>>>>>>>>>>The HTTP header sends to the browser ISO-8859-15. That
>>>>>>>>>>should stay so, because about 2.000 html pages are coded
>>>>>>>>>>in this. I don't care for the DB contect, then I have it
>>>>>>>>>>local in some programm. My only wish is to establish a
>>>>>>>>>>working-encoding-connection between the PHP and DB.
>>>>>>>>>>>
>>>>>>>>>>Can You please tell what exactly I have to do? Please,
>>>>>>>>>>understand I can't reprogramm the application, I even
>>>>>>>>>>don't want to, because it worked before. I'm quit sure
>>>>>>>>>>it's not correct setted up.
>>>>>>>>>>>
>>>>>>>>>>Thanks a lot in advance!!!
>>>>>>>>>>>
>>>>>>>>>>Regards
>>>>>>>>>>>
>>>>>>>>>>Marc
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>Oops, I didn't notice you crossposted this to
>>>>>>>>>comp.databases.mysql.
>>>>>>>>>>
>>>>>>>>>You indicated you had "non-standard" characters in the
>>>>>>>>>database. The first question is - what charset is the
>>>>>>>>>data in the database in? And exactly what are these
>>>>>>>>>"non-standard" characters?
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>Hello Jerry,
>>>>>>>>>
>>>>>>>>sorry for crossposting. I think it's not only MYSQL
>>>>>>>>problem, but also PHP problem.
>>>>>>>>>
>>>>>>>>I have German umlauts in there. I guess the old DB used
>>>>>>>>latin1 and the new one uses UTF-8 for internal corresponding.
>>>>>>>>>
>>>>>>>>As I have found it, it acctually doesn't matter how the DB
>>>>>>>>corresponds internal, then it's able to encode by request
>>>>>>>>sent by client. And that's the point, I guess PHP doesn't
>>>>>>>>say which encoding it would like to use. Or maybe it's
>>>>>>>>enough to tell DB which encoding it should normally use
>>>>>>>>talking to anything from outside.
>>>>>>>>>
>>>>>>>>Right?
>>>>>>>>>
>>>>>>>>So, I'm sorry for crossposting but I guess it's a
>>>>>>>>mysql-php-problem.
>>>>>>>>>
>>>>>>>>Thanks a lot for Your responce.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>Regards,
>>>>>>>>>
>>>>>>>>Marc
>>>>>>>>>
>>>>>>>>
>>>>>>>Marc,
>>>>>>>>
>>>>>>>No, it's my fault for not noticing the cross-posting in the
>>>>>>>first place.
>>>>>>>>
>>>>>>>You probably now have a mismatch between your connection and
>>>>>>>your table. Besides the charset used by the table, you
>>>>>>>have a charset used by the connection. I suspect you have a
>>>>>>>mismatch here. After connecting, call:
>>>>>>>>
>>>>>>>mysql_query("SET NAMES 'latin1'");
>>>>>>>>
>>>>>>>This should also set your connection to latin1.
>>>>>>>>
>>>>>>>
>>>>>>Jerry,
>>>>>>>
>>>>>>that's what I guess the problem is.
>>>>>>>
>>>>>>I've tried to call the command at the mysql-shell... and got
>>>>>>error 1064 (42000). Should it be called via the php script
>>>>>>perhaps? That's not that comfortable way for me...
>>>>>>>
>>>>>>Regards,
>>>>>>>
>>>>>>Marc
>>>>>>>
>>>>>>
>>>>>This statement is connection specific. You need to call it
>>>>>after connecting, and every time after connecting. Calling it
>>>>>from the command line only sets it for that connection; it
>>>>>won't set it for your PHP script, since it uses a different
>>>>>connection.
>>>>>>
>>>>>But your error message by itself is meaningless. What is the
>>>>>statement you're entering, and what is the entire error
>>>>>message you get? It works fine for me.
>>>>>>
>>>>>
>>>>>
>>>>I have connected with my server via putty, have logged in. Then
>>>>logged in into mysql:
>>>>>
>>>># mysql --password=mypassword --user=myuser [enter]
>>>>>
>>>>Welcome to the MySQL monitor. Commands end with ; or \g.
>>>>Your MySQL connection id is 246
>>>>Server version: 5.0.32-Debian_7etch5-log Debian etch distribution
>>>>>
>>>>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>>>>>
>>>>mysqlmysql_query("SET NAMES 'latin1'"); [enter]
>>>>>
>>>>ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die
>>>>korrekte Syntax im Handbuch nachschlagen bei 'mysql_query("SET
>>>>NAMES 'latin1'")' in Zeile 1
>>>>mysql>
>>>>>
>>>>How can I change the connection permamently for php? Is it
>>>>possible? Without changing the scripts...
>>>>>
>>>>
>>>No, mysql_query() is a PHP function call, not a MySQL
>>>statement. What you need is just
>>>>
>>>mysql>SET NAMES 'latin1';
>>>>
>>>The easiest way to change startup values is to go into MySQL
>>>Administrator, go to Service Control and click on the Configure
>>>Service tab. Then set the Localization section to the charset
>>>you wish.
>>>>
>>>
>>I have added this call to my php script. It didn't work!
>>>
>>I use MySQL on Linux! So there is no Service Control...
>>>
>>I don't understand it, the MySQL docs say the standard install
>>comes with latin1... But it seems to be not a rule...
>>>
>>Maybe I should recompile MySQL?!
>>>
>>Thanks a lot for Your hints!
>>>
>>
>Marc,
>>
>Since you changed servers, the other possibility is the charset
>the web page itself is sent as. What is the character encoding
>your web server is sending for the page?
>>
>
>
At the beginning there was file 'charset' with AddDefaultCharset
UTF-8 at my apache2 config dir. So I didn't get it I have this
problem. But then I mentioned umlaut problems at the admin backend,
so I've begun to look for the source of it. After deleting this
file, I have find out, that DB data won't be shown correctly.
>
The HTML pages have a HEADER with ISO-8859-15.
>
I have set a new 'charset' with ISO encoding - no result.
>

OK, let's back up a bit. What was the charset you used on the
previous system? Was it actually latin1? Or could it have been
something else?

Also, how did you move your data from one system to another?

Finally - to set the charset permanently, put it in your my.cfg file
under the [mysqld] section, i.e.

character_set_client = latin1
Well, to say the truth I have no idea what the previous system was
set like. I can access it only via Plesk, and the MySQL via
phpMyAdmin 2.8.2.4. The system variables received via phpMyAdmin I
have posted already. If there is anything else I can find out, please
tell me what and where.

I made an export by phpMyAdmin and then called following on the
mysql-shell at the new system:

/usr/bin/mysql --host=localhost --password=password --user=user
DBname < /home/user/DBname.sql

Viewing the export file via SuperEdi all umlauts are correct and it
seems all data is latin1-encoded, there is always this statement in
there: CREATE DATABASE `shop` DEFAULT CHARACTER SET latin1 COLLATE
latin1_swedish_ci; or DEFAULT CHARSET=latin1 in the tables.
Viewing the old DB all umlauts are OK, doing it at the new one, none
is OK.

Yes, but chances are you used utf-8 to do the import, since that was
the default for the connection.

Yes, that's probabely the point. I still don't know how to set it to
latin1.
>>>
I have tried to set the charset permanently, but the server says
following after restart:
Jun 10 10:13:15 v213880518 mysqld_safe[20556]: started
Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 [Warning]
/usr/sbin/mysqld: ignoring option '--character-set-client$
Jun 10 10:13:15 v213880518 mysqld[20563]:
Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 InnoDB:
Started; log sequence number 0 2196292
Jun 10 10:13:16 v213880518 mysqld[20563]: 080610 10:13:16 [Note]
/usr/sbin/mysqld: bereit für Verbindungen.
Jun 10 10:13:16 v213880518 mysqld[20563]: Version:
'5.0.32-Debian_7etch5-log' Socket: '/var/run/mysqld/mysqld.sock'
Port: 3$
So, I habe no idea why it doesn't work.

How did you try to change the charset? And what *exactly* did you
enter? Again - a message without knowing exactly what you did is
worthless.

I'm sorry.

I connected to my vServer via putty.

I opened my.cnf entering: mcedit /etc/mysql/my.cnf

I added to my.cnf, as You told me, as follows

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/german
skip-external-locking
character_set_client = latin1
#character_set_connection = latin1 # i tried it once with this,
but it didn't work at all...
By the way, I also set default-character-set at the [client], see:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
#set names = latin1
#character-set-client = latin1 doesn't work, so pitty!
#character-set-connection = latin1 doesn't work, so pitty!
default-character-set = latin1
#default-collation = latin1_german2_ci doesn't work
I hope it's a better posting. I'm sorry once more.
Here is your problem - it is character_set_client, not
character-set-client. See the difference?

(That's what the message said, also).

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

Jun 27 '08 #34

P: n/a
Jerry Stuckle schrieb:
Marc wrote:
>Jerry Stuckle schrieb:
>>Marc wrote:
Jerry Stuckle schrieb:
Marc wrote:
>Jerry Stuckle schrieb:
>>Marc wrote:
>>>Jerry Stuckle schrieb:
>>>>Marc wrote:
>>>>>Jerry Stuckle schrieb:
>>>>>>Marc wrote:
>>>>>>>Jerry Stuckle schrieb:
>>>>>>>>Marc wrote:
>>>>>>>>>Jerry Stuckle schrieb:
>>>>>>>>>>Marc wrote:
>>>>>>>>>>>alex schrieb:
>>>>>>>>>>>>>
>>>>>>>>>>>>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.
>>>>>>>>>>>>
>>>>>>>>>>>Hello,
>>>>>>>>>>>>
>>>>>>>>>>>well, first of all, I'm newbie with MYSQL & Co., I have
>>>>>>>>>>>read all answers and followed the link reading them all
>>>>>>>>>>>carefully. I suppose I understand the matter.
>>>>>>>>>>>>
>>>>>>>>>>>I have apparently the same problem as Alex has. But my
>>>>>>>>>>>connection is in UTF-8 and I need ISO-8859-1.
>>>>>>>>>>>>
>>>>>>>>>>>The problem accrued as I've moved from my webspace to a
>>>>>>>>>>>vServer.
>>>>>>>>>>>>
>>>>>>>>>>>The old configuration shown by phpMyAdmin on MySQL
>>>>>>>>>>>4.1.13 is:
>>>>>>>>>>>>
>>>>>>>>>>>character set client utf8
>>>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>>>character set connection utf8
>>>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>>>character set database latin1
>>>>>>>>>>>character set results utf8
>>>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>>>character set server latin1
>>>>>>>>>>>character set system utf8
>>>>>>>>>>>collation connection utf8_general_ci
>>>>>>>>>>>(Globaler Wert) latin1_swedish_ci
>>>>>>>>>>>collation database latin1_swedish_ci
>>>>>>>>>>>collation server latin1_swedish_ci
>>>>>>>>>>>>
>>>>>>>>>>>The new configuration shown by phpMyAdmin on my new
>>>>>>>>>>>MySQL 5.0.32 is exactly the same, the I set "SET NAMES
>>>>>>>>>>>latin1" via mysql-shell.
>>>>>>>>>>>>
>>>>>>>>>>>BUT:
>>>>>>>>>>>>
>>>>>>>>>>>Making "SHOW VARIABLES" at mysql-shell gives:
>>>>>>>>>>>>
>>>>>>>>>>>character_set_client latin1
>>>>>>>>>>>character_set_connection latin1
>>>>>>>>>>>character_set_database latin1
>>>>>>>>>>>character_set_filesystem binary
>>>>>>>>>>>character set results latin1
>>>>>>>>>>>character set server latin1
>>>>>>>>>>>character set system utf8
>>>>>>>>>>>collation connection latin1_swedish_ci
>>>>>>>>>>>collation database latin1_swedish_ci
>>>>>>>>>>>collation server latin1_swedish_ci
>>>>>>>>>>>>
>>>>>>>>>>>asking for the values by PHP gives:
>>>>>>>>>>>>
>>>>>>>>>>>character_set_client utf8
>>>>>>>>>>>character_set_connection utf8
>>>>>>>>>>>character_set_database latin1
>>>>>>>>>>>character_set_filesystem binary
>>>>>>>>>>>character_set_results utf8
>>>>>>>>>>>character_set_server latin1
>>>>>>>>>>>character_set_system utf8
>>>>>>>>>>>collation_connection utf8_general_ci
>>>>>>>>>>>collation_database latin1_swedish_ci
>>>>>>>>>>>collation_server latin1_swedish_ci
>>>>>>>>>>>>
>>>>>>>>>>>I need the character_set_client and
>>>>>>>>>>>character_set_connection in latin1.
>>>>>>>>>>>>
>>>>>>>>>>>The HTTP header sends to the browser ISO-8859-15. That
>>>>>>>>>>>should stay so, because about 2.000 html pages are coded
>>>>>>>>>>>in this. I don't care for the DB contect, then I have it
>>>>>>>>>>>local in some programm. My only wish is to establish a
>>>>>>>>>>>working-encoding-connection between the PHP and DB.
>>>>>>>>>>>>
>>>>>>>>>>>Can You please tell what exactly I have to do? Please,
>>>>>>>>>>>understand I can't reprogramm the application, I even
>>>>>>>>>>>don't want to, because it worked before. I'm quit sure
>>>>>>>>>>>it's not correct setted up.
>>>>>>>>>>>>
>>>>>>>>>>>Thanks a lot in advance!!!
>>>>>>>>>>>>
>>>>>>>>>>>Regards
>>>>>>>>>>>>
>>>>>>>>>>>Marc
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>Oops, I didn't notice you crossposted this to
>>>>>>>>>>comp.databases.mysql.
>>>>>>>>>>>
>>>>>>>>>>You indicated you had "non-standard" characters in the
>>>>>>>>>>database. The first question is - what charset is the
>>>>>>>>>>data in the database in? And exactly what are these
>>>>>>>>>>"non-standard" characters?
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>Hello Jerry,
>>>>>>>>>>
>>>>>>>>>sorry for crossposting. I think it's not only MYSQL
>>>>>>>>>problem, but also PHP problem.
>>>>>>>>>>
>>>>>>>>>I have German umlauts in there. I guess the old DB used
>>>>>>>>>latin1 and the new one uses UTF-8 for internal corresponding.
>>>>>>>>>>
>>>>>>>>>As I have found it, it acctually doesn't matter how the DB
>>>>>>>>>corresponds internal, then it's able to encode by request
>>>>>>>>>sent by client. And that's the point, I guess PHP doesn't
>>>>>>>>>say which encoding it would like to use. Or maybe it's
>>>>>>>>>enough to tell DB which encoding it should normally use
>>>>>>>>>talking to anything from outside.
>>>>>>>>>>
>>>>>>>>>Right?
>>>>>>>>>>
>>>>>>>>>So, I'm sorry for crossposting but I guess it's a
>>>>>>>>>mysql-php-problem.
>>>>>>>>>>
>>>>>>>>>Thanks a lot for Your responce.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>Regards,
>>>>>>>>>>
>>>>>>>>>Marc
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>Marc,
>>>>>>>>>
>>>>>>>>No, it's my fault for not noticing the cross-posting in the
>>>>>>>>first place.
>>>>>>>>>
>>>>>>>>You probably now have a mismatch between your connection
>>>>>>>>and your table. Besides the charset used by the table,
>>>>>>>>you have a charset used by the connection. I suspect you
>>>>>>>>have a mismatch here. After connecting, call:
>>>>>>>>>
>>>>>>>>mysql_query("SET NAMES 'latin1'");
>>>>>>>>>
>>>>>>>>This should also set your connection to latin1.
>>>>>>>>>
>>>>>>>>
>>>>>>>Jerry,
>>>>>>>>
>>>>>>>that's what I guess the problem is.
>>>>>>>>
>>>>>>>I've tried to call the command at the mysql-shell... and got
>>>>>>>error 1064 (42000). Should it be called via the php script
>>>>>>>perhaps? That's not that comfortable way for me...
>>>>>>>>
>>>>>>>Regards,
>>>>>>>>
>>>>>>>Marc
>>>>>>>>
>>>>>>>
>>>>>>This statement is connection specific. You need to call it
>>>>>>after connecting, and every time after connecting. Calling
>>>>>>it from the command line only sets it for that connection; it
>>>>>>won't set it for your PHP script, since it uses a different
>>>>>>connection.
>>>>>>>
>>>>>>But your error message by itself is meaningless. What is the
>>>>>>statement you're entering, and what is the entire error
>>>>>>message you get? It works fine for me.
>>>>>>>
>>>>>>
>>>>>>
>>>>>I have connected with my server via putty, have logged in.
>>>>>Then logged in into mysql:
>>>>>>
>>>>># mysql --password=mypassword --user=myuser [enter]
>>>>>>
>>>>>Welcome to the MySQL monitor. Commands end with ; or \g.
>>>>>Your MySQL connection id is 246
>>>>>Server version: 5.0.32-Debian_7etch5-log Debian etch distribution
>>>>>>
>>>>>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>>>>>>
>>>>>mysqlmysql_query("SET NAMES 'latin1'"); [enter]
>>>>>>
>>>>>ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die
>>>>>korrekte Syntax im Handbuch nachschlagen bei 'mysql_query("SET
>>>>>NAMES 'latin1'")' in Zeile 1
>>>>>mysql>
>>>>>>
>>>>>How can I change the connection permamently for php? Is it
>>>>>possible? Without changing the scripts...
>>>>>>
>>>>>
>>>>No, mysql_query() is a PHP function call, not a MySQL
>>>>statement. What you need is just
>>>>>
>>>>mysql>SET NAMES 'latin1';
>>>>>
>>>>The easiest way to change startup values is to go into MySQL
>>>>Administrator, go to Service Control and click on the Configure
>>>>Service tab. Then set the Localization section to the charset
>>>>you wish.
>>>>>
>>>>
>>>I have added this call to my php script. It didn't work!
>>>>
>>>I use MySQL on Linux! So there is no Service Control...
>>>>
>>>I don't understand it, the MySQL docs say the standard install
>>>comes with latin1... But it seems to be not a rule...
>>>>
>>>Maybe I should recompile MySQL?!
>>>>
>>>Thanks a lot for Your hints!
>>>>
>>>
>>Marc,
>>>
>>Since you changed servers, the other possibility is the charset
>>the web page itself is sent as. What is the character encoding
>>your web server is sending for the page?
>>>
>>
>>
>At the beginning there was file 'charset' with AddDefaultCharset
>UTF-8 at my apache2 config dir. So I didn't get it I have this
>problem. But then I mentioned umlaut problems at the admin
>backend, so I've begun to look for the source of it. After
>deleting this file, I have find out, that DB data won't be shown
>correctly.
>>
>The HTML pages have a HEADER with ISO-8859-15.
>>
>I have set a new 'charset' with ISO encoding - no result.
>>
>
OK, let's back up a bit. What was the charset you used on the
previous system? Was it actually latin1? Or could it have been
something else?
>
Also, how did you move your data from one system to another?
>
Finally - to set the charset permanently, put it in your my.cfg
file under the [mysqld] section, i.e.
>
character_set_client = latin1
>

Well, to say the truth I have no idea what the previous system was
set like. I can access it only via Plesk, and the MySQL via
phpMyAdmin 2.8.2.4. The system variables received via phpMyAdmin I
have posted already. If there is anything else I can find out,
please tell me what and where.

I made an export by phpMyAdmin and then called following on the
mysql-shell at the new system:

/usr/bin/mysql --host=localhost --password=password --user=user
DBname < /home/user/DBname.sql

Viewing the export file via SuperEdi all umlauts are correct and it
seems all data is latin1-encoded, there is always this statement in
there: CREATE DATABASE `shop` DEFAULT CHARACTER SET latin1 COLLATE
latin1_swedish_ci; or DEFAULT CHARSET=latin1 in the tables.
Viewing the old DB all umlauts are OK, doing it at the new one, none
is OK.
Yes, but chances are you used utf-8 to do the import, since that was
the default for the connection.

Yes, that's probabely the point. I still don't know how to set it to
latin1.
>>>>
I have tried to set the charset permanently, but the server says
following after restart:
Jun 10 10:13:15 v213880518 mysqld_safe[20556]: started
Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 [Warning]
/usr/sbin/mysqld: ignoring option '--character-set-client$
Jun 10 10:13:15 v213880518 mysqld[20563]:
Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 InnoDB:
Started; log sequence number 0 2196292
Jun 10 10:13:16 v213880518 mysqld[20563]: 080610 10:13:16 [Note]
/usr/sbin/mysqld: bereit für Verbindungen.
Jun 10 10:13:16 v213880518 mysqld[20563]: Version:
'5.0.32-Debian_7etch5-log' Socket: '/var/run/mysqld/mysqld.sock'
Port: 3$
So, I habe no idea why it doesn't work.
How did you try to change the charset? And what *exactly* did you
enter? Again - a message without knowing exactly what you did is
worthless.

I'm sorry.

I connected to my vServer via putty.

I opened my.cnf entering: mcedit /etc/mysql/my.cnf

I added to my.cnf, as You told me, as follows

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/german
skip-external-locking
character_set_client = latin1
#character_set_connection = latin1 # i tried it once with
this, but it didn't work at all...
By the way, I also set default-character-set at the [client], see:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
#set names = latin1
#character-set-client = latin1 doesn't work, so pitty!
#character-set-connection = latin1 doesn't work, so pitty!
default-character-set = latin1
#default-collation = latin1_german2_ci doesn't work
I hope it's a better posting. I'm sorry once more.

Here is your problem - it is character_set_client, not
character-set-client. See the difference?

(That's what the message said, also).
I'm sorry, it seems You are not that right.

Trying to call MySQL at the shell via putty I get following error message:

v213880518:/# mysql --password=password --user=user
mysql: unknown variable 'character_set_client=latin1'

It doesn't matter how I use it in [client], neither character_set_client
nor character-set-client work. So that's why they are commented.

So, once more, there are two blocks in my.cnf: [mysqld] and [client].
Should I use only one of them to set the charsets or both? And with
which option?
Jun 27 '08 #35

P: n/a
Marc wrote:
Jerry Stuckle schrieb:
>Marc wrote:
>>Jerry Stuckle schrieb:
Marc wrote:
Jerry Stuckle schrieb:
>Marc wrote:
>>Jerry Stuckle schrieb:
>>>Marc wrote:
>>>>Jerry Stuckle schrieb:
>>>>>Marc wrote:
>>>>>>Jerry Stuckle schrieb:
>>>>>>>Marc wrote:
>>>>>>>>Jerry Stuckle schrieb:
>>>>>>>>>Marc wrote:
>>>>>>>>>>Jerry Stuckle schrieb:
>>>>>>>>>>>Marc wrote:
>>>>>>>>>>>>alex schrieb:
>>>>>>>>>>>>>>
>>>>>>>>>>>>>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.
>>>>>>>>>>>>>
>>>>>>>>>>>>Hello,
>>>>>>>>>>>>>
>>>>>>>>>>>>well, first of all, I'm newbie with MYSQL & Co., I have
>>>>>>>>>>>>read all answers and followed the link reading them all
>>>>>>>>>>>>carefully. I suppose I understand the matter.
>>>>>>>>>>>>>
>>>>>>>>>>>>I have apparently the same problem as Alex has. But my
>>>>>>>>>>>>connection is in UTF-8 and I need ISO-8859-1.
>>>>>>>>>>>>>
>>>>>>>>>>>>The problem accrued as I've moved from my webspace to a
>>>>>>>>>>>>vServer.
>>>>>>>>>>>>>
>>>>>>>>>>>>The old configuration shown by phpMyAdmin on MySQL
>>>>>>>>>>>>4.1.13 is:
>>>>>>>>>>>>>
>>>>>>>>>>>>character set client utf8
>>>>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>>>>character set connection utf8
>>>>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>>>>character set database latin1
>>>>>>>>>>>>character set results utf8
>>>>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>>>>character set server latin1
>>>>>>>>>>>>character set system utf8
>>>>>>>>>>>>collation connection utf8_general_ci
>>>>>>>>>>>>(Globaler Wert) latin1_swedish_ci
>>>>>>>>>>>>collation database latin1_swedish_ci
>>>>>>>>>>>>collation server latin1_swedish_ci
>>>>>>>>>>>>>
>>>>>>>>>>>>The new configuration shown by phpMyAdmin on my new
>>>>>>>>>>>>MySQL 5.0.32 is exactly the same, the I set "SET NAMES
>>>>>>>>>>>>latin1" via mysql-shell.
>>>>>>>>>>>>>
>>>>>>>>>>>>BUT:
>>>>>>>>>>>>>
>>>>>>>>>>>>Making "SHOW VARIABLES" at mysql-shell gives:
>>>>>>>>>>>>>
>>>>>>>>>>>>character_set_client latin1
>>>>>>>>>>>>character_set_connection latin1
>>>>>>>>>>>>character_set_database latin1
>>>>>>>>>>>>character_set_filesystem binary
>>>>>>>>>>>>character set results latin1
>>>>>>>>>>>>character set server latin1
>>>>>>>>>>>>character set system utf8
>>>>>>>>>>>>collation connection latin1_swedish_ci
>>>>>>>>>>>>collation database latin1_swedish_ci
>>>>>>>>>>>>collation server latin1_swedish_ci
>>>>>>>>>>>>>
>>>>>>>>>>>>asking for the values by PHP gives:
>>>>>>>>>>>>>
>>>>>>>>>>>>character_set_client utf8
>>>>>>>>>>>>character_set_connection utf8
>>>>>>>>>>>>character_set_database latin1
>>>>>>>>>>>>character_set_filesystem binary
>>>>>>>>>>>>character_set_results utf8
>>>>>>>>>>>>character_set_server latin1
>>>>>>>>>>>>character_set_system utf8
>>>>>>>>>>>>collation_connection utf8_general_ci
>>>>>>>>>>>>collation_database latin1_swedish_ci
>>>>>>>>>>>>collation_server latin1_swedish_ci
>>>>>>>>>>>>>
>>>>>>>>>>>>I need the character_set_client and
>>>>>>>>>>>>character_set_connection in latin1.
>>>>>>>>>>>>>
>>>>>>>>>>>>The HTTP header sends to the browser ISO-8859-15. That
>>>>>>>>>>>>should stay so, because about 2.000 html pages are
>>>>>>>>>>>>coded in this. I don't care for the DB contect, then I
>>>>>>>>>>>>have it local in some programm. My only wish is to
>>>>>>>>>>>>establish a working-encoding-connection between the PHP
>>>>>>>>>>>>and DB.
>>>>>>>>>>>>>
>>>>>>>>>>>>Can You please tell what exactly I have to do? Please,
>>>>>>>>>>>>understand I can't reprogramm the application, I even
>>>>>>>>>>>>don't want to, because it worked before. I'm quit sure
>>>>>>>>>>>>it's not correct setted up.
>>>>>>>>>>>>>
>>>>>>>>>>>>Thanks a lot in advance!!!
>>>>>>>>>>>>>
>>>>>>>>>>>>Regards
>>>>>>>>>>>>>
>>>>>>>>>>>>Marc
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>Oops, I didn't notice you crossposted this to
>>>>>>>>>>>comp.databases.mysql.
>>>>>>>>>>>>
>>>>>>>>>>>You indicated you had "non-standard" characters in the
>>>>>>>>>>>database. The first question is - what charset is the
>>>>>>>>>>>data in the database in? And exactly what are these
>>>>>>>>>>>"non-standard" characters?
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>Hello Jerry,
>>>>>>>>>>>
>>>>>>>>>>sorry for crossposting. I think it's not only MYSQL
>>>>>>>>>>problem, but also PHP problem.
>>>>>>>>>>>
>>>>>>>>>>I have German umlauts in there. I guess the old DB used
>>>>>>>>>>latin1 and the new one uses UTF-8 for internal
>>>>>>>>>>corresponding.
>>>>>>>>>>>
>>>>>>>>>>As I have found it, it acctually doesn't matter how the
>>>>>>>>>>DB corresponds internal, then it's able to encode by
>>>>>>>>>>request sent by client. And that's the point, I guess PHP
>>>>>>>>>>doesn't say which encoding it would like to use. Or maybe
>>>>>>>>>>it's enough to tell DB which encoding it should normally
>>>>>>>>>>use talking to anything from outside.
>>>>>>>>>>>
>>>>>>>>>>Right?
>>>>>>>>>>>
>>>>>>>>>>So, I'm sorry for crossposting but I guess it's a
>>>>>>>>>>mysql-php-problem.
>>>>>>>>>>>
>>>>>>>>>>Thanks a lot for Your responce.
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>Regards,
>>>>>>>>>>>
>>>>>>>>>>Marc
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>Marc,
>>>>>>>>>>
>>>>>>>>>No, it's my fault for not noticing the cross-posting in
>>>>>>>>>the first place.
>>>>>>>>>>
>>>>>>>>>You probably now have a mismatch between your connection
>>>>>>>>>and your table. Besides the charset used by the table,
>>>>>>>>>you have a charset used by the connection. I suspect you
>>>>>>>>>have a mismatch here. After connecting, call:
>>>>>>>>>>
>>>>>>>>>mysql_query("SET NAMES 'latin1'");
>>>>>>>>>>
>>>>>>>>>This should also set your connection to latin1.
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>Jerry,
>>>>>>>>>
>>>>>>>>that's what I guess the problem is.
>>>>>>>>>
>>>>>>>>I've tried to call the command at the mysql-shell... and
>>>>>>>>got error 1064 (42000). Should it be called via the php
>>>>>>>>script perhaps? That's not that comfortable way for me...
>>>>>>>>>
>>>>>>>>Regards,
>>>>>>>>>
>>>>>>>>Marc
>>>>>>>>>
>>>>>>>>
>>>>>>>This statement is connection specific. You need to call it
>>>>>>>after connecting, and every time after connecting. Calling
>>>>>>>it from the command line only sets it for that connection;
>>>>>>>it won't set it for your PHP script, since it uses a
>>>>>>>different connection.
>>>>>>>>
>>>>>>>But your error message by itself is meaningless. What is
>>>>>>>the statement you're entering, and what is the entire error
>>>>>>>message you get? It works fine for me.
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>I have connected with my server via putty, have logged in.
>>>>>>Then logged in into mysql:
>>>>>>>
>>>>>># mysql --password=mypassword --user=myuser [enter]
>>>>>>>
>>>>>>Welcome to the MySQL monitor. Commands end with ; or \g.
>>>>>>Your MySQL connection id is 246
>>>>>>Server version: 5.0.32-Debian_7etch5-log Debian etch
>>>>>>distribution
>>>>>>>
>>>>>>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>>>>>>>
>>>>>>mysqlmysql_query("SET NAMES 'latin1'"); [enter]
>>>>>>>
>>>>>>ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die
>>>>>>korrekte Syntax im Handbuch nachschlagen bei
>>>>>>'mysql_query("SET NAMES 'latin1'")' in Zeile 1
>>>>>>mysql>
>>>>>>>
>>>>>>How can I change the connection permamently for php? Is it
>>>>>>possible? Without changing the scripts...
>>>>>>>
>>>>>>
>>>>>No, mysql_query() is a PHP function call, not a MySQL
>>>>>statement. What you need is just
>>>>>>
>>>>>mysql>SET NAMES 'latin1';
>>>>>>
>>>>>The easiest way to change startup values is to go into MySQL
>>>>>Administrator, go to Service Control and click on the
>>>>>Configure Service tab. Then set the Localization section to
>>>>>the charset you wish.
>>>>>>
>>>>>
>>>>I have added this call to my php script. It didn't work!
>>>>>
>>>>I use MySQL on Linux! So there is no Service Control...
>>>>>
>>>>I don't understand it, the MySQL docs say the standard install
>>>>comes with latin1... But it seems to be not a rule...
>>>>>
>>>>Maybe I should recompile MySQL?!
>>>>>
>>>>Thanks a lot for Your hints!
>>>>>
>>>>
>>>Marc,
>>>>
>>>Since you changed servers, the other possibility is the charset
>>>the web page itself is sent as. What is the character encoding
>>>your web server is sending for the page?
>>>>
>>>
>>>
>>At the beginning there was file 'charset' with AddDefaultCharset
>>UTF-8 at my apache2 config dir. So I didn't get it I have this
>>problem. But then I mentioned umlaut problems at the admin
>>backend, so I've begun to look for the source of it. After
>>deleting this file, I have find out, that DB data won't be shown
>>correctly.
>>>
>>The HTML pages have a HEADER with ISO-8859-15.
>>>
>>I have set a new 'charset' with ISO encoding - no result.
>>>
>>
>OK, let's back up a bit. What was the charset you used on the
>previous system? Was it actually latin1? Or could it have been
>something else?
>>
>Also, how did you move your data from one system to another?
>>
>Finally - to set the charset permanently, put it in your my.cfg
>file under the [mysqld] section, i.e.
>>
>character_set_client = latin1
>>
>
Well, to say the truth I have no idea what the previous system was
set like. I can access it only via Plesk, and the MySQL via
phpMyAdmin 2.8.2.4. The system variables received via phpMyAdmin I
have posted already. If there is anything else I can find out,
please tell me what and where.
>
I made an export by phpMyAdmin and then called following on the
mysql-shell at the new system:
>
/usr/bin/mysql --host=localhost --password=password --user=user
DBname < /home/user/DBname.sql
>
Viewing the export file via SuperEdi all umlauts are correct and it
seems all data is latin1-encoded, there is always this statement in
there: CREATE DATABASE `shop` DEFAULT CHARACTER SET latin1 COLLATE
latin1_swedish_ci; or DEFAULT CHARSET=latin1 in the tables.
>
>
Viewing the old DB all umlauts are OK, doing it at the new one,
none is OK.
>

Yes, but chances are you used utf-8 to do the import, since that was
the default for the connection.

Yes, that's probabely the point. I still don't know how to set it to
latin1.

>
I have tried to set the charset permanently, but the server says
following after restart:
>
>
Jun 10 10:13:15 v213880518 mysqld_safe[20556]: started
Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 [Warning]
/usr/sbin/mysqld: ignoring option '--character-set-client$
Jun 10 10:13:15 v213880518 mysqld[20563]:
Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 InnoDB:
Started; log sequence number 0 2196292
Jun 10 10:13:16 v213880518 mysqld[20563]: 080610 10:13:16 [Note]
/usr/sbin/mysqld: bereit für Verbindungen.
Jun 10 10:13:16 v213880518 mysqld[20563]: Version:
'5.0.32-Debian_7etch5-log' Socket: '/var/run/mysqld/mysqld.sock'
Port: 3$
>
>
So, I habe no idea why it doesn't work.
>

How did you try to change the charset? And what *exactly* did you
enter? Again - a message without knowing exactly what you did is
worthless.
I'm sorry.

I connected to my vServer via putty.

I opened my.cnf entering: mcedit /etc/mysql/my.cnf

I added to my.cnf, as You told me, as follows

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/german
skip-external-locking
character_set_client = latin1
#character_set_connection = latin1 # i tried it once with
this, but it didn't work at all...
By the way, I also set default-character-set at the [client], see:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
#set names = latin1
#character-set-client = latin1 doesn't work, so pitty!
#character-set-connection = latin1 doesn't work, so pitty!
default-character-set = latin1
#default-collation = latin1_german2_ci doesn't work
I hope it's a better posting. I'm sorry once more.

Here is your problem - it is character_set_client, not
character-set-client. See the difference?

(That's what the message said, also).

I'm sorry, it seems You are not that right.

Trying to call MySQL at the shell via putty I get following error message:

v213880518:/# mysql --password=password --user=user
mysql: unknown variable 'character_set_client=latin1'

It doesn't matter how I use it in [client], neither character_set_client
nor character-set-client work. So that's why they are commented.

So, once more, there are two blocks in my.cnf: [mysqld] and [client].
Should I use only one of them to set the charsets or both? And with
which option?
Ah, I missed you had those in [client]. I shouldn't look at code before
the first cup of coffee :-).

They go in the [mysqld] section. That's where you set the mysql
parameters. [client] is for client use.

I know it isn't completely intuitive. But that's how it works.

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

Jun 27 '08 #36

P: n/a
Jerry Stuckle schrieb:
Marc wrote:
>Jerry Stuckle schrieb:
>>Marc wrote:
Jerry Stuckle schrieb:
Marc wrote:
>Jerry Stuckle schrieb:
>>Marc wrote:
>>>Jerry Stuckle schrieb:
>>>>Marc wrote:
>>>>>Jerry Stuckle schrieb:
>>>>>>Marc wrote:
>>>>>>>Jerry Stuckle schrieb:
>>>>>>>>Marc wrote:
>>>>>>>>>Jerry Stuckle schrieb:
>>>>>>>>>>Marc wrote:
>>>>>>>>>>>Jerry Stuckle schrieb:
>>>>>>>>>>>>Marc wrote:
>>>>>>>>>>>>>alex schrieb:
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>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.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>Hello,
>>>>>>>>>>>>>>
>>>>>>>>>>>>>well, first of all, I'm newbie with MYSQL & Co., I
>>>>>>>>>>>>>have read all answers and followed the link reading
>>>>>>>>>>>>>them all carefully. I suppose I understand the matter.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>I have apparently the same problem as Alex has. But my
>>>>>>>>>>>>>connection is in UTF-8 and I need ISO-8859-1.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>The problem accrued as I've moved from my webspace to
>>>>>>>>>>>>>a vServer.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>The old configuration shown by phpMyAdmin on MySQL
>>>>>>>>>>>>>4.1.13 is:
>>>>>>>>>>>>>>
>>>>>>>>>>>>>character set client utf8
>>>>>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>>>>>character set connection utf8
>>>>>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>>>>>character set database latin1
>>>>>>>>>>>>>character set results utf8
>>>>>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>>>>>character set server latin1
>>>>>>>>>>>>>character set system utf8
>>>>>>>>>>>>>collation connection utf8_general_ci
>>>>>>>>>>>>>(Globaler Wert) latin1_swedish_ci
>>>>>>>>>>>>>collation database latin1_swedish_ci
>>>>>>>>>>>>>collation server latin1_swedish_ci
>>>>>>>>>>>>>>
>>>>>>>>>>>>>The new configuration shown by phpMyAdmin on my new
>>>>>>>>>>>>>MySQL 5.0.32 is exactly the same, the I set "SET NAMES
>>>>>>>>>>>>>latin1" via mysql-shell.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>BUT:
>>>>>>>>>>>>>>
>>>>>>>>>>>>>Making "SHOW VARIABLES" at mysql-shell gives:
>>>>>>>>>>>>>>
>>>>>>>>>>>>>character_set_client latin1
>>>>>>>>>>>>>character_set_connection latin1
>>>>>>>>>>>>>character_set_database latin1
>>>>>>>>>>>>>character_set_filesystem binary
>>>>>>>>>>>>>character set results latin1
>>>>>>>>>>>>>character set server latin1
>>>>>>>>>>>>>character set system utf8
>>>>>>>>>>>>>collation connection latin1_swedish_ci
>>>>>>>>>>>>>collation database latin1_swedish_ci
>>>>>>>>>>>>>collation server latin1_swedish_ci
>>>>>>>>>>>>>>
>>>>>>>>>>>>>asking for the values by PHP gives:
>>>>>>>>>>>>>>
>>>>>>>>>>>>>character_set_client utf8
>>>>>>>>>>>>>character_set_connection utf8
>>>>>>>>>>>>>character_set_database latin1
>>>>>>>>>>>>>character_set_filesystem binary
>>>>>>>>>>>>>character_set_results utf8
>>>>>>>>>>>>>character_set_server latin1
>>>>>>>>>>>>>character_set_system utf8
>>>>>>>>>>>>>collation_connection utf8_general_ci
>>>>>>>>>>>>>collation_database latin1_swedish_ci
>>>>>>>>>>>>>collation_server latin1_swedish_ci
>>>>>>>>>>>>>>
>>>>>>>>>>>>>I need the character_set_client and
>>>>>>>>>>>>>character_set_connection in latin1.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>The HTTP header sends to the browser ISO-8859-15. That
>>>>>>>>>>>>>should stay so, because about 2.000 html pages are
>>>>>>>>>>>>>coded in this. I don't care for the DB contect, then I
>>>>>>>>>>>>>have it local in some programm. My only wish is to
>>>>>>>>>>>>>establish a working-encoding-connection between the
>>>>>>>>>>>>>PHP and DB.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>Can You please tell what exactly I have to do? Please,
>>>>>>>>>>>>>understand I can't reprogramm the application, I even
>>>>>>>>>>>>>don't want to, because it worked before. I'm quit sure
>>>>>>>>>>>>>it's not correct setted up.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>Thanks a lot in advance!!!
>>>>>>>>>>>>>>
>>>>>>>>>>>>>Regards
>>>>>>>>>>>>>>
>>>>>>>>>>>>>Marc
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>Oops, I didn't notice you crossposted this to
>>>>>>>>>>>>comp.databases.mysql.
>>>>>>>>>>>>>
>>>>>>>>>>>>You indicated you had "non-standard" characters in the
>>>>>>>>>>>>database. The first question is - what charset is the
>>>>>>>>>>>>data in the database in? And exactly what are these
>>>>>>>>>>>>"non-standard" characters?
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>Hello Jerry,
>>>>>>>>>>>>
>>>>>>>>>>>sorry for crossposting. I think it's not only MYSQL
>>>>>>>>>>>problem, but also PHP problem.
>>>>>>>>>>>>
>>>>>>>>>>>I have German umlauts in there. I guess the old DB used
>>>>>>>>>>>latin1 and the new one uses UTF-8 for internal
>>>>>>>>>>>corresponding.
>>>>>>>>>>>>
>>>>>>>>>>>As I have found it, it acctually doesn't matter how the
>>>>>>>>>>>DB corresponds internal, then it's able to encode by
>>>>>>>>>>>request sent by client. And that's the point, I guess
>>>>>>>>>>>PHP doesn't say which encoding it would like to use. Or
>>>>>>>>>>>maybe it's enough to tell DB which encoding it should
>>>>>>>>>>>normally use talking to anything from outside.
>>>>>>>>>>>>
>>>>>>>>>>>Right?
>>>>>>>>>>>>
>>>>>>>>>>>So, I'm sorry for crossposting but I guess it's a
>>>>>>>>>>>mysql-php-problem.
>>>>>>>>>>>>
>>>>>>>>>>>Thanks a lot for Your responce.
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>Regards,
>>>>>>>>>>>>
>>>>>>>>>>>Marc
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>Marc,
>>>>>>>>>>>
>>>>>>>>>>No, it's my fault for not noticing the cross-posting in
>>>>>>>>>>the first place.
>>>>>>>>>>>
>>>>>>>>>>You probably now have a mismatch between your connection
>>>>>>>>>>and your table. Besides the charset used by the table,
>>>>>>>>>>you have a charset used by the connection. I suspect you
>>>>>>>>>>have a mismatch here. After connecting, call:
>>>>>>>>>>>
>>>>>>>>>>mysql_query("SET NAMES 'latin1'");
>>>>>>>>>>>
>>>>>>>>>>This should also set your connection to latin1.
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>Jerry,
>>>>>>>>>>
>>>>>>>>>that's what I guess the problem is.
>>>>>>>>>>
>>>>>>>>>I've tried to call the command at the mysql-shell... and
>>>>>>>>>got error 1064 (42000). Should it be called via the php
>>>>>>>>>script perhaps? That's not that comfortable way for me...
>>>>>>>>>>
>>>>>>>>>Regards,
>>>>>>>>>>
>>>>>>>>>Marc
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>This statement is connection specific. You need to call it
>>>>>>>>after connecting, and every time after connecting. Calling
>>>>>>>>it from the command line only sets it for that connection;
>>>>>>>>it won't set it for your PHP script, since it uses a
>>>>>>>>different connection.
>>>>>>>>>
>>>>>>>>But your error message by itself is meaningless. What is
>>>>>>>>the statement you're entering, and what is the entire error
>>>>>>>>message you get? It works fine for me.
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>I have connected with my server via putty, have logged in.
>>>>>>>Then logged in into mysql:
>>>>>>>>
>>>>>>># mysql --password=mypassword --user=myuser [enter]
>>>>>>>>
>>>>>>>Welcome to the MySQL monitor. Commands end with ; or \g.
>>>>>>>Your MySQL connection id is 246
>>>>>>>Server version: 5.0.32-Debian_7etch5-log Debian etch
>>>>>>>distribution
>>>>>>>>
>>>>>>>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>>>>>>>>
>>>>>>>mysqlmysql_query("SET NAMES 'latin1'"); [enter]
>>>>>>>>
>>>>>>>ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die
>>>>>>>korrekte Syntax im Handbuch nachschlagen bei
>>>>>>>'mysql_query("SET NAMES 'latin1'")' in Zeile 1
>>>>>>>mysql>
>>>>>>>>
>>>>>>>How can I change the connection permamently for php? Is it
>>>>>>>possible? Without changing the scripts...
>>>>>>>>
>>>>>>>
>>>>>>No, mysql_query() is a PHP function call, not a MySQL
>>>>>>statement. What you need is just
>>>>>>>
>>>>>>mysql>SET NAMES 'latin1';
>>>>>>>
>>>>>>The easiest way to change startup values is to go into MySQL
>>>>>>Administrator, go to Service Control and click on the
>>>>>>Configure Service tab. Then set the Localization section to
>>>>>>the charset you wish.
>>>>>>>
>>>>>>
>>>>>I have added this call to my php script. It didn't work!
>>>>>>
>>>>>I use MySQL on Linux! So there is no Service Control...
>>>>>>
>>>>>I don't understand it, the MySQL docs say the standard install
>>>>>comes with latin1... But it seems to be not a rule...
>>>>>>
>>>>>Maybe I should recompile MySQL?!
>>>>>>
>>>>>Thanks a lot for Your hints!
>>>>>>
>>>>>
>>>>Marc,
>>>>>
>>>>Since you changed servers, the other possibility is the charset
>>>>the web page itself is sent as. What is the character encoding
>>>>your web server is sending for the page?
>>>>>
>>>>
>>>>
>>>At the beginning there was file 'charset' with AddDefaultCharset
>>>UTF-8 at my apache2 config dir. So I didn't get it I have this
>>>problem. But then I mentioned umlaut problems at the admin
>>>backend, so I've begun to look for the source of it. After
>>>deleting this file, I have find out, that DB data won't be shown
>>>correctly.
>>>>
>>>The HTML pages have a HEADER with ISO-8859-15.
>>>>
>>>I have set a new 'charset' with ISO encoding - no result.
>>>>
>>>
>>OK, let's back up a bit. What was the charset you used on the
>>previous system? Was it actually latin1? Or could it have been
>>something else?
>>>
>>Also, how did you move your data from one system to another?
>>>
>>Finally - to set the charset permanently, put it in your my.cfg
>>file under the [mysqld] section, i.e.
>>>
>>character_set_client = latin1
>>>
>>
>Well, to say the truth I have no idea what the previous system was
>set like. I can access it only via Plesk, and the MySQL via
>phpMyAdmin 2.8.2.4. The system variables received via phpMyAdmin I
>have posted already. If there is anything else I can find out,
>please tell me what and where.
>>
>I made an export by phpMyAdmin and then called following on the
>mysql-shell at the new system:
>>
>/usr/bin/mysql --host=localhost --password=password --user=user
>DBname < /home/user/DBname.sql
>>
>Viewing the export file via SuperEdi all umlauts are correct and
>it seems all data is latin1-encoded, there is always this
>statement in there: CREATE DATABASE `shop` DEFAULT CHARACTER SET
>latin1 COLLATE latin1_swedish_ci; or DEFAULT CHARSET=latin1 in the
>tables.
>>
>>
>Viewing the old DB all umlauts are OK, doing it at the new one,
>none is OK.
>>
>
Yes, but chances are you used utf-8 to do the import, since that
was the default for the connection.

Yes, that's probabely the point. I still don't know how to set it to
latin1.

>>
>I have tried to set the charset permanently, but the server says
>following after restart:
>>
>>
>Jun 10 10:13:15 v213880518 mysqld_safe[20556]: started
>Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15
>[Warning] /usr/sbin/mysqld: ignoring option '--character-set-client$
>Jun 10 10:13:15 v213880518 mysqld[20563]:
>Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 InnoDB:
>Started; log sequence number 0 2196292
>Jun 10 10:13:16 v213880518 mysqld[20563]: 080610 10:13:16 [Note]
>/usr/sbin/mysqld: bereit für Verbindungen.
>Jun 10 10:13:16 v213880518 mysqld[20563]: Version:
>'5.0.32-Debian_7etch5-log' Socket: '/var/run/mysqld/mysqld.sock'
>Port: 3$
>>
>>
>So, I habe no idea why it doesn't work.
>>
>
How did you try to change the charset? And what *exactly* did you
enter? Again - a message without knowing exactly what you did is
worthless.
>

I'm sorry.

I connected to my vServer via putty.

I opened my.cnf entering: mcedit /etc/mysql/my.cnf

I added to my.cnf, as You told me, as follows

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/german
skip-external-locking
character_set_client = latin1
#character_set_connection = latin1 # i tried it once with
this, but it didn't work at all...
By the way, I also set default-character-set at the [client], see:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
#set names = latin1
#character-set-client = latin1 doesn't work, so pitty!
#character-set-connection = latin1 doesn't work, so pitty!
default-character-set = latin1
#default-collation = latin1_german2_ci doesn't work
I hope it's a better posting. I'm sorry once more.
Here is your problem - it is character_set_client, not
character-set-client. See the difference?

(That's what the message said, also).

I'm sorry, it seems You are not that right.

Trying to call MySQL at the shell via putty I get following error
message:

v213880518:/# mysql --password=password --user=user
mysql: unknown variable 'character_set_client=latin1'

It doesn't matter how I use it in [client], neither
character_set_client nor character-set-client work. So that's why they
are commented.

So, once more, there are two blocks in my.cnf: [mysqld] and [client].
Should I use only one of them to set the charsets or both? And with
which option?

Ah, I missed you had those in [client]. I shouldn't look at code before
the first cup of coffee :-).

They go in the [mysqld] section. That's where you set the mysql
parameters. [client] is for client use.

I know it isn't completely intuitive. But that's how it works.

You are absolutely right, it's not intuitive, not at all. ;-)

I'm reading again the MySQL docs, chapter 9th, Internationalization and
Localization.

I'm also studying again: http://www.drummy.org/archives/37/1/ - an
explaintion about the umlaut problem comming from the mySQLDumper developer.

I have to loose it, before I get sick :)

Thanks a lot for all Your hints!
Jun 27 '08 #37

P: n/a
Greetings, Marc.
In reply to Your message dated Tuesday, June 10, 2008, 13:51:36,
AnrDaemon schrieb:
>Greetings, Marc.
In reply to Your message dated Tuesday, June 10, 2008, 01:45:36,
>>>>>>>You have the answer, but apparently lost it in your explanation. I'll quote:
>>>>
>>>>The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
>>>>exactly the same, the I set "SET NAMES latin1" via mysql-shell.
>>>You must tell to server, what encoding you want, every time you connecting to
>>>database.
>>>Just add execution of 'SET NAMES latin1 COLLATE whatever_collation_you_need'
>>>statement right after the moment you have connected to database in your script.
>>thanks a lot for this advice. But it's not possible to change the whole
>>project for this. That's why I would like to set it generally up. On the
>>other side, I don't need any other encoding.
>You lost the point.
>It is not "changing the whole project", it is the action that project must do
>once in only one script - one that establishing connection to database.
>It is, literally, one line of code to add to whole your project, and it will
>serve you to the end of times.
that seems that easy, but it cannot be the cleanest way. I don't feel so
comfortable about it. It has worked already. I just would like to set
everything right up.
It has worked already just because server was configured to work that way.
It was a 4.0 server most likely, or server config have had an init_connect
option set. Your argument "it was worked already" pointless, because
environment have changed.

You have only two ways to solve your problem.
1. I think it is right way.
Adapt environment for your needs (by changing connection encoding every time
you connect to database, it is one line of code in one single file, or in few
files, if you are not using some sort of abstraction layer) - it will work as
long as you are using MySQL servers for your database. They may have any
settings on their own, but you'll have what you need in your script all the
time.

2. Hard and one that I not considering acceptable for myself.
Change your scripts to handle current database output encoding. It will strike
just about every line of your code, in simple words - it will require to
rewrite about half of your project.

I'm sending the data from some external DB by one php script and I'm
trying to add this simple line to it... It's gonna take some time, I to
new on this stuff. I'm gonna report.
It's simple as find-replace :)
Locate the point in your (key word - your) script, where you are using
mysql_connect().
Add
mysql_query('SET NAMES latin1');
Right after calling mysql_connect().

It should work.

If you are using some sort of abstraction layer, it may be a bit different,
but not harder.

>>Adding mysql_query didn't work, I mean it didn't change anything. The
Umlauts are still wrong.

You're stating your troubles without any confirmation.
Please show us few lines of your code where you have changed it, and explain,
how are you tested that it "didn't change anything".

I think you have checked it with your other script, which have not been
changed, thus not showing you what you want.
>>I'm really fed up. It makes me sick.

I have feeling of your anger, but that's not what makes man wise.

I'm really sorry for giving my angery.
I use xt commerce. There are only 2 files calling mysql_connect(). So I
have done what You have told, I have three times added mysql_query...
Well, you have it done almost the right way, but I'll change it a bit for more
correct handling of possible errors.

function xtc_db_connect($server = DB_SERVER, $username = DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database = DB_DATABASE, $link = 'db_link') {
global $$link;

if (USE_PCONNECT == 'true') {
$$link = mysql_pconnect($server, $username, $password);
} else {
$$link = mysql_connect($server, $username, $password);
}

if ($$link) {
// Set encoding if we successfully connected to database
mysql_query('SET NAMES latin1', $$link);

mysql_select_db($database);
}

return $$link;
}
--
Sincerely Yours, AnrDaemon <an*******@freemail.ru>

Jun 27 '08 #38

P: n/a
AnrDaemon schrieb:
Greetings, Marc.
In reply to Your message dated Tuesday, June 10, 2008, 13:51:36,
>AnrDaemon schrieb:
>>Greetings, Marc.
In reply to Your message dated Tuesday, June 10, 2008, 01:45:36,

>>>>You have the answer, but apparently lost it in your explanation. I'll quote:
>>>>>
>>>>>The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is
>>>>>exactly the same, the I set "SET NAMES latin1" via mysql-shell.
>>>>You must tell to server, what encoding you want, every time you connecting to
>>>>database.
>>>>Just add execution of 'SET NAMES latin1 COLLATE whatever_collation_you_need'
>>>>statement right after the moment you have connected to database in your script.
>>>thanks a lot for this advice. But it's not possible to change the whole
>>>project for this. That's why I would like to set it generally up. On the
>>>other side, I don't need any other encoding.
>>You lost the point.
>>It is not "changing the whole project", it is the action that project must do
>>once in only one script - one that establishing connection to database.
>>It is, literally, one line of code to add to whole your project, and it will
>>serve you to the end of times.
>that seems that easy, but it cannot be the cleanest way. I don't feel so
>comfortable about it. It has worked already. I just would like to set
>everything right up.
It has worked already just because server was configured to work that way.
It was a 4.0 server most likely, or server config have had an init_connect
option set. Your argument "it was worked already" pointless, because
environment have changed.
>
You have only two ways to solve your problem.
1. I think it is right way.
Adapt environment for your needs (by changing connection encoding every time
you connect to database, it is one line of code in one single file, or in few
files, if you are not using some sort of abstraction layer) - it will work as
long as you are using MySQL servers for your database. They may have any
settings on their own, but you'll have what you need in your script all the
time.
>
2. Hard and one that I not considering acceptable for myself.
Change your scripts to handle current database output encoding. It will strike
just about every line of your code, in simple words - it will require to
rewrite about half of your project.
>
>I'm sending the data from some external DB by one php script and I'm
>trying to add this simple line to it... It's gonna take some time, I to
>new on this stuff. I'm gonna report.
It's simple as find-replace :)
Locate the point in your (key word - your) script, where you are using
mysql_connect().
Add
mysql_query('SET NAMES latin1');
Right after calling mysql_connect().
>
It should work.
>
If you are using some sort of abstraction layer, it may be a bit different,
but not harder.
>

Adding mysql_query didn't work, I mean it didn't change anything. The
Umlauts are still wrong.
You're stating your troubles without any confirmation.
Please show us few lines of your code where you have changed it, and explain,
how are you tested that it "didn't change anything".

I think you have checked it with your other script, which have not been
changed, thus not showing you what you want.

I'm really fed up. It makes me sick.
I have feeling of your anger, but that's not what makes man wise.

>I'm really sorry for giving my angery.
>I use xt commerce. There are only 2 files calling mysql_connect(). So I
have done what You have told, I have three times added mysql_query...

Well, you have it done almost the right way, but I'll change it a bit for more
correct handling of possible errors.

function xtc_db_connect($server = DB_SERVER, $username = DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database = DB_DATABASE, $link = 'db_link') {
global $$link;

if (USE_PCONNECT == 'true') {
$$link = mysql_pconnect($server, $username, $password);
} else {
$$link = mysql_connect($server, $username, $password);
}

if ($$link) {
// Set encoding if we successfully connected to database
mysql_query('SET NAMES latin1', $$link);

mysql_select_db($database);
}

return $$link;
}

Thanks a lot for Your help.

It didn't work, I don't really now why. Last night I had a server crash,
a very bad one. I set the server new up and was more then very carefull
importing my old DB to MySQL. It works now.

My export file was utf-encoded. I saved it as ANSI. Then I copied it on
my system calling >mysql --host=localhost --password=pd --user=u
--default-character-set=latin1 < /root/db.sql. On this way I got a clean DB.

Then I changed in apache2 settings dir the charset file, from
AddDefaultCharset UTF-8 to AddDefaultCharset ISO-8859-1. After it I
uploaded all my files.

It works now.

Really, thanks a lot!!

Regards,

Marc
Jun 27 '08 #39

P: n/a
Marc schrieb:
Jerry Stuckle schrieb:
>Marc wrote:
>>Jerry Stuckle schrieb:
Marc wrote:
Jerry Stuckle schrieb:
>Marc wrote:
>>Jerry Stuckle schrieb:
>>>Marc wrote:
>>>>Jerry Stuckle schrieb:
>>>>>Marc wrote:
>>>>>>Jerry Stuckle schrieb:
>>>>>>>Marc wrote:
>>>>>>>>Jerry Stuckle schrieb:
>>>>>>>>>Marc wrote:
>>>>>>>>>>Jerry Stuckle schrieb:
>>>>>>>>>>>Marc wrote:
>>>>>>>>>>>>Jerry Stuckle schrieb:
>>>>>>>>>>>>>Marc wrote:
>>>>>>>>>>>>>>alex schrieb:
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>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.
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>Hello,
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>well, first of all, I'm newbie with MYSQL & Co., I
>>>>>>>>>>>>>>have read all answers and followed the link reading
>>>>>>>>>>>>>>them all carefully. I suppose I understand the matter.
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>I have apparently the same problem as Alex has. But
>>>>>>>>>>>>>>my connection is in UTF-8 and I need ISO-8859-1.
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>The problem accrued as I've moved from my webspace to
>>>>>>>>>>>>>>a vServer.
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>The old configuration shown by phpMyAdmin on MySQL
>>>>>>>>>>>>>>4.1.13 is:
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>character set client utf8
>>>>>>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>>>>>>character set connection utf8
>>>>>>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>>>>>>character set database latin1
>>>>>>>>>>>>>>character set results utf8
>>>>>>>>>>>>>>(Globaler Wert) latin1
>>>>>>>>>>>>>>character set server latin1
>>>>>>>>>>>>>>character set system utf8
>>>>>>>>>>>>>>collation connection utf8_general_ci
>>>>>>>>>>>>>>(Globaler Wert) latin1_swedish_ci
>>>>>>>>>>>>>>collation database latin1_swedish_ci
>>>>>>>>>>>>>>collation server latin1_swedish_ci
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>The new configuration shown by phpMyAdmin on my new
>>>>>>>>>>>>>>MySQL 5.0.32 is exactly the same, the I set "SET
>>>>>>>>>>>>>>NAMES latin1" via mysql-shell.
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>BUT:
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>Making "SHOW VARIABLES" at mysql-shell gives:
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>character_set_client latin1
>>>>>>>>>>>>>>character_set_connection latin1
>>>>>>>>>>>>>>character_set_database latin1
>>>>>>>>>>>>>>character_set_filesystem binary
>>>>>>>>>>>>>>character set results latin1
>>>>>>>>>>>>>>character set server latin1
>>>>>>>>>>>>>>character set system utf8
>>>>>>>>>>>>>>collation connection latin1_swedish_ci
>>>>>>>>>>>>>>collation database latin1_swedish_ci
>>>>>>>>>>>>>>collation server latin1_swedish_ci
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>asking for the values by PHP gives:
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>character_set_client utf8
>>>>>>>>>>>>>>character_set_connection utf8
>>>>>>>>>>>>>>character_set_database latin1
>>>>>>>>>>>>>>character_set_filesystem binary
>>>>>>>>>>>>>>character_set_results utf8
>>>>>>>>>>>>>>character_set_server latin1
>>>>>>>>>>>>>>character_set_system utf8
>>>>>>>>>>>>>>collation_connection utf8_general_ci
>>>>>>>>>>>>>>collation_database latin1_swedish_ci
>>>>>>>>>>>>>>collation_server latin1_swedish_ci
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>I need the character_set_client and
>>>>>>>>>>>>>>character_set_connection in latin1.
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>The HTTP header sends to the browser ISO-8859-15.
>>>>>>>>>>>>>>That should stay so, because about 2.000 html pages
>>>>>>>>>>>>>>are coded in this. I don't care for the DB contect,
>>>>>>>>>>>>>>then I have it local in some programm. My only wish
>>>>>>>>>>>>>>is to establish a working-encoding-connection between
>>>>>>>>>>>>>>the PHP and DB.
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>Can You please tell what exactly I have to do?
>>>>>>>>>>>>>>Please, understand I can't reprogramm the
>>>>>>>>>>>>>>application, I even don't want to, because it worked
>>>>>>>>>>>>>>before. I'm quit sure it's not correct setted up.
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>Thanks a lot in advance!!!
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>Regards
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>Marc
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>Oops, I didn't notice you crossposted this to
>>>>>>>>>>>>>comp.databases.mysql.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>You indicated you had "non-standard" characters in the
>>>>>>>>>>>>>database. The first question is - what charset is the
>>>>>>>>>>>>>data in the database in? And exactly what are these
>>>>>>>>>>>>>"non-standard" characters?
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>Hello Jerry,
>>>>>>>>>>>>>
>>>>>>>>>>>>sorry for crossposting. I think it's not only MYSQL
>>>>>>>>>>>>problem, but also PHP problem.
>>>>>>>>>>>>>
>>>>>>>>>>>>I have German umlauts in there. I guess the old DB used
>>>>>>>>>>>>latin1 and the new one uses UTF-8 for internal
>>>>>>>>>>>>corresponding.
>>>>>>>>>>>>>
>>>>>>>>>>>>As I have found it, it acctually doesn't matter how the
>>>>>>>>>>>>DB corresponds internal, then it's able to encode by
>>>>>>>>>>>>request sent by client. And that's the point, I guess
>>>>>>>>>>>>PHP doesn't say which encoding it would like to use. Or
>>>>>>>>>>>>maybe it's enough to tell DB which encoding it should
>>>>>>>>>>>>normally use talking to anything from outside.
>>>>>>>>>>>>>
>>>>>>>>>>>>Right?
>>>>>>>>>>>>>
>>>>>>>>>>>>So, I'm sorry for crossposting but I guess it's a
>>>>>>>>>>>>mysql-php-problem.
>>>>>>>>>>>>>
>>>>>>>>>>>>Thanks a lot for Your responce.
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>Regards,
>>>>>>>>>>>>>
>>>>>>>>>>>>Marc
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>Marc,
>>>>>>>>>>>>
>>>>>>>>>>>No, it's my fault for not noticing the cross-posting in
>>>>>>>>>>>the first place.
>>>>>>>>>>>>
>>>>>>>>>>>You probably now have a mismatch between your connection
>>>>>>>>>>>and your table. Besides the charset used by the table,
>>>>>>>>>>>you have a charset used by the connection. I suspect
>>>>>>>>>>>you have a mismatch here. After connecting, call:
>>>>>>>>>>>>
>>>>>>>>>>>mysql_query("SET NAMES 'latin1'");
>>>>>>>>>>>>
>>>>>>>>>>>This should also set your connection to latin1.
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>Jerry,
>>>>>>>>>>>
>>>>>>>>>>that's what I guess the problem is.
>>>>>>>>>>>
>>>>>>>>>>I've tried to call the command at the mysql-shell... and
>>>>>>>>>>got error 1064 (42000). Should it be called via the php
>>>>>>>>>>script perhaps? That's not that comfortable way for me...
>>>>>>>>>>>
>>>>>>>>>>Regards,
>>>>>>>>>>>
>>>>>>>>>>Marc
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>This statement is connection specific. You need to call
>>>>>>>>>it after connecting, and every time after connecting.
>>>>>>>>>Calling it from the command line only sets it for that
>>>>>>>>>connection; it won't set it for your PHP script, since it
>>>>>>>>>uses a different connection.
>>>>>>>>>>
>>>>>>>>>But your error message by itself is meaningless. What is
>>>>>>>>>the statement you're entering, and what is the entire
>>>>>>>>>error message you get? It works fine for me.
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>I have connected with my server via putty, have logged in.
>>>>>>>>Then logged in into mysql:
>>>>>>>>>
>>>>>>>># mysql --password=mypassword --user=myuser [enter]
>>>>>>>>>
>>>>>>>>Welcome to the MySQL monitor. Commands end with ; or \g.
>>>>>>>>Your MySQL connection id is 246
>>>>>>>>Server version: 5.0.32-Debian_7etch5-log Debian etch
>>>>>>>>distribution
>>>>>>>>>
>>>>>>>>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>>>>>>>>>
>>>>>>>>mysqlmysql_query("SET NAMES 'latin1'"); [enter]
>>>>>>>>>
>>>>>>>>ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die
>>>>>>>>korrekte Syntax im Handbuch nachschlagen bei
>>>>>>>>'mysql_query("SET NAMES 'latin1'")' in Zeile 1
>>>>>>>>mysql>
>>>>>>>>>
>>>>>>>>How can I change the connection permamently for php? Is it
>>>>>>>>possible? Without changing the scripts...
>>>>>>>>>
>>>>>>>>
>>>>>>>No, mysql_query() is a PHP function call, not a MySQL
>>>>>>>statement. What you need is just
>>>>>>>>
>>>>>>>mysql>SET NAMES 'latin1';
>>>>>>>>
>>>>>>>The easiest way to change startup values is to go into MySQL
>>>>>>>Administrator, go to Service Control and click on the
>>>>>>>Configure Service tab. Then set the Localization section to
>>>>>>>the charset you wish.
>>>>>>>>
>>>>>>>
>>>>>>I have added this call to my php script. It didn't work!
>>>>>>>
>>>>>>I use MySQL on Linux! So there is no Service Control...
>>>>>>>
>>>>>>I don't understand it, the MySQL docs say the standard
>>>>>>install comes with latin1... But it seems to be not a rule...
>>>>>>>
>>>>>>Maybe I should recompile MySQL?!
>>>>>>>
>>>>>>Thanks a lot for Your hints!
>>>>>>>
>>>>>>
>>>>>Marc,
>>>>>>
>>>>>Since you changed servers, the other possibility is the
>>>>>charset the web page itself is sent as. What is the character
>>>>>encoding your web server is sending for the page?
>>>>>>
>>>>>
>>>>>
>>>>At the beginning there was file 'charset' with
>>>>AddDefaultCharset UTF-8 at my apache2 config dir. So I didn't
>>>>get it I have this problem. But then I mentioned umlaut
>>>>problems at the admin backend, so I've begun to look for the
>>>>source of it. After deleting this file, I have find out, that
>>>>DB data won't be shown correctly.
>>>>>
>>>>The HTML pages have a HEADER with ISO-8859-15.
>>>>>
>>>>I have set a new 'charset' with ISO encoding - no result.
>>>>>
>>>>
>>>OK, let's back up a bit. What was the charset you used on the
>>>previous system? Was it actually latin1? Or could it have been
>>>something else?
>>>>
>>>Also, how did you move your data from one system to another?
>>>>
>>>Finally - to set the charset permanently, put it in your my.cfg
>>>file under the [mysqld] section, i.e.
>>>>
>>>character_set_client = latin1
>>>>
>>>
>>Well, to say the truth I have no idea what the previous system
>>was set like. I can access it only via Plesk, and the MySQL via
>>phpMyAdmin 2.8.2.4. The system variables received via phpMyAdmin
>>I have posted already. If there is anything else I can find out,
>>please tell me what and where.
>>>
>>I made an export by phpMyAdmin and then called following on the
>>mysql-shell at the new system:
>>>
>>/usr/bin/mysql --host=localhost --password=password --user=user
>>DBname < /home/user/DBname.sql
>>>
>>Viewing the export file via SuperEdi all umlauts are correct and
>>it seems all data is latin1-encoded, there is always this
>>statement in there: CREATE DATABASE `shop` DEFAULT CHARACTER SET
>>latin1 COLLATE latin1_swedish_ci; or DEFAULT CHARSET=latin1 in
>>the tables.
>>>
>>>
>>Viewing the old DB all umlauts are OK, doing it at the new one,
>>none is OK.
>>>
>>
>Yes, but chances are you used utf-8 to do the import, since that
>was the default for the connection.
>
Yes, that's probabely the point. I still don't know how to set it
to latin1.
>
>>>
>>I have tried to set the charset permanently, but the server says
>>following after restart:
>>>
>>>
>>Jun 10 10:13:15 v213880518 mysqld_safe[20556]: started
>>Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15
>>[Warning] /usr/sbin/mysqld: ignoring option '--character-set-client$
>>Jun 10 10:13:15 v213880518 mysqld[20563]:
>>Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15
>>InnoDB: Started; log sequence number 0 2196292
>>Jun 10 10:13:16 v213880518 mysqld[20563]: 080610 10:13:16 [Note]
>>/usr/sbin/mysqld: bereit für Verbindungen.
>>Jun 10 10:13:16 v213880518 mysqld[20563]: Version:
>>'5.0.32-Debian_7etch5-log' Socket:
>>'/var/run/mysqld/mysqld.sock' Port: 3$
>>>
>>>
>>So, I habe no idea why it doesn't work.
>>>
>>
>How did you try to change the charset? And what *exactly* did you
>enter? Again - a message without knowing exactly what you did is
>worthless.
>>
>
I'm sorry.
>
I connected to my vServer via putty.
>
I opened my.cnf entering: mcedit /etc/mysql/my.cnf
>
I added to my.cnf, as You told me, as follows
>
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/german
skip-external-locking
character_set_client = latin1
#character_set_connection = latin1 # i tried it once with
this, but it didn't work at all...
>
>
By the way, I also set default-character-set at the [client], see:
>
>
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
#set names = latin1
#character-set-client = latin1 doesn't work, so pitty!
#character-set-connection = latin1 doesn't work, so pitty!
default-character-set = latin1
#default-collation = latin1_german2_ci doesn't work
>
>
I hope it's a better posting. I'm sorry once more.
>

Here is your problem - it is character_set_client, not
character-set-client. See the difference?

(That's what the message said, also).
I'm sorry, it seems You are not that right.

Trying to call MySQL at the shell via putty I get following error
message:

v213880518:/# mysql --password=password --user=user
mysql: unknown variable 'character_set_client=latin1'

It doesn't matter how I use it in [client], neither
character_set_client nor character-set-client work. So that's why
they are commented.

So, once more, there are two blocks in my.cnf: [mysqld] and [client].
Should I use only one of them to set the charsets or both? And with
which option?

Ah, I missed you had those in [client]. I shouldn't look at code
before the first cup of coffee :-).

They go in the [mysqld] section. That's where you set the mysql
parameters. [client] is for client use.

I know it isn't completely intuitive. But that's how it works.


You are absolutely right, it's not intuitive, not at all. ;-)

I'm reading again the MySQL docs, chapter 9th, Internationalization and
Localization.

I'm also studying again: http://www.drummy.org/archives/37/1/ - an
explaintion about the umlaut problem comming from the mySQLDumper
developer.

I have to loose it, before I get sick :)

Thanks a lot for all Your hints!


Thanks a lot for Your help.

It didn't work, I don't really now why. Last night I had a server crash,
a very bad one. I set the server new up and was more then very carefull
importing my old DB to MySQL. It works now.

My export file was utf-encoded. I saved it as ANSI. Then I copied it on
my system calling >mysql --host=localhost --password=pd --user=u
--default-character-set=latin1 < /root/db.sql. On this way I got a clean DB.

Then I changed in apache2 settings dir the charset file, from
AddDefaultCharset UTF-8 to AddDefaultCharset ISO-8859-1. After it I
uploaded all my files.

It works now.

Really, thanks a lot!!

Regards,

Marc

PS: sorry for doubleposting!
Jun 27 '08 #40

This discussion thread is closed

Replies have been disabled for this discussion.