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

PHP and Script.aculo.us Book

http://www.packtpub.com/php-and-script-aculo-us-web-2-0-application-interface/book

Advertisements

About srinix

I am Sridhar, a matured nut, on this planet with water, air and love ;)

58 responses to “Tutorial: How to store UTF8 (indian language) data in mysql?”

  1. Pawej says :

    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

  2. Pawej says :

    Sir now its showing ?????? characters.
    after the alter query fire that problem still same.
    pawej

  3. Hi says :

    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 …

  4. pushpinder says :

    could you please send me the code too.. I am facing a similar issue and its not gettting resolved… thanks in advace…

  5. Dipan says :

    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

  6. Sridhar says :

    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

  7. ankur khare says :

    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…

  8. sachin says :

    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?????

  9. Kelnor says :

    i really donot know how to insert hindi tex in the wordpress web . please help me to find it .

  10. Monsterwar says :

    Thanks for info great article it helpmed to solve my issue related to cryllic text search

  11. Gurpreet says :

    Sri

    Can u help me.

    How can I convert BHASKAR font in UTF codes like विचार

    Thanks.

  12. Veeru says :

    How about sorting? lets say i have names in hindi in my db, how can i perform a correct sorting?

    Thanks
    Vru

  13. shantanu says :

    thanx for this post, it does helped me…..

  14. hiteshsathawane says :

    pleas show us create insure update delete query for this UTF8 encoding .

  15. Anurag Litoriya says :

    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???

  16. derek says :

    I am uable to get hindi character in my database inspite of changing database,tables,character_set collation to utf8
    pls help me!

  17. jugal says :

    data browse in mysql same as entered but not display in php self created page??????????????????

  18. gunabalans says :

    great, let me try

  19. Sunmeet Singh says :

    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.

  20. Deshvasi says :

    This is nice! Is there any impact if I change collation of MySQL database repeatedly!

  21. shriddha says :

    i tried,but hindi not display in php page,it show like ??????????

  22. Sunmeet says :

    Please anybody send me the code to display the data in punjabi language.
    Its request

  23. drpyro says :

    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”);
    ———————————————–

  24. drpyro says :

    add this to the head of ur pages

    meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″

  25. dharmesh says :

    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 says :

      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;

  26. raj says :

    hi sir
    when i use hindi boruse ,show rectangal massagee

  27. Amit says :

    hey drpyro!

    thanks man…. your comments helped me a lot…

    thanks again.

  28. jitender verma says :

    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

  29. ronald says :

    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

  30. Rose says :

    Hello Sir,
    How to retrieve tamil language from the database. Am using php/mysql. Plz sent the code to mail ID

  31. rosee says :

    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.

  32. Praveen says :

    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

  33. BhaviK says :

    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.

  34. Preeti says :

    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

  35. Girish says :

    One should add following tags in html page.

  36. Girish says :

    !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″ / >

  37. ronald says :

    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;’);

  38. ashutosh says :

    that is the vary hel full for me but i am using the vb as frontend

  39. howto-getfatfast says :

    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.

  40. Nabajyoti says :

    It helps me a lot, during storing the hindi characters in my mysql db.

    Good staff.

    Nabajyoti

  41. Shreyansh says :

    Thx a lot .. it help me

  42. े्िेो्िरत says :

    क्.ा बाे्कितेकरो्तिकरेो्तिकरेोत्किर

  43. Tanuj Dave says :

    Thank you very much ronald your code is very working for showing mysql hindi database

  44. elangovanp says :

    How getting unique code value?

  45. Kaartikeyan R says :

    Such a Amazing Help my Friend BhaviK! Thanks a Lot…! Nice Help… Thanks a Lot!

  46. durgesh pyase says :

    its working 100%

  47. anil says :

    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.

  48. Himanshu Agarwal says :

    $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 🙂

  49. deviyash says :

    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’);’

  50. मिलिंद पाटील says :

    मिलिंद पाटील

  51. Matang says :

    Thank you for such nice tutorial

  52. Sathesh says :

    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.

  53. Veeramanikandan says :

    It is working perfect. thank you very much. i own you 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: