LITTLEBLACKDOG.COM

Posted: Sat Feb 24, 2007 10:43 am Post subject: php & mysql & UTF8

hey folks,

Simple problem, hard solution. I'm trying to process a series of flatfiles ( 2.4 million of them ), and some of the flatfiles have foreign languages in them. The problem is, when i go and add them to the table, as soon as the insert query reaches a foreign character, it stops adding the rest of the field.. so if it was manana ( with the enye thing instead of regular n ), it would put 'ma' as the field.

I've already tried doing set name, and set character name via sql query, but the mysql client build into php always defaults to latin1.

Any other suggestoins anyone can give me?

Posted: Sat Feb 24, 2007 11:29 am Post subject:

utf8 should be supported natively.

perhaps you are mis-handling some of the strings?!?!

can you share the snippets of code where you "read" and "write" the data?!?!

Posted: Sat Feb 24, 2007 11:38 am Post subject:

wolven wrote:
I've already tried doing set name, and set character name via sql query, but the mysql client build into php always defaults to latin1.

That would have been my suggestion: after mysql_connect() and mysql_select_db() did you try to add the command:

mysql_query("SET NAMES utf8");

??

Also, make sure you are using varchar and not char.

Posted: Sat Feb 24, 2007 12:07 pm Post subject:

varchar?.. all the documentation i've been reading is char.

Posted: Sat Feb 24, 2007 12:09 pm Post subject:

fathertyme wrote:
utf8 should be supported natively.

perhaps you are mis-handling some of the strings?!?!

can you share the snippets of code where you "read" and "write" the data?!?!

When i send the information, its just mysql_query("insert into MyTable values ('Iñtërnâtiônàlizætiøn')");

and when i print the line, it shows correctly... but after the query runs, it only shows 'I'

Actually.... i wonder hahaha.. im gonna be sooo irked.... *thinks evil thoughts*.

anyone know if sqlyog is utf8 compliant? heh

Posted: Sat Feb 24, 2007 12:11 pm Post subject:

wolven wrote:
varchar?.. all the documentation i've been reading is char.

Saves lots of space. If you don't MySQL will reserve 3 bytes for each character (the maximum UTF-8 character length.)

What version of MySQL are you using?

Posted: Sat Feb 24, 2007 12:14 pm Post subject:

I wish i were at home right now hehe.. Its a freebsd box, and running mysql 5.something... was installed from their website like 3 months ago.

Posted: Sat Feb 24, 2007 1:01 pm Post subject:

it has to be something with the server config, because i run that on my external host, and it works fine Smile

only on the server i'm trying to use it on does it have a problem.

Posted: Sat Feb 24, 2007 2:14 pm Post subject:

wolven wrote:
it has to be something with the server config, because i run that on my external host, and it works fine

only on the server i'm trying to use it on does it have a problem.

Just curious, what charset was mysql compiled with?

Posted: Sat Feb 24, 2007 2:57 pm Post subject:

creed wrote:
wolven wrote:
it has to be something with the server config, because i run that on my external host, and it works fine

only on the server i'm trying to use it on does it have a problem.

Just curious, what charset was mysql compiled with?

whatever the binary came as hehe, i may have to recompile it to include default-char set.. i think it works with utf8, because i can make database as utf8.

Posted: Sat Feb 24, 2007 7:28 pm Post subject:

Ok, im just getting plain annoyed now. I recompiled mysql, verion 5.0.33 using the default-charset=utf8 compile option, so now show variables shows anything related to language as UTF8.

yet, when i do an import of a file ( mysql < whatever.sql ), that has UTF8 value in it ( internationalization with every letter a foreign character ), it still only imports the letter I. I've added set names and set character set both to utf8, and yet it still doesnt show right.

my next attempt will be using phpmyadmin, because i know it works for remote host.

Posted: Sat Feb 24, 2007 7:47 pm Post subject:

ok, using phpmyadmin works fine, so im stumped...

Posted: Sat Feb 24, 2007 8:45 pm Post subject:

Have you tried Navicat ? Works great. Particularly with imports like your's.

Posted: Sun Feb 25, 2007 6:49 am Post subject:

Navicat looks nice, but i dont think it would like processing a 24 million entry .sql file Smile

I figured out what was wrong, i was using a sql class 'library' for lack of a better term, called ez_sql.. the problem with it, is it always re-selected the database right before every query. The set names / etc, commands, have to be run just after selecting the database, every time, and of course the way the script was written, i had just added the commands to the connect method, not the other 25 methods Smile

All is well, andi have a totally current install of mysql and php now Wink