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
Sridhar: Web Unplugged!!!! 
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…
sure he will send the code … you send him your salary 😀
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”);
———————————————–
Thank u so much !!!
Its working 100% 🙂
Thanks you very much its working 100% …
Thanks for your help…:)
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.
I setup all the fields to utf8_general_ci and i also tried with varbinary it store all language fine
but when I insert from php its shows different
Hey drpyro,
your solution resolved my problem.
Thanks a lot.
This will definitely solve the problem guys.
===============
$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’);
============
write the above code after connection code.
hiii
i am making my project in punjabi language in php & mysql. i already set characterset utf8. my values are appearing in database . but it did not appear in page.
when i echo the value it just print ?????. so plz help me to print the values in a page and send the code to print the pujabi values from mysql to php page.
thanx
One should add following tags in html page.
!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
html xmlns=”http://www.w3.org/1999/xhtml”>
head
meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″ / >
Maybe it is a bit superfluous, but I also add 3 collation statements (better safe than sorry), as follows:
mysql_query(‘SET collation_server=utf8_general_ci’);
mysql_query(‘SET collation_connection=utf8_general_ci’);
mysql_query(‘SET collation_database=utf8_general_ci’);
mysql_query(‘SET character_set_server = utf8’);
mysql_query(‘SET character_set_database = utf8’);
mysql_query(‘SET character_set_results = utf8’);
mysql_query(‘SET character_set_client = utf8’);
mysql_query(‘SET character_set_connection=utf8’);
mysql_query(‘SET names utf8;’);
mysql_query(‘SET character_set utf8;’);
that is the vary hel full for me but i am using the vb as frontend
Hi there! this is the perfect show case of a good wordpress theme am I right? seems all so organized that it even makes me think its more than perfect! keep it going.
It helps me a lot, during storing the hindi characters in my mysql db.
Good staff.
Nabajyoti
Thx a lot .. it help me
क्.ा बाे्कितेकरो्तिकरेो्तिकरेोत्किर
Thank you very much ronald your code is very working for showing mysql hindi database
How getting unique code value?
Such a Amazing Help my Friend BhaviK! Thanks a Lot…! Nice Help… Thanks a Lot!
its working 100%
Thank You all Of you my friend who is sharing their knowledge here ,i really happy and i wish you heartly god bless you ..
thank you……..
Anil Patel.
$mysqlConnect = mysql_connect(‘localhost’, ‘root’, ”);
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’);
This is perfextly working 🙂
Thank you! Here is another tutorial about UTF8:
http://web-o-blog.blogspot.com/2011/04/utf-8-with-phpmysql.html
hi
i am facing bug when i post link on facebook it looks like this
à¤à¤²à¥à¤«à¤¾ नॠà¤à¤¾à¤°à¤¤ सॠठलठहà¥à¤¨à¥ à¤à¥ माà¤à¤ à¤à¥à¤¡à¤¼à¥
what i code in wp-config
‘/** Database Charset to use in creating database tables. */
define(‘DB_CHARSET’, ‘utf8’);
/** The Database Collate type. Don’t change this if in doubt. */
define(‘DB_COLLATE’, ‘utf8_general_ci’);’
मिलिंद पाटील
Thank you for such nice tutorial
Hi,
The encoding differs when we export the database as a whole and export individual tables. When I export it as a database as a whole, I’m getting something like this ‡Æâ‡Æ±‡Øç‡Æ±‡Ææ‡Æ∞ in the fields instead of indian language character. If I export the individual table, everything is fine. Any help is really appreciated. Thank you.
It is working perfect. thank you very much. i own you 🙂