Wednesday, 29 September 2010

How to store UTF8 (indian language) data in mysql?

We assume we have a DataBase with table news, and a column named posts, which will save the news written in your website.We know that all major DataBase’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 DataBase otherwise you should see the hindi data. Clear? Now lets see the code given below:

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’;
this will show the below result if support:
Full Texts
Variable_name    Value
character_set_system    utf8

Now that being checked, alter the table and just modify the column, which is 'text' in our above example and specify it as UTF8

Then execute following query before inserting into the table:

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 collation_connection=utf8_general_ci');

Now, try to insert the hindi value and save it. Query it and u should see the hindi text :)



  1. great job! That means a lot to me...

  2. not working.plz send me the code to store hindi in database..

    1. please check ur code again n let me know. It would be great for me if i can help you... Thanks

    2. move_uploaded_file($_FILES["txtuplode"]["tmp_name"],$_FILES["txtuplode"]["name"]);

      $fp = fopen($_FILES["txtuplode"]["name"],'r') or die("can't open file");

      while($csv_line = fgetcsv($fp,1024)) {

      $code = trim($csv_line[0]);
      $gn = trim($csv_line[1]);
      $cin = trim($csv_line[2]);
      $cout = trim($csv_line[3]);


      if(empty($code) || empty($gn))

      $msg="Do Not upload Blank File";

      $qr= "INSERT INTO meaning set date ='$code1' , word ='$gn' , lang ='$txtcategory'";
      mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'", $link);
      mysql_query($qr)or die(mysql_error());
      //$result = mysql_query("SET NAMES utf8",$qr);

      $msg="Uploded Successfully;";


  3. this is not working. please send me the code to store hindi in database .

    1. please check ur code again n let me know. as code is working so pls send ur code what u using???

  4. hello dear,
    i'm store my information data base in English language.but i want to display that information in Hindi language when i fetch information form the data base........plz help me
    give me any suggestion plz

    1. Hi Vishal,

      In that case, you have to use Google Translate API after fetching information from database.


  5. Hi there!

    Found your article extremely helpful!! This is exactly what I was looking for. I am a newbie programmer and have learnt PHP and MySQL on my own. Currently I am developing a database management system where in I have to store data in English as well as in Marathi (Hindi or Devnagari script) in the same table with flags for the languages E for English and M for Marathi are also stored. I want to save the data in Marathi and also display it in Marathi using one of the Marathi fonts. MySQL that I am using supports UTF8. I came across your article a bit late by that time I had already designed and developed the table in MySQL and it was storing the keystrokes in the fields to be displayed in Marathi. So far so good. When I want to update the same record and bring the info on the screen to be displayed in Marathi so that it can be modified, I found out that some characters in Marathi were not displayed (which otherwise were displayed perfectly using PHP script and using Marathi font for the paragraph. The storing of Marathi and displaying of Marathi in MySQL is giving me the problem. I did change one column of my table to UTF8 just to see if the data in that column is displayed properly. But it is not happening. The above steps that you described, will the characters be set to UTF8 throughout? How do I go about it? Should I drop the table altogether and re-create it? But I do need some tables columns in English ONLY and some to hold both Marathi and English as the case may be. Can you please guide me?

    Thanks in advance!

  6. This works..but when i store data in table it removes the spacing between the words. What is the problem with this?

  7. Nice it conversion matter

  8. i am implementing one stand alone java project in that i have to store Marathi data in MySQL.... plz help..above code not work for me..

  9. I have inserted hindi values into mysql table using same trick but passed parameter values from android app... and facing same problem showing ????? instead hindi .. can you give me any trick to solve my problem? thanks

  10. hello sir My Excel file is in Hindi and my data is too much, which I want to insert in my hindi text in mysql file, how can do it with php"

  11. it was helpful for one table but when i used same thing on another database then its not working

  12. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here.
    Kindly keep blogging. If anyone wants to become a Front end developer learn from javascript and jquery training in chennai .
    or learn thru Javascript Training in Chennai.
    Nowadays JavaScript has tons of job opportunities on various vertical industry. javascript and jquery training in chennai