views:

2842

answers:

2

I have to store hindi text in a MySQL database, fetch it using a PHP script and display it on a webpage. I did the following:

I created a database and set its encoding to UTF-8 and also the collation to utf8_bin. I added a varchar field in the table and set it to accept UTF-8 text in the charset property.

Then I set about adding data to it. Here I had to copy data from an existing site. The hindi text looks like this: सूर्योदय:05:30

I directly copied this text into my database and used the PHP code echo(utf8_encode($string)) to display the data. Upon doing so the browser showed me "??????".

When I inserted the UTF equivalent of the text by going to "view source" in the browser, however, सूर्योदय translates into सूर्योदय.

If I enter and store सूर्योदय in the database, it converts perfectly.

So what I want to know is how I can directly store सूर्योदय into my database and fetch it and display it in my webpage using PHP.

Also, can anyone help me understand if there's a script which when I type in सूर्योदय, gives me सूर्योदय?

Solution Found

I wrote the following sample script which worked for me. Hope it helps someone else too

<html>
  <head>
    <title>Hindi</title></head>
  <body>
    <?php
      include("connection.php"); //simple connection setting
      $result = mysql_query("SET NAMES utf8"); //the main trick
      $cmd = "select * from hindi";
      $result = mysql_query($cmd);
      while ($myrow = mysql_fetch_row($result))
      {
          echo ($myrow[0]);
      }
    ?>
  </body>
</html>

The dump for my database storing hindi utf strings is

CREATE TABLE `hindi` (
  `data` varchar(1000) character set utf8 collate utf8_bin default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `hindi` VALUES ('सूर्योदय');

Now my question is, how did it work without specifying "META" or header info?

Thanks!

+5  A: 

Did you set proper Charset in HTML Head section?

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

and/or something like

   header( 'Content-Type: text/html; charset=utf-8' );

in your PHP code.

SOME links to existing posts on SO

http://stackoverflow.com/questions/202205/how-to-make-mysql-handle-utf-8-properly/202246 http://stackoverflow.com/questions/624301/setting-utf8-with-mysql-through-php

http://stackoverflow.com/questions/405684/php-mysql-with-encoding-problems

So what i want to know is how can i directly store सूर्योदय into my database and fetch it and display in my webpage using PHP.

Am not sure what you mean by "directly storing in the database" .. did you mean entering data using phpmyadmin or any other tool? If yes, I had tried using PhpMyAdmin so "Yes" you can enter data using phpmyadmin and retrieve it using php script. But I would suggest creating a php script and set the NAMES and CHARACTER SET etc when you create mysql connection and before you do insert queries and when you do select query. Above links will help you more.

Wbdvlpr
Also: http://stackoverflow.com/questions/1085093/strategy-for-supporting-unicode-multi-language-in-php5/
deceze
"SET NAMES utf8" did the trick for me! Thanks
Anirudh Goel
why did it work even without setting the header?
Anirudh Goel
am not sure about that .. what was your character set earlier?
Wbdvlpr
Better use mysql_set_charset() instead of 'SET NAMES', otherwise mysql_real_escape_string() isn't aware of the change. http://php.net/mysql_set_charset
VolkerK
+1  A: 
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8">


<?php 
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

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 character_set_results=utf8');
mysql_query('SET collation_connection=utf8_general_ci');

mysql_select_db('onlinetest',$con);

$nith = "CREATE TABLE IF NOT EXISTS `TAMIL` (
  `data` varchar(1000) character set utf8 collate utf8_bin default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1";

if (!mysql_query($nith,$con))
{
  die('Error: ' . mysql_error());
}

$nithi = "INSERT INTO `TAMIL` VALUES ('இந்தியா நாட்டின் பக்கங்கள்')";

if (!mysql_query($nithi,$con))
{
  die('Error: ' . mysql_error());
}

$result = mysql_query("SET NAMES utf8");//the main trick
$cmd = "select * from TAMIL";
$result = mysql_query($cmd);
while($myrow = mysql_fetch_row($result))
{
    echo ($myrow[0]);
}
?>
</body>
</html>
ROSE
thanks for the code, i had got it already..
Anirudh Goel