Problem with pma charset configuration

[trejder]

Hi,

- create your table with

Code: Select all

CREATE TABLE tbl_name (column_list) CHARACTER SET utf8

And if you store or retrieve data to/from the database with PHP, use this as first statement/query after the connection:

Code: Select all

SET NAMES 'utf8';

That's all.

Done! In my code looks like this (direct copy&paste from .sql file):

Code: Select all

DROP TABLE IF EXISTS `strings`;
CREATE TABLE `strings` (
  `ident` varchar(21) NOT NULL,
  `pl` varchar(100) NOT NULL,
  `en` varchar(100) NOT NULL,
  `fr` varchar(100) NOT NULL,
  KEY `ident` (`ident`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

And I use SET NAMES utf8 each time after I connect to database. Still not working!

Maybe there is allready wrong data in the server. And so this data can't retrieve correctly from the server (anymore).

Rather impossible - because with each and another try, I drop whole database and import it (through phpMyAdmin) again. File (.sql) is encoded in utf8, you said that by default pma imports in utf8. So the only way data is being garbled, is when pma imports it into database. Is it possible?

Well, but the easiest way is to use allways UTF-8, because you have now only 1 charset for all things and not many. And you can use every language you want with UTF-8.

Yes! Of course! But what to do, if your server admin does not understand this and refuses to configure server properly? :/

BTW: French have ISO-8859-1 or ISO-8859-15 (like German).

Hm... That's odd! ISO-8859-1 is XHTML equivalent for latin1, yes? Then how to explain situation described by me a few post above:

When I used latin1_general_ci collation I was even unable to store (add, update) text in a row, because each time I tried, I got this error message: "Warning: #1366 Incorrect string value: '\xB3a. Sp...' for column 'fr' at row 1".

I tried to insert some text with French national letters and got such an error message...

Regards,
Tom

[Wiedmann]

But what to do, if your server admin does not understand this and refuses to configure server properly?

The server admin must do nothing. The values the server admin can set are only used, if you don't define a charset in "CREATE TABLE".

I drop whole database and import it (through phpMyAdmin) again. File (.sql) is encoded in utf8,

Have you ever tried to import the file without PMA?

ISO-8859-1 is XHTML equivalent for latin1, yes?

Yes.

[trejder]

Have you ever tried to import the file without PMA?

I can't! Because I have no access to mysql itself on a remote server (or don't know how to do this). But my admin did and said to me that got the same effects - garbage (? signs).

[Wiedmann]

Because I have no access to mysql itself on a remote server

What does you mean with "mysql itself"? Maybe you have no shell access (and can't use the MySQL commandline client), but you can do this with PHP, Perl... (LOAD DATA LOCAL INFILE).

But my admin did and said to me that got the same effects - garbage (? signs).

Maybe you should engage a better admin, if he can't import and use utf-8 with MySQL For him this should be easy to see what happens, because he have the real files and can see what happens....

[trejder]

What does you mean with "mysql itself"? Maybe you have no shell access (and can't use the MySQL commandline client), but you can do this with PHP, Perl... (LOAD DATA LOCAL INFILE).

Yes, I have to test it out and see. Maybe this solve a problem. But if problem really is there, that data is incorectly imported into database - I still can't understand, why pma garbage it?

Anyway I'll try and see what happens?

[sari42]

the character set of the table takes only effect when you (manually) create new columns. when you import a backup/dump the old column character sets are restored - same mess as before!

[trejder]

the character set of the table takes only effect when you (manually) create new columns. when you import a backup/dump the old column character sets are restored - same mess as before!

Thanks! Problem is already solved. I setup encoding of database / table / columns to utf-8, reencoded dump-file to utf-8, imported it into database and use SET NAMES utf8, and now all works fine! Thanks!

[trejder]

PHP always use latin1, if you don't change it with "SET NAME" or better mysql(i)_set_charset.

I can't use mysql_set_charset, because my webserver admin uses very old (antique) versions of PHP and MySQL, which don't have this function. But using your idea with SET NAMES solved the problem.

You said that PHP uses latin1 by default. Using SET NAMES changes encoding that is used in data exchange with database. But can you tell me, how can I change encoding of pages generated by PHP itself? Right now I have problem with database solved (as said before) but some functions (strftime with setlocale set to Polish to be correct) generates localized results with incorectly encoded national characters. How can I change that?

Best regards,
Tom

[Wiedmann]

but some functions (strftime with setlocale set to Polish to be correct) generates localized results with incorectly encoded national characters.

You have an example?

[trejder]

You have an example?

Sorry, for bothering you. I solved this problem already. Until now I used:

Code: Select all

setlocale(LC_ALL, 'pl_PL');

But, as I found at:

http://www.onphp5.com/article/22

I have to change it to:

Code: Select all

setlocale(LC_ALL, 'pl_PL.UTF-8');

Adding .UTF-8 forces PHP to generate all results of localized-functions in selected encoding.

[Wiedmann]

Adding .UTF-8 forces PHP to generate all results of localized-functions in selected encoding.

Not 100%. Locale is a function of your server (PHP only pass-through the result from the server).

Of course:
a) I thought you are on Windows, and "pl_PL.UTF-8" is not valid on Windows
b) not all *nix servers have this locale installed.

[trejder]

Not 100%. Locale is a function of your server (PHP only pass-through the result from the server).

Of course:
a) I thought you are on Windows, and "pl_PL.UTF-8" is not valid on Windows
b) not all *nix servers have this locale installed.

a) I use Windows for localhost (development and testing) and Linux for production server, where resulting page will finally be hosted. That's why I use:

Code: Select all

$lang = get_lang();
if ($lang == 'pl') setlocale(LC_ALL, 'pl_PL.UTF-8', 'plk');
if ($lang == 'fr') setlocale(LC_ALL, 'fr_FR.UTF-8', 'fra');

where get_lang() is my private function for detecting language, user selected. It works on both development (Windows + XAMPP) and production (Linux) server.

b) For now, it looks that my production server supports pl_PL.UTF-8 and I hope that will not change in future.