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!
This question is related to
php
mysql
unicode
utf-8
internationalization
Did you set proper charset in the HTML Head section?
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8">
or you can set content type in your php script using -
header( 'Content-Type: text/html; charset=utf-8' );
There are already some discussions here on StackOverflow - please have a look
How to make MySQL handle UTF-8 properly setting utf8 with mysql through php
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.
I am not sure what you mean by "directly storing in the database" .. did you mean entering data using PhpMyAdmin or any other similar tool? If yes, I have tried using PhpMyAdmin to input unicode data, so it has worked fine for me - You could try inputting data using phpmyadmin and retrieve it using a php script to confirm. If you need to submit data via a Php script just set the NAMES and CHARACTER SET when you create mysql connection, before execute insert queries, and when you select data. Have a look at the above posts to find the syntax. Hope it helps.
** UPDATE ** Just fixed some typos etc
For those who are looking for PHP ( >5.3.5 ) PDO statement, we can set charset as per below:
$dbh = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
For Those who are facing difficulty just got to php admin and change collation to utf8_general_ci Select Table go to Operations>> table options>> collations should be there
<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>
CREATE DATABASE hindi_test
CHARACTER SET utf8
COLLATE utf8_unicode_ci;
USE hindi_test;
CREATE TABLE `hindi` (`data` varchar(200) COLLATE utf8_unicode_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `hindi` (`data`) VALUES('????????');
Source: Stackoverflow.com