mysql/php/phpmyadmin/utf8

kenji1903 · 03-02-2005, 03:51 AM

G'day all,
I finally got a basic LAMPS working! I've installed everything from RPMs so it wasn't that difficult

php-4.2.2
apache-2.0.40
mysql-4.1.9
phpmyadmin-2.6.0-pl3

I have been following several web tutorials on php/mysql scripting, trying to get a simple employee database running with Traditional and Simplified Chinese data.

OK, here's my problem... I think have set mostly every character coding option available to utf-8; that includes mbstrings in php.ini to character_set in mysql. I can insert data into the database but when i do a select * from my_table, i get ???!
I also tried reading the data from a php script but still get the same ??? results!

What surprises me is that everything is perfect when i view the data using phpmyadmin!

Maybe I left something out? I have been googling for days, tried most of the suggestion but so far nothing works... any help?

Cheers,
~WiLL~

mirradric · 03-02-2005, 04:53 AM

Quote:

Originally posted by kenji1903
G'day all,
I finally got a basic LAMPS working! I've installed everything from RPMs so it wasn't that difficult
php-4.2.2
apache-2.0.40
mysql-4.1.9
phpmyadmin-2.6.0-pl3

I have been following several web tutorials on php/mysql scripting, trying to get a simple employee database running with Traditional and Simplified Chinese data.

OK, here's my problem... I think have set mostly every character coding option available to utf-8; that includes mbstrings in php.ini to character_set in mysql. I can insert data into the database but when i do a select * from my_table, i get ???!
I also tried reading the data from a php script but still get the same ??? results!

What surprises me is that everything is perfect when i view the data using phpmyadmin!

Maybe I left something out? I have been googling for days, tried most of the suggestion but so far nothing works... any help?

Cheers,
~WiLL~

Does your page display correctly if you set your browser encoding to utf 8? I had a problem like this before because I forgot to send the header for the encoding/charset. Apache was sending the default ISO-8859-1 and the browsers were trying to display it as such. Could this be the case?

kenji1903 · 03-02-2005, 05:58 PM

Thank you for your reply, mirradric!

Do you mean this line?

Code:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

Included this line in all my test scripts

Doesn't work, I tried selecting it manually as well... I have also set AddDefaultCharSet in httpd.conf to utf-8.

Another thing is, I am doing the SELECT and INSERT both from the web server (mozilla 1.6) and from an XP client (IE6).
But I wonder why i can view the data from my web server using phpMyAdmin?

Thanks again for replying!
~WiLL~

kenji1903 · 03-03-2005, 01:36 AM

Well... i tried running this

Code:

LANG=zh_CN.GB18030 gnome-terminal --disable-factory

and a chinese terminal popped up, i logged into mysql but i am still reading ??? from the tables!
What bothers me is that i can read chinese files and directories but not the data stored in mysql tables...

I installed mysql from an RPM package, is it better to install it from source? I reckon there's a missing string or something...

kenji1903 · 03-05-2005, 02:38 AM

The mysql forums ain't much help...

kenji1903 · 03-05-2005, 03:09 AM

Oh Sh*t! I got it!

Code:

1     $link = mysql_connect($host, $usr, $pwd);
2     mysql_select_db($db);
3     if (mysql_error()) { print "Database ERROR: " . mysql_error(); }
4     mysql_query("SET CHARACTER SET utf8", $link);
5     mysql_query("SET NAMES 'utf8'", $link);

I added lines 4 and 5 my php script and now I can INSERT and SELECT chinese characters from my database!
I'm going to try with more languages, will be back with more info/queries!

It's weird that mysql keeps resetting the character set to latin1 whenever i logoff and log into the mysql server...

only problem left now is reading the data from mysql prompt in gnome-terminal... for some reason redhat cannot display utf8 properly, I know FC2 can...

jasonlam852 · 09-03-2005, 06:36 AM

i got the same problem on MySQL- 4.1.14,

i have just installed the server and MySQL by rpm, but the default charset in rpm version is latin

the only way to do is call set names after every connection

[ i was unable to do this in my.cnf too!!
init_connect="SET NAMES utf8"; i copy this line from one of the thread in this forum
default-collation=utf8_general_ci
character_set=utf8
character_set_client=utf8
character_set_server=utf8
collation_database=utf8_general_ci
**all/any singal one above cause errors, and can't start mysqld
]

i even tried to reinstall by source, but was not able to clean the current rpm installation clearly [with error: It's ready to run, you don't need to configure it! ]

details as follow,
commands entered (nothing find by rpm -qa | grep mysql // nor rpm -q mysql, so i can't use rpm -e $(rpm -qa |grep mysql))
>rpm -e MySQL-server
>rpm -e MySQL-client
>rpm -e MySQL-devel
>rpm -e MySQL-share
>CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors \
-fno-exceptions -fno-rtti" ./configure \
--prefix=/usr/local/mysql --enable-assembler \
--with-mysqld-ldflags=-all-static --with-charset=utf8

-----------------------------------------------------------------------
NOTE: This is a MySQL binary distribution. It's ready to run, you don't
need to configure it!

To help you a bit, I am now going to create the needed MySQL databases
and start the MySQL server for you. If you run into any trouble, please
consult the MySQL manual, that you can find in the Docs directory.

Installing all prepared tables
Fill help tables

To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h server.community.hk password 'new-password'
See the manual for more instructions.

NOTE: If you are upgrading from a MySQL <= 3.22.10 you should run
the ./bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
cd sql-bench ; perl run-all-tests

Please report any problems with the ./bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
Starting the mysqld server. You can test that it is up and running
with the command:
./bin/mysqladmin version
Starting mysqld daemon with databases from /usr/local/mysql/data
[root@server mysql]# STOPPING server from pid file /usr/local/mysql/data/server.community.hk.pid
050902 23:56:04 mysqld ended
-----------------------------------------------------------------------------

it seems that i didn't clean the MySQL rpm installation clearly by rpm -e
what should i do before using ./configure ? or
how should it configure and make to ingore current installation?