Oct 13, 2008 - MySQL, PHP    153 Comments

PHP – MySQL: Unicode solution to Chinese, Russian or any language

Hey Guys,

I am a Freelance Web Developer and my main tools are PHP & MySQL. Few days ago, I got a Chinese project where I had to develop a Real Estate site in Chinese language. You know we often build websites in English and Databases are in English too. So, the default configuration in MySQL works fine everytime.

But when it comes a language other than English, many people do not know what to do. Well. When I started the project, I did not even know that the default MySQL settings will not work for the Chinese language. So, I started searching for a stable solution where my program will support any language for adding, updating and searching data from the MySQL database.

And Yeah.
I found it!

OK.

Let us see the solution now.
It is very very simple.

Step One: SET THE CHARSET TO UTF-8 IN THE HEAD SECTION

First of all, the browser needs to know that you are going to display or use Unicode in this page. So, go to your <HEAD></HEAD> section and set the charset to utf-8. So, the browser will be able to show the Unicode text without any error and smoothly. You can also copy and paste the line below:

Step Two: CREATING THE DATABASE

When you create your (a) Database and (b) any Table in the database, set the Collation of both of them to utf8_unicode_ci and you know it is very easy if you are using phpMyAdmin.

Step Three: DATABASE INITIALIZATION

When you initialize the database connection, please add the “extra lines”

But why are you adding the extra lines? Because you are letting the database know what kind of input you are going to work with soon.

Step Four: INSERTING INPUTS/DATA IN THE DATABASE

Why are you adding the first two lines for? Because the database should know what kind of data is going to be stored.

Step Five: UPDATING INPUTS/DATA IN THE DATABASE

So, you are adding the extra two lines before you run your query string as you are playing with Unicode.

Step Six: SEARCHING DATA FROM THE DATABASE

Adding the one extra line every time you search your Unicode data is enough.

OKKK.
You are done. This should work smoothly for handling your data in any language does not matter it is Bangla (my mother tongue), Hindi, Chinese, French, German, Spanish, Russian, Arabian (Arabic), Urdu, or any other language.

And do not forget to leave a comment if you have any. Because I need to update the post in case required.

Thanks for reading and please check if it works for you.

153 Comments

  • Very usefull!! Thank You!

  • hai sir,
    u r great. ur tutorial is nice and simple.
    can u make such a simple example in using ajax. im new to programming and using php and mysql. but ajax is a hard nut to crack
    tanx.

  • @ Andrews

    thank you for your comment. ajax is simple. you can start learning ajax from here: http://www.w3schools.com/ajax/default.asp

  • You are my hero!!! Thanks so much. We are creating a multi-language website and I’ve struggled so much with all these languagages and character encodings.

  • It works! Many thanks for your help and saving me many hours of headache.

    Inputing foreign characters works fine. However, the retrieving part of the foreign data displays only in ??? question marks. Strangely enough retrieving foreign characters displays correctly in my old non optimized older version. Any idea?

    Thanks again Adi

  • @ adi

    i am not sure. but I faced ?? when I missed “Step One: SET THE CHARSET TO UTF-8 IN THE HEAD SECTION”

    May be you are missing somewhere. You can try from first to last without avoiding any step.

  • hi sir,
    I was successful to inserting chinese character to database but
    I have a problem when I want to display chinese character.
    Please help me sir..

    this is my code:
    ————————————————————————————-
    mysql_query(“SET character_set_results=utf8”, $con);
    $sql_query = “SELECT * FROM user where email=’$user’ and password=’$pass’;”;
    $query = mysql_query($sql_query, $con);

    $result = mysql_fetch_array($query);
    $row = mysql_num_rows($query);

    if(($user == “”) && ($pass == “”))
    {
    print “e-mail and password empty !!!”;
    exit;
    }
    if($row != 0)
    {
    if($pass != $result[‘password’])
    {
    print “Password salah !”;
    }
    else
    {
    setcookie(“username”,$user);
    print “Welcome “.$result[‘First_Name’].” “.$result[‘Mid_Name’].” “.$result[‘Last_Name’].”
    Klik To HomePage“;
    }
    ——————————————————————————————-

  • @eko

    Well. I originally wrote this code for Chinese language. So, it surely works as I tested too.

    I think you are missing one or more steps out of all the steps.

  • I just wanted to say thankyou. I have been having no end of trouble with PHP, SQL and Oriental Character Sets. This solved it, and for PDO’s too! Brilliant 🙂

  • thank you buddy….

  • so many thanks…. may god bless you…

  • Oh my God , I have to say 非常谢谢您。 I found this one for two days , bu I finaly got Good Answer . My I could buy a soda for you , thank you alot

  • you are toooo good dude…. but one thing is we have to first translate in google then copy paste in our editor… is this any thing that like google transalte editor….?

    pls reply on this…. waiting for ur reply…. 🙂

  • @ Sourabh

    I do not know. please use Google to find the answer. 🙂

  • Hi,
    i followed the steps provided by you and getting the data correctly in Phpmyadmin like (我的秋天) , but not able to see the same result while connecting from SqlYog , it comes like block .
    please advice how can i see the correct data .

    Also, when i had collation utf8_general_ci the characters were like 我的秋天 , is there any way to change the it so it will become the same when we use utf8_unicode_ci ??
    Please help .

  • @ shishir,

    please forget whatever SqlYog shows. SqlYog might not be compatible with unicode.

    just make sure if you get the data properly in your output page.

  • Dear,

    i am not using two lines when database select.

    mb_language(‘uni’);
    mb_internal_encoding(‘UTF-8’);

    and every things use according your guidelines. Every things working fine but PDF(use TCPDF) in Hindi is not working . Other languages (PDF) are working fine. can you hepl me?

    Thanks
    Anil

  • I didn’t test your code because i want to insert my data in urdu format in database. My question is that how can i make entries into database because i don’t know how the the text can be written in urdu language in a text field or a text area so that it could be inserted in database. Please help me. Thanks.

  • Thank you sooo much. I have gadgets in different languages to display my artwork. I tried many codes, including utf-8…but nothing really worked until I tried yours.
    Great tutorial! Very well explained too.

    Thank you again.
    Carmen

  • Thanks bro :). This scripts are very helpful.

  • that is amazing. thank you for your help

  • need php code convert english to kannada

  • you saved me i almost give up i searched for like 3 hours god dammmm

  • Hi,

    I have a database containing chinese characters and i would like to implement an improved search function where the chinese characters (in both the html form and in the database) would be converted to han yu pin yin first before being compared so i can get words with same han yu pin yin (without the accents)

    Any advice?

    Thanks

  • Really wonderful article 😀

  • Thank you alot, You saved my time for this issue, I’m building a multi languages web site with MySQL database, your way working with me perfectly, I can now insert diffirent languages word in one feild without any mistakes, I should to thank you for your easy way for explaining.

  • Wow! Work perfect!
    I still need some solution for some forenger text that i take from a php variables.

    But this work grate for the data in the database!

    Thank You!

  • Great post. You saved me hours!
    You’re a great teacher.
    Thanks!

  • Thanx man, you’re a superstar. I’ve been struggling on it for ages…

  • The below line put an end to my hours-long worry and hopeless effort

    mysql_query(“SET character_set_results=utf8”, $dbLink);

    not enough words to thank you 🙂

  • Hi….
    I am begineer in php. I want to make simple html form that take input in nepali by defult, I have made all database and php code that supports utf8 described as above but I feel prb in form…plz help me how can I write nepali in my html form

  • Just an awesome verbosely explained post! Figured out I had some mis-conceptions about some of the stages 🙂

  • I was having issue where my database showed the correct Japanese text but when viewed in browser, it would be in ????.

    I added the lines in step 3 and it works. In my case, it would of worked if I only added the last line which states to set names to utf8. I added all just in case. Thanks.

  • Great !

  • Thanks dude for the tip. I have had issue on an off with different sites. This tip don’t all them right.

  • You are a genius! I’ve been struggling with this problem for ages and 10 minutes to implement this and I’ve got it loading my mySQL tables with Chinese!

  • And one more thing, my code was using the sqli_xxx versions of the functions so I put it into that format. Worked fine.

  • Thank you so much…………
    God is Great.

  • I having the russsian languages in data in mysql.How i can convert the russian language content to english in mysql

    If any one know mail me..

  • i have an urgent proble…please solve

    insert urdu data into mysql database . i change my database collassions to utf8_bin, utf_unicode_ci but it is insert data into another format

  • Information is quiet impressive to me, i think this could be more impressive as compared to other information. Well thanks.

  • Hey It Is Outputting :-
    Call to undefined function mb_language() in E:\Apache\htdocs\databse.php
    What Does It Mean
    I am using Apache 2.2.4
    Php 5.4
    Mysql 5.2

  • Found It :-

    extension=php_mbstring.dll
    Was Commented ;

  • But It Is storing
    लललललल
    instead of :-
    लललललल in Database ……

  • Thnx Dude It is gr8 For Storing Result !!
    But
    When Using Search Then It Is Dispalying :-
    Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ‘like’

    my search query is :-
    Select * from search_tbl Where name like ‘$ip’ ;
    where $ip=$_GET[‘search_term’];

  • Thanks a lot for post and Thanks Smokey I have the same issue with the htmlentities.

    Great !!!

  • Thank you so much.Really a gud job

  • Dear,

    I hired a guy to develop a blogging site with ‘Bangla typing’ & ‘Bangla search’ support.. But looks like he is struggling.. Unfortunately many of these folks do not have the energy to search the web to find a solution to a problem..

    I know you may be very busy, would it be too much If I ask you to help me on this? Let me know..

    thx
    Shams

  • @ shams

    If you use WordPress, you will find everything ready.

  • Thanks

Got anything to say? Go ahead and leave a comment!