How to fix LAMP UTF8 encoding?

Question

I set all about encoding to utf8. but browser show me '???????'. My settings are below.

MySQL "show variables like 'c%'"

character_set_client     | utf8
character_set_connection | utf8
character_set_database   | utf8
.... (all utf8)

MySQL my.cnf

[client]
default-character-set = utf8
[mysqld]
init_connect="SET collation_connection = utf8_general_ci"
init_connect="SET names utf8"
character-set-server = utf8
collation-server = utf8_general_ci
[mysql]
default-character-set = utf8

In all my PHP file included:

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

Apache2 httpd.conf

AddDefaultCharset Off (if utf-8, show me '???' ,too)

In MySQL console, (database is dumped)

SELECT name from campagin LIMIT 1
    => print in working order, not '????'

I don't know what is problem.

For reference, I test this case.. in test.php

<?php
    mysql_connect('localhost','root',ROOT_PW);
    mysql_select_db(TEST_DB_NAME);
    mysql_query("INSERT INTO a SET msg='안녕하세요'"); // char is KOREAN, meaning 'Hi'
    $res = mysql_query("SELECT msg FROM a LIMIT 1");
    $row = mysql_fetch_assoc($res);
    echo $row['msg'];
?>
    => this print '안녕하세요' in working order not '????'

but in MySQL console

 "SELECT msg FROM a LIMIT 1"
     => print not '안녕하세요' but abnormally 'ì•ˆë…•í•˜ì„¸ìš”'

ADDED : in mysql console

"SHOW CREATE TABLE a"
    => Table | Create Table
       a     | CREATE TABLE `a` ( 'msg' varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8

a) is the file itself saved in UTF8 (probably is, since you can see the korean), and b) when you set up the database connection in PHP, do you remember to set NAMES and CHARSET to UTF8 before running any queries (doesn't look like you are?) — Mike 'Pomax' Kamermans, Dec 06 '13 at 22:32
It looks like your `campagin` table is fine, but your table `a` is not. Have you checked to ensure your databases, tables and columns are all set to be UTF-8? — halfer, Dec 06 '13 at 22:32
(+1 for one of the clearest questions in the PHP tag for several weeks)! — halfer, Dec 06 '13 at 22:34
Can you show the output of "SHOW CREATE TABLE " for all the relevant tables, too? — Mike 'Pomax' Kamermans, Dec 06 '13 at 22:34
@Mike'Pomax'Kamermans // a) yes! all file is saved for UTF-8 and b) already included in mysql config file(my.cnf). init_connect='SET NAMES utf8', so I think in php code don't need to query that. In mysql console, "SHOW CREATE TABLE a" => CREATE Table `a` ('msg' varchar(255) DEFAULT NULL) ENGIN=InnoDB DEFAULT CHARSET=utf8 — myggul, Dec 06 '13 at 23:07
please put the "SHOW CREATE" output in your post, so it's nicely formatted and readable for everyone =) — Mike 'Pomax' Kamermans, Dec 06 '13 at 23:08
@halfer // table a is same configuration with campaign table. if Fetch from campaign table in a php file, same print '??????' but in mysql console "SELECT * FROM campaign" print in working order ex) '안녕하세요'. — myggul, Dec 06 '13 at 23:09
Does the string literal `안녕하세요` work correctly in PHP? I'd suspect that's the problem since PHP doesn't support multibyte characters by default. — Waleed Khan, Dec 06 '13 at 23:10
my.cnf only applies to mysql itself, not to the DB connection from php to mysql. I've been bitten by that several times, and have adopted the practice of always issuing `$dbh = new PDO(...); $dbh->query("SET NAMES 'utf8'"); $dbh->query("SET CHARACTER SET 'utf8'");` before doing real work. — Mike 'Pomax' Kamermans, Dec 06 '13 at 23:10
@Mike'Pomax'Kamermans // oh, yeah!!!!!~~! solved!! after i include "mysql_query("SET NAMES utf8");mysql_query("SET CHARACTER SET 'utf8'");". — myggul, Dec 06 '13 at 23:18
certainly close enough. marking as duplicate, but it sounds like it's also already solved now =) — Mike 'Pomax' Kamermans, Dec 06 '13 at 23:22
All Thanks!!!! But i have a question more. In webservice hosted by company server, work well in spite of not including that codes ,"mysql_query("SET NAMES utf8");mysql_query("SET CHARACTER SET 'utf8'");".. hmm. why work well in hosting server? — myggul, Dec 06 '13 at 23:26
Also, note that this database library is deprecated, and you should switch to PDO or MySQLi. — halfer, Dec 06 '13 at 23:51

score 1 · Answer 1 · answered Dec 06 '13 at 23:28

Using the base mysql_ connector you will need to declare

mysql_query( "SET NAMES utf8" );
mysql_query( "SET CHARACTER SET utf8" );

These tell MySQL that your communications with it are going to be via UTF8, otherwise it may not work.

Further more you need to make certain your tables and their columns are defined UTF-8, MySQL handles encoding at a column level. if you SHOW CREATE TABLE campagin what do you get for encoding on the table and columns? If a column doesn't spit out an encoding, it will default to the table level encoding.

How to fix LAMP UTF8 encoding?

1 Answers1