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
ALTER TABLE news MODIFY text VARCHAR(200) CHARACTER SET 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 :)


 Cheers!

20 comments:

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

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

    asjad03@gmail.com.

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

      Delete
    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]);

      $code1=strtotime($code);
      $code1=date("Y-m-d",$code1);

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

      $msg="Do Not upload Blank File";
      $_SESSION['errors']=$msg;
      header("location:manage_meaning.php");
      }
      else
      {


      $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;";
      $_SESSION['content']=$msg;
      header("location:manage_meaning.php");
      exit;

      }

      Delete
    3. ખોટીના છે આ લોકો મારા પ્રોજેક્ટ માં હાલતું જ નથી

      Delete
  3. this is not working. please send me the code to store hindi in database .
    surbhichadha305@yahoo.com

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

      Delete
  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

    ReplyDelete
    Replies
    1. Hi Vishal,

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


      Thanks
      Ashwani

      Delete
  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!

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

    ReplyDelete
  7. Nice it conversion matter

    ReplyDelete
  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..

    ReplyDelete
  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

    ReplyDelete
  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"

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

    ReplyDelete

  12. Wonderful blog & good post.Its really helpful for me, awaiting for more new post. Keep Blogging!
    Hire PHP Mysql Developers in India

    ReplyDelete