Problem with pma charset configuration

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Postby trejder » 15. April 2008 13:07

Hi,

Wiedmann wrote:- 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! :(

Wiedmann wrote: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?

Wiedmann wrote: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? :/

Wiedmann wrote: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
trejder
 
Posts: 42
Joined: 13. April 2008 06:45

Postby Wiedmann » 15. April 2008 13:17

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.
Wiedmann
AF Moderator
 
Posts: 17106
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby trejder » 15. April 2008 13:21

Wiedmann wrote: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).
trejder
 
Posts: 42
Joined: 13. April 2008 06:45

Postby Wiedmann » 15. April 2008 13:29

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....
Wiedmann
AF Moderator
 
Posts: 17106
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby trejder » 15. April 2008 13:34

Wiedmann wrote: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?
trejder
 
Posts: 42
Joined: 13. April 2008 06:45

Postby sari42 » 15. April 2008 15:14

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!
sari42
 
Posts: 800
Joined: 27. November 2005 18:28

!

Postby trejder » 16. April 2008 08:41

sari42 wrote: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
 
Posts: 42
Joined: 13. April 2008 06:45

Postby trejder » 16. April 2008 09:02

Wiedmann wrote: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
trejder
 
Posts: 42
Joined: 13. April 2008 06:45

Postby Wiedmann » 16. April 2008 09:06

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

You have an example?
Wiedmann
AF Moderator
 
Posts: 17106
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby trejder » 16. April 2008 09:11

Wiedmann wrote: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.
trejder
 
Posts: 42
Joined: 13. April 2008 06:45

Postby Wiedmann » 16. April 2008 10:01

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.
Wiedmann
AF Moderator
 
Posts: 17106
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby trejder » 16. April 2008 10:08

Wiedmann wrote: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.
trejder
 
Posts: 42
Joined: 13. April 2008 06:45

Previous

Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 58 guests