Store and retrieve Unicode characters with MySQL using PHP

 

Newcomers to web programming world may face some very common issues related to Unicode and local language support.
Here I am pointing some generic queries that you might be asked to your colleagues in many days.
1. How to store Unicode characters in a database
2. How php can store/retrieve Unicode characters to and from Mysql database
3. How Indian languages like Malayalam or Hindi or Tamil can be stored in database.

There are some significant difference between Unicode and Ansi fonts (visit : http://users.csc.calpoly.edu/~bfriesen/software/builds.html for difference between them). Since local language computing become much popular and even laymen starts using internet. So storing local languages(Malayalam, Hindi, etc ) will be a necessary feature for the web sites. This can be a big problem to web programmers as most might not be aware of the techniques required for storing the Unicode languages in a database.

Here I am using MySql as DB and PHP as programming language.

Design of the database tables :

when creating a table, you should ensure your table can receive Unicode characters. below are the steps to create a table, which supports Unicode.

CREATE TABLE IF NOT EXISTS `doc_test` (
`id` int(10) unsigned NOT NULL auto_increment,
`unicodeText` varchar(450) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=30 ;

Here you have to make sure below things :
1. Your database encoding/collation should be utf8_general_ci.
2. Make sure your designed table(here doc_test ) should be having collation as utf8_general_ci.
3. Or even you can check the fields collation settings and make sure its utf8_general_ci.
4.Below image will help you to understand about what I meant.
I have taken a screen shot of table structure from phpmyadmin
 
(Click on the image to view in bigger size)

Here note the the circled part.
5. If your database is not having the proper collation settings, visit below link to convert it into utf8_general_ci.
http://bogdan.org.ua/2008/02/08/convert-mysql-database-from-one-encodingcollation-into-another.html.
Or install phpmyadmin software, which will help you to convert the collation settings by some mouse clicks.
6. Hope you have converted your collation settings.

Now We need PHP code to insert or retrieve the Unicode character.

1. Script to insert Unicode text to table. Here $txt is a variable which will be filled from a submit action. so user should have a text area in HTML page with a name of 'ta'.

(Eg : <textarea name="ta" rows="10" cols="80”>)

<?php
$txt= $_POST['ta']; // get the unicode text from a submit action.
$unicodeText = $txt;
@mysql_query("SET NAMES 'utf8'",$id); /// Its very inportant to display or insert unicode characters in db
$cQry= "insert into document (unicodeText) values ('".$unicodeText."')" ;
$cresult = @mysql_query($cQry,$id);
?>
2. PHP code to Display the Unicode text from MySQL database:

PHP CODE :
<?php
@mysql_query("SET NAMES 'utf8'",$id); /// Its very inportant to display or insert unicode characters in db
$cresult = @mysql_query("SELECT unicodeText FROM document",$id);
while ($crow = @mysql_fetch_array($cresult,MYSQL_ASSOC))
{
echo ( $crow['unicodeText']);
}
?>
Note: SET NAMES 'utf8' is an important command to work with Unicode texts. Don't forget to include that in your code.
3. Finished !!!
Hope you have enjoyed by seeing your local language visible in your web page.