Encoding database and server

Question

I am importing data from outside into my database MYSQL using PHP Scripts. Encoding my database charset to utf8 from the query

ALTER DATABASE DEFAULT CHARSET 'utf8';

then i executed a query to see all charsets by

SHOW VARIABLES LIKE 'character_set%';

output is:

+--------------------------+----------------------------+
| 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     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

as we can see character_set_database is set to utf8 but still if i write a code from my PHP Script to see the encoding as

echo $charset = mysql_client_encoding($cn);

the output is latin1 . From the above query latin1 is for server only. Can anyone tell me what exactly i am missing as i am unable to encode my Chinese and Japaneses characters to database.

EDIT

I am importing a database from outside which have unicode characters as 我的上网主页 and 嶏紞鎴戠殑 in Chinese , Japaneses and other different languages. But when i import data to my database tables i get ????? instead of above characters. How can i encode these characters? Is it utf-8 or 16 and how i can recognize that which encoding will support these characters?

Answer 1

character_set_database just refers to the default character set of any created tables in that DB, I think. Therefore, having it set to UTF8 won't help, I suggest the following:

Every time I initialize my database connection, I execute $db->query("SET NAMES 'utf8'");

Talks about SET NAMES https://stackoverflow.com/a/1650834/1221902

More on set names for the critics

It will depend on your MySQL/PHP version for the availability of the appropriate function that would be a better alternative to the "SET NAMES 'utf8'" query.

A SET NAMES 'x' statement is equivalent to these three statements:

SET character_set_client = x; SET character_set_results = x; SET character_set_connection = x;

From http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html MySQL 5.1 (alot of people still use 5.1)

The character_set_results system variable indicates the character set in which the server returns query results to the client. This includes result data such as column values, and result metadata such as column names.

Answer 2

You are missing client encoding.
While above variables are just server-side ones, you have to set up client encoding using

mysql_set_charset()

As you stated that you are using obsolete PHP version, the only option you have (beside upgrading PHP/switching drivers) is SET NAMES <actual data encoding> set names query.

As it turned out, your problem not in the setting connection encoding but with some mysterious "importing". As you provide no details, I can only guess.
If you are importing some mysql dump - check table definitions. it is very likely that charset may be wrong there. You can simple change it with search&replace.