How to manage international languages in MySQL database
If you are making a web site where multiple languages used like Arabic, French, Hindi or Urdu then your MySQL database default settings is not valid to do that to insert all languages in database. If you insert data in your database and database is not configured as I am going to show you in this tutorial then your data will be look like ????? and you lost your data.
So now i am going to tell you how to configure database to accept international languages.
Very important HTML settings you have to add a meta for character set UTF-8
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
Now create table in your database its default character set will be latin1_swedish_ci
You have to change it to utf8_general_ci
Here is the insert query
mysqli_query($connection,"INSERT INTO `comments`(id,comment) values (N'$id',N'$comment')");
N’$id’ N is stands for national regional language character set.
see alos: How to use MySQLi_connect in PHP
Fetch code and query
<?php include('db.php'); mysqli_query($connection,"set character_set_results='utf8'"); ---- code --- ?>
Now your database is configured to insert all international languages.
Bux Panhwar of PHPGang, I am so grateful to you. A Facebook like and saying thanks is the least I can do for the pain you reduced!
phpgang your are awesom….thanks a lot….
Any specific reason for using utf8_general_ci ?
utf8_bin also does the same thing
I wouldn’t use mysql_query any more
Dear Hazoor bux i am also workong language converter in php. i have done all steps defined by you. but unfortunately getting result like this . ?????????????
Your data already showing “????” or new data you insert shows you “????”.
Old data converted to questions try these steps
1. the db collation has to be `utf8_general_ci`
2. the collation of the table with hebrew has to be `utf8_general_ci`
3. in your php connection script put `header(‘Content-Type: text/html; charset=utf-8’);`
4. in xhtml head tag put “
5. after selecting the db in the connection script put `mysqli_query($connection,”SET NAMES ‘utf8′”);`
Dear Huzoor Bux,
Till insertion it done well, but could not retrieve in proper Urdu form on the screen, it replaced with ???? Could you please write it’s retrieval code as you wrote insertion codes?
Add this meta in you html head section.
Added this but couldn’t get any positive result 🙁
What is in your database actual text or same ????
Sallam dear I have learnt from another search on the internet where I found and want to share with you all guys that before querying to get result on the screen just add this line inside php script tag
mysql_query(“SET NAMES ‘utf8′”);
everything will be ok
Thanks for your assistance too Huzoor Bux bhai 🙂
Thanks but how can i store emoticins(emoji) in mysql with international language.. please