Multi-Language Support in MYSQL

June 10, 2009 at 9:28 am (MYSQL)

This article is about how to store and manipulate multi languages in mysql table. May be useful while developing globalization / locale support enabled websites

By default mysql supports many european languages, Since unicode character(UTF-8) support implemented in mysql it allows us to store many of the indian (Asian) languages.

Mysql supports Gujrathi, Hindi, Telugu and TAMIL among too many languages in the subcondinent
Let’s consider TAMIL language and workout:-

To store & search tamil character sets in MySQL table, first of all we need to create a table with character set UTF-8.

CREATE TABLE multi_language
(
id INTEGER NOT NULL AUTO_INCREMENT,
language VARCHAR(30),
characters TEXT,
PRIMARY KEY(id)
) ENGINE=INNODB CHARACTER SET = utf8;

INSERT INTO multi_language VALUES (NULL, ‘English’, ‘abcdefghijklmnopqsrtuvwxyz’);
INSERT INTO multi_language VALUES (NULL, ‘Arabic’, ‘ﺃ‎ﺏﺝﺩ‎ﻫﻭﺯﺡﻁﻱﻙﻝ‎ﻡﻥ’);
INSERT INTO multi_language VALUES (NULL, ‘Arabic’, ‘ﺃ‎ﺏﺝﺩ‎ﻫﻭﺯﺡﻁﻱﻙﻝ‎ﻡ ﻥ’);
INSERT INTO multi_language VALUES (NULL, ‘Hindi’, ‘ਓਊਨਣਥਨਫ’);
INSERT INTO multi_language VALUES (NULL, ‘Thai’, ‘ЁώύЂЬЫЗЪШДГЦШГЕ’);

INSERT INTO multi_language VALUES (NULL, ‘Telugu’, ‘ని మీ హొమ్ పేజిగా అమర్చుకోండి’);
INSERT INTO multi_language VALUES (NULL, ‘Tamil’, ‘இந்தியா நாட்டின் பக்கங்கள்’);
INSERT INTO multi_language VALUES (NULL, ‘Arabic’, ‘البحث في الصفحات العربية ‘);
INSERT INTO multi_language VALUES (NULL, ‘Korean’, ‘시작페이지로 하세요 채용정보 광고 프로그램 정보’);

Command to Change the client character set (which sends request to the server):

this has to be done so that the server can understand the request which send by Client.

— To be executed at Client Side
 SET NAMES ‘utf8’;

— System Variable Name : character_set_client

— To Set Locale time zone name
SET @@lc_time_names = ‘en_US’;

For Tamil language, we need to set the time zone as follows..

SET @@lc_time_names = ‘ta_IN’;

Now, we use select query and check out the result.
Important :
————–
 1. If the result shows like the ???? then prpoerly in your system (windows XP) need to install the  extral language support tool by enabling the following options,

 Control Panel -> Regional and Language Option ->  Languages -> Install files for complex script and right-to-left languages (including Thai) 

 2. While fecthing the row using PHP, for displaying the Multilanguage content properly, you must need to include the Meta tag like,

 <META HTTP-EQUIV=”Content-Type” CONTENT=”text/html; charset=utf-8″>
 if needed the add mysql_query(‘SET character_set_results=utf8’) in the php code before fecthing the reocrd.