PHP and MySQL work great together and both comes optimized for each others– the best thing about the PHP and MySQL is both support multilingual and has endless opportunities. For the developers struggling in storing and retrieving Unicode charsets of the languages, including Hindi, Gujarati, Tamil, Telugu, Punjabi, Marathi, Nepali and Bengali etc.
There are several encoding issues faced while storing Unicode charset based languages and most of the time it only stores question marks or the garbage characters instead of the actual text. Though there are several methods and alternatives to store Unicode-charset, but the issues with those alternative methods is you cannot read and see the actual stored text and contents directly in the Database using MySQL console and phpMyAdmin.
In this article we will learn to store Hindi, Gujarati, Tamil, Telugu, Punjabi, Marathi, Nepali and Bengali etc. without losing its actual format or changing any content.
1. Configure MySQL to support UTF-8
The very first step is to configure your database to support UTF-8 Unicode and if the alteration isn’t possible with database, you can simply set either alter in the table or just in the column.
Altering Database to support UTF-8:
ALTER DATABASE YOUR_DATABASE_NAME CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
Altering only table instead of entire database to support UTF-8:
ALTER TABLE YOUR_TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Altering only specific column instead of entire table to support UTF-8:
ALTER TABLE
YOUR_TABLE_NAME
CHANGE COLUMN_NAME COLUMN_NAME
VARCHAR(191)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
If you’ve phpMyAdmin installed, you can make these changes using GUI option by simply going into phpMyAdmin–>Select Database–>Select Table–>Select operations.
2. PHP Script to Insert UTF-8 Unicode Charset to MySQL
Once you’ve modified the MySQL to support UTF-8 Unicode charset, the next step is to configuring your PHP Script to insert Unicode charset.
After establishing the connection to MySQL using PHP, you’ve to define the mysqli_set_charset(). For example to support UTF-Unicode simply add mysqli_set_charset($conn ,'utf8'); just after you’ve created the object of the mysqli().
Sample Snippet:
<?php
$conn = new mysqli($servername, $username, $password, $dbname);
mysqli_set_charset($conn ,'utf8'); //IMPORTANT
if ($conn->connect_error) {
die("Connection error: " . $conn->connect_error);
}
$sql = "INSERT INTO TABLE_NAME(COLUMN_NAME)
VALUES ('हिंदी')";
if ($conn->query($sql) === TRUE) {
echo "Data has been inserted.";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
