Working with Foreign Languages using MySQL and PHP.
Wall Script
Wall Script
Monday, August 29, 2011

Working with Foreign Languages using MySQL and PHP.

I received many tutorial requests from my readers that asked to me how to display and store foreign/regional language UTF-8 data into MySQL database using PHP code. This post explains you to solve Unicode data problems, just there steps you should follow take a quick look at this post.

UTF-8 using MySQL and PHP.

Live Demo

Database
Sample database articles table columns id, title and description. Character set should be UTF-8 format.
CREATE TABLE `articles`
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(300) CHARACTER SET utf8 collate utf8_general_ci ,
`description` text CHARACTER SET utf8 collate utf8_general_ci,
PRIMARY KEY (`id`)
)

You can set this using PHPMyAdmin. Go to table structure and change collation latin1_swedish_ci to utf8_general_ci
MySQL Character set

Insert - Why prefix 'N'?
SQL insert statement. Here the N stands for National language character set. Which means that you are passing an NCHAR, NVARCHAR or NTEXT value Read more.

INSERT INTO
articles(title,description)
VALUES
(N'శ్రీనివాస్ తామాడా', N'新概念英语第');

HTML META TAG
You have to include this following tag in data results pages.

Useful Related Posts
Regional Language UTF-8 Text Box
Database Searching Techniques Regional Language.

<meta http-equiv="Content-Type" content="text/html
charset=UTF-8" />

Displaying Records
Contains PHP code displaying records form database. Before that you have to specify mysql_query() function data character set type.

<?php
include('db.php');
mysql_query ("set character_set_results='utf8'");
$query = mysql_query("SELECT * FROM articles") or die(mysql_error());
while($row=mysql_fetch_array($query))
{
echo $row['title']; // Article
echo $row['description']; // Description
}
?>

Hope this post helps your web project to reach global and regional(Indian languages). Thanks!
web notification

35 comments:

  1. You can replace:
    mysql_query ("set character_set_results='utf8'");

    With:

    mysql_query("set names utf8");

    I'm from Việt Nam :D

    ReplyDelete
  2. Good post srinivas... thanks a lot.

    ReplyDelete
  3. if you use mysqli you can http://php.net/manual/en/mysqli.set-charset.php
    then mysqli_set_charset($link, "utf8")

    for mysql connect use mysql_query("set names utf8");

    There is a different problem for example table was latin1 but our html page utf8 and we were writing utf8 in the database that stores single byte charset... and when we do not set connection default was latin1 basically we had utf8 in wrong data format but since we query latin1 we get back string that later is converted to utf8 in php and 'you do not notice the difference'

    real problem starts when you set names to utf8 and mysql 'converts' your latin1 to 'utf8' and you get garbage

    there is one simple trick how to convert data that are in stored as utf8 into latin1

    alter table T modify myField blob;
    alter table T modify myField text CHARACTER SET utf8 collate utf8_general_ci;

    and as a final thing with utf8_general_ci you will not be able to see 'accent' differences and that might be major problem.

    ReplyDelete
  4. demo live not working languaje spanish :(

    ReplyDelete
  5. thanks a lot buddy ! great tut
    gonna work on it soon!
    thanks once again!!!

    ReplyDelete
  6. You don't need
    mysql_query ("set character_set_results='utf8'");

    ReplyDelete
  7. Hi,

    I am generating URL slugs for permalink purpose and if it's a foreign language it will be generated in that language separated by Dashes, it saves into the database fine, it also shows correctly upon normal select query but it Doesn't work in the WHERE clause.

    For Example:

    SELECT *
    FROM Table
    WHERE permalink = 'تركيا-تقرر-تخفيض-التمثيل-الدبلوماسي-مع-إسرائيل'

    Can you please help me in sorting this one out

    ReplyDelete
  8. Hey thanks. This is helpful. Every once in awhile I waste time on character encoding problems.

    One question though: I'm assuming you only have to set character_set_results='utf8'" once for each mysql connection? i.e. you don't have to set it before each query right?

    ReplyDelete
  9. thank you very much.

    ReplyDelete
  10. It is really useful for me

    thank you very much for ur tutorial

    ReplyDelete
  11. I Have Data In MySQL in જુવાર
    this format how do i take this data in Required (Gujrati) Format

    ReplyDelete
  12. Admin,

    Is there any way that, I can sort records of Gujarati alphabets like kakkavari.?

    ReplyDelete
  13. Thanks man.

    You just solve my big problem.

    cheer.

    ReplyDelete
  14. hey i want to insert gujarati data into table i use all method which you describe above but still now when i have got prob... my $_POST variable get data in gujarati language but when this data inserted into table that time it is converted like this format "ભાવેશ ચા..." if you have solution of problems then plss rply meeeee...
    thanks,

    ReplyDelete
  15. Hi Srinivas i want get regional language data from mysql database using soap web service without mysql query.if you have idea please help me.thanks in advance.

    ReplyDelete
  16. I am new to php I have understand the concept, in my website i have created a list box of all the languages, but i don't know how to translate my english content to chinese or to spanish basically i need a translation code of a particular language.
    I have created a table Language(Lang_id,Lang_name)

    please help me

    ReplyDelete
  17. Really Really Helpful man Thank You Great Job :)

    ReplyDelete
  18. Please give solution for mysqli statatemen ? Thanks in advance

    ReplyDelete

mailxengine Youtueb channel
Make in India
X