Tutorial: How to store UTF8 (indian language) data in mysql?
Well, let me put it straight this tutorial also applies to any other native languages like latin, spanish etc. But, since I have tried for Indian languages I am writing exclusive for it.
Okay, so before we say code I must tell you this tutorial intends, a thought provoking stuff 
Lately, we have seen enough number of Indic-translators, and you must have got your hands on with typing in ur mail/blog. Now, just think for a while, when u sent a mail, and try to read it back from “Sent Mail”, u still see the indian language or even in that case blog articles. For that case, even in Orkut.
How are they stored inside database? Was what I was trying.
Again, I am not sure for any other DB, I have tried with MySQL. But, I guess should be same.
Quick Explanation:
We assume we have a DB with table articles, and a column named posts, which will save the article written in your blog. Best part, we know that all major DB’s support UTF8. And we shall explore that feature.
Now we write a article in hindi, हेल्लो वर्ल्ड
If the UTF8 is not specified, you should see something like ?????? in ur DB else u shud see the hindi data. Clear? Koool lets see the code
Quick Code:
First check for UTF8 compatibility with this query. If it supports you should see the output as “Character_set_system”| “UTF8″
SHOW VARIABLES LIKE ‘character_set_system’;
Now that being checked, alter the table and just modify the column, Posts in our above example and specify it as UTF8
ALTER TABLE articles MODIFY Posts VARCHAR(20) CHARACTER SET UTF8;
Now, try to insert the hindi value and save it. Query it and u shud see the hindi text
We have go ahead and use many other options as well, like Collate etc. But this tutorial intends to keep it simple.
I hope you find this useful and interesting. If you have tried any hacks similar to this, pls drop in comments and share with us.
Cheerz
Sridhar
If you are reading article, you might also be interested in the PHP and Scriptaculous Book I have authored.
The book gives you insights about effects, drag-n-drop, slideshows, applications, auto-completion, in-place editing and more. Complete code snippets and explanations.
CHECK out and BUY the PHP and Script.aculo.us book at Packt official site
PHP and Script.aculo.us Book
http://www.packtpub.com/php-and-script-aculo-us-web-2-0-application-interface/book
32 comments so far
Leave a reply
Sir,
I do same thing as per your direction for store the hindi language in mysql.
but still now when i insert the hindi text from copy to word into database insert query,it shows the english fonts and after the select query same english fonts is shown.
please sir help me
Thanks and Regards
Pawej
Sir now its showing ?????? characters.
after the alter query fire that problem still same.
pawej
its not showing sir …
could you please send me the code that you have written …
i am working it on with php
like we would have to write something in the webpage also …
could you please send me the code too.. I am facing a similar issue and its not gettting resolved… thanks in advace…
hi
If i want to send email in content and that content would be in hindi, then how can i do so?
Avidly waiting for your reply.
regards
Dipan
Dipan,
Since it is using UNICODE, it will be displayed same in email/text as well.
If u can be lil more clear abt requirement, may b I can help u.
Cheerz
Shri
Hi
I store hindi data(in mangle font provided by Win-XP) in MYSQL database. I use the table field collection is UTF 8 . when i store data in table it shown in hindi in database table . but when i fatch hindi data in the php form it show like ????????? . pls help me….. it very argent…
Sir,
I do same thing as per your direction for store the hindi language in mysql.
i want 2 make my database generic so that if a user wants tto insert any sort of data he can..
can u help me soring this issue?????
i really donot know how to insert hindi tex in the wordpress web . please help me to find it .
Thanks for info great article it helpmed to solve my issue related to cryllic text search
Sri
Can u help me.
How can I convert BHASKAR font in UTF codes like विचार
Thanks.
How about sorting? lets say i have names in hindi in my db, how can i perform a correct sorting?
Thanks
Vru
thanx for this post, it does helped me…..
pleas show us create insure update delete query for this UTF8 encoding .
Respected Sir,
I am using this code to fire the value from mysql5 in hindi I already set charset in mysql as you said earlier ALTER TABLE articles MODIFY Posts VARCHAR(20) CHARACTER SET UTF8;
<?php
$con = mysql_connect(“localhost”,”root”,”");
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}
mysql_select_db(“test”, $con);
$result = mysql_query(“SELECT * FROM text”);
while($row = mysql_fetch_array($result))
{
echo $row['test']. $row['post'] ;
echo “”;
}
mysql_close($con);
?>
but code is still not showing values in Hindi. Plz help me what shoud I do???
I am uable to get hindi character in my database inspite of changing database,tables,character_set collation to utf8
pls help me!
data browse in mysql same as entered but not display in php self created page??????????????????
great, let me try
i am making my project in punjabi language. i already set characterset utf8. my values are appearing in database . but it did not appear in page.
$query_list2 = “select id, new1 from new”;
$b->query($query_list2);
$row = $b->result->fetch_array(MYSQLI_BOTH);
echo $row['new1'];
i just put this code in php page. this code is written in php5. so don;t worry abt $b . wen i echo the value it just print ?????. so plz help me to print the values in a page.
This is nice! Is there any impact if I change collation of MySQL database repeatedly!
i tried,but hindi not display in php page,it show like ??????????
Please anybody send me the code to display the data in punjabi language.
Its request
you should set the PHP database client to provide and extract stored data in the correct format. This should be done right after calling myqsl_connect() function:
———————————————
$mysql_link = mysql_connect($host, $username, $password);
mysql_select_db($database, $mysql_link); mysql_query(‘SET character_set_results=utf8′);
mysql_query(‘SET names=utf8′);
mysql_query(‘SET character_set_client=utf8′);
mysql_query(‘SET character_set_connection=utf8′);
mysql_query(‘SET character_set_results=utf8′);
mysql_query(‘SET collation_connection=utf8_general_ci’);
———————————————–
also, you should add this META tag to your page to inform the browser that your page is UTF-8 encoded:
———————————————–
Title
———————————————–
It is good also to add HTTP header through PHP header() function:
———————————————–
header(“Content-Type:text/html; charset=utf-8″);
———————————————–
add this to the head of ur pages
meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″
sir,
i have done all the prerequisite for utf8 encoding i.e. included utf8 in my html code(via meta), php page(via header) and created database with charset utf8 and collation utf8_unicode_ci
But the hindi text from text area on html page submitting to Mysql 4.1.11 via PHP 5.0.4 (LAMP platform) is showing ???? in table on select query…..
please help and tell where i am wrong or what else i have to do???
kapil dhingra
use this code it will definitely work.
and save the file in utf-8 format not in ANSI format.
before this alter the table columm in with punjabi or hindi data is store using query:
Alter table table_name modify columm datatype(e.g varchar) CHARACTER SET UTF8;
hi sir
when i use hindi boruse ,show rectangal massagee
hey drpyro!
thanks man…. your comments helped me a lot…
thanks again.
hi i got ?????? when I fetch hindi data from access databse.it is correctly appeare in database but in hmtl it dislplay ????????????…plz help
thanxxxx a lot
I have setup everything as instructed, including meta tag and php script instructions
The MySQL instructions:
SHOW VARIABLES LIKE ‘%colla%’;
SHOW VARIABLES LIKE ‘%char%’;
all show either collations ‘utf8_general_ci’ and ‘utf8′.
I can store and display many languages (see my home site start page) including Gujarati, Tamil and Punjabi, but I am not able to handle the Hindi text you’ve shown in your tutorial.
Like raj (on May 9, 2009) all I see are squares.
Please advise, I might miss something here.
Regards – Ronald
Hello Sir,
How to retrieve tamil language from the database. Am using php/mysql. Plz sent the code to mail ID
I Hav a trouble in tamil font so any one can help me in storing the Tamil font in phpMySQL.
When i copy a tamil text from the browser it is displaying, but when i type in the MSword and copy them to store in MySQL TABLE. It is showing only the keyboard english characters.
So plz guide me sir and mail the answer to my ID.