How to manage international languages in MySQL database using php ?
If you want to store multiple languages like Arabic, French, Hindi or Urdu to 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 special characters निम and your data will lost .
So now i am going to tell you how to configure database to accept Unicode strings (हिन्दी).
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" />
|
1 |
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> |
In HTML5
<meta charset="UTF-8">
|
1 |
<meta charset="UTF-8"> |
Create table in your database with CHARACTER SET utf8 COLLATE utf8_bin
mysqli_query($conn,'SET character_set_results=utf8');
mysqli_query($conn,'SET names=utf8');
mysqli_query($conn,'SET character_set_client=utf8');
mysqli_query($conn,'SET character_set_connection=utf8');
mysqli_query($conn,'SET character_set_results=utf8');
mysqli_query($conn,'SET collation_connection=utf8_general_ci');
|
1 2 3 4 5 6 |
mysqli_query($conn,'SET character_set_results=utf8'); mysqli_query($conn,'SET names=utf8'); mysqli_query($conn,'SET character_set_client=utf8'); mysqli_query($conn,'SET character_set_connection=utf8'); mysqli_query($conn,'SET character_set_results=utf8'); mysqli_query($conn,'SET collation_connection=utf8_general_ci'); |
<?php
$servername = "localhost";
$username = "";
$password = "";
$dbname = "";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$tcreation = "CREATE TABLE IF NOT EXISTS `encoding` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`language` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`title` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1";
if (!mysqli_query($conn,$tcreation))
{
die('Error: ' . mysql_error());
}
if($_POST[submit])
{
mysqli_query($conn,'SET character_set_results=utf8');
mysqli_query($conn,'SET names=utf8');
mysqli_query($conn,'SET character_set_client=utf8');
mysqli_query($conn,'SET character_set_connection=utf8');
mysqli_query($conn,'SET character_set_results=utf8');
mysqli_query($conn,'SET collation_connection=utf8_general_ci');
$language=$_POST[language];
$title=$_POST[title];
$sql = "INSERT INTO encoding (language, title)
VALUES ('$language','$title')";
if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Store and display Unicode string (हिन्दी) with MySQL using PHP</title>
</head>
<body>
<h1>Working with Foreign Languages using MySQL and PHP.</h1>
<form method='post' action='unicode-demo.php' name='test'>
First name:<br>
<input type="text" name="language">
<br>
Last name:<br>
<input type="text" name="title">
<br>
<input type="submit" name="submit" value='Submit'>
</form>
<?php
mysqli_query($conn,"SET NAMES utf8"); //the main trick
$cmd = "select * from encoding";
$result = mysqli_query($conn,$cmd);
echo "<table border=1>";
while($myrow = mysqli_fetch_row($result))
{
echo "<tr><td>".$myrow[1]."</td><td>".$myrow[2]."</td></tr>";
}
echo "</table>";
mysqli_close($conn);
?>
</body>
</html>
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
<?php $servername = "localhost"; $username = ""; $password = ""; $dbname = ""; // Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Check connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } $tcreation = "CREATE TABLE IF NOT EXISTS `encoding` ( `id` int(11) NOT NULL AUTO_INCREMENT, `language` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1"; if (!mysqli_query($conn,$tcreation)) { die('Error: ' . mysql_error()); } if($_POST[submit]) { mysqli_query($conn,'SET character_set_results=utf8'); mysqli_query($conn,'SET names=utf8'); mysqli_query($conn,'SET character_set_client=utf8'); mysqli_query($conn,'SET character_set_connection=utf8'); mysqli_query($conn,'SET character_set_results=utf8'); mysqli_query($conn,'SET collation_connection=utf8_general_ci'); $language=$_POST[language]; $title=$_POST[title]; $sql = "INSERT INTO encoding (language, title) VALUES ('$language','$title')"; if (mysqli_query($conn, $sql)) { echo "New record created successfully"; } else { echo "Error: " . $sql . "<br>" . mysqli_error($conn); } } ?> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Store and display Unicode string (हिन्दी) with MySQL using PHP</title> </head> <body> <h1>Working with Foreign Languages using MySQL and PHP.</h1> <form method='post' action='unicode-demo.php' name='test'> First name:<br> <input type="text" name="language"> <br> Last name:<br> <input type="text" name="title"> <br> <input type="submit" name="submit" value='Submit'> </form> <?php mysqli_query($conn,"SET NAMES utf8"); //the main trick $cmd = "select * from encoding"; $result = mysqli_query($conn,$cmd); echo "<table border=1>"; while($myrow = mysqli_fetch_row($result)) { echo "<tr><td>".$myrow[1]."</td><td>".$myrow[2]."</td></tr>"; } echo "</table>"; mysqli_close($conn); ?> </body> </html> |
