MySQL

To display utf-8 characters on web page correctly do the following:

 

1. #vim /etc/my.cnf

Add lines:

character-set-server = utf8
collation-server = utf8_unicode_ci
skip-character-set-client-handshake

So the complete my.cnf file will look like:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
character-set-server = utf8
collation-server = utf8_unicode_ci
skip-character-set-client-handshake

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


[mysql]
default-character-set=utf8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

 

2. Alter db and tables into utf-8

> ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
> ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; 

 

3. Put into php file:

$linkID=mysql_connect("$db_host","$db_user","$db_pass") or die('MYSQL_CONNECT ERROR: '.mysql_error());
mysql_select_db($db_name2, $linkID) or die('MYSQL_SELECT_DB ERROR: '.mysql_error());

$u_rub=mysql_db_query("$db_name3", "SELECT * FROM rubrike");
mysql_query("SET NAMES 'utf8' COLLATE 'utf8_general_ci' ", $linkID) or die('SET NAMES ERROR: '.mysql_error());

 

 

4. define utf8 in HTML

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

 

 

 

 

Check with:

> SHOW VARIABLES LIKE 'character_set%';

 

Output should be:

character_set_client     utf8
character_set_connection     utf8
character_set_database     utf8
character_set_filesystem     binary
character_set_results     utf8
character_set_server     utf8
character_set_system     utf8
character_sets_dir     /usr/share/mysql/charsets/