How to Store Emoji into MySQL Database 😍

Category
Tutorial
Reading
4 mins
Views
10.3K
Posting
23 Aug 2022

Emoji are special characters that are usually used in chat, but at this time in writing articles we usually want to use emoji when we write. Not only for chat purposes, the use of emoticons is commonly used in website-based article content.

By using emoji, the articles we make will be more colorful and more expressive. However, there is a slight problem regarding the use of this emoji, when we use it in the articles we create, the database cannot save every emoji that we insert by default.

This of course requires a special configuration of our database and the framework used, namely charset and collation. In this update, we will demonstrate how to change the database and framework settings to use emojis.

 

 

1. What are Charsets and Collations?

Charset is often used in HTML documents in translating a character into bits which can later be read by us from various types of characters, be it symbols, numbers or letters.

Now for the use of this emoji, a special charset is needed in its use, by default it will be utf-8, in this case we use utf8mb4 with the collation utfmb4_unicode_ci when using emoji into our content.

If the charset used is plain utf-8, then later the emoji will only display ???? because the character cannot be encoded perfectly, if you use the right charset and collate, the emoji will appear perfect.

While collation is a set of commands or rules in the database to sort and compare a character that is in the set.

Usually collations are used to sort and compare a column such as ORDER BY post_id DESC and WHERE post_id = "1".

 

How to Store Emoji into MySQL Database

 

2. Examples of using emojis in content

To add this emoji, we usually need a special HTML Editor to manage this, for example, CKEditor, CKEditor already provides a fairly complete emoji plugin that we can use in writing articles.

Currently, the addition of emojis is very commonplace used by bloggers or content writers in presenting their content. Adding emojis can increase engagement with users and also make content more attractive.

 

3. Then how to change the charset and collation in the MySQL database

In saving emoticons into the database, previously we have mentioned about the charset that must be changed specifically.

There are 2 components that must be changed, namely charset and collation, please enter into phpmyadmin then enter the syntax below to change the 2 components, namely:

The first step is to change the charset of your main database name, here I give an example of the database name I use is example_database, then enter the SQL tab and enter the following SQL syntax, then press submit:

ALTER DATABASE
    example_database
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

Changing the char set and collate in the main database has been completed, next is to change the charset in the table that will be used to store emoji, here I give an example of the table name is main_article, then the syntax used is:

ALTER TABLE
    main_article
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

Congratulations, you have changed both components into charsets and collates which are supposed to store emojis. The first code syntax functions to change these two database components into utf8mb4 and utf8mb4_unicode_ci, while the second syntax functions to change these two components from the table you want to change.

For the second syntax, not all tables will change their charset, we must first determine which table will be inserted by this emoji. Surely not all tables will store emoticons right? so first determine which table will be changed.

If all tables are changed to the charset, usually an error will occur, because it is caused by long rows and columns.

We assume you have changed and set the charset and collation in the database, the next step is that we need to make advanced configuration changes in our application, for example in Codeigniter or Laravel.

 

4. How to change Charset and Collation in Codeigniter 3

To change these two components in Codeigniter, it is very easy to do. You only need to change the line of code in the Codeigniter database configuration file in the application/config/database.php folder then open the file.

Change the code in the char_set' => 'utf8' and 'dbcollat' => 'utf8_general_ci' sections to:

$active_group = 'default';
$db['default'] = array(
    ...
    'char_set' => 'utf8mb4',
    'dbcollat' => 'utf8mb4_unicode_ci',
    ...
);

 

5. How to change Charset and Collation in Codeigniter 4

Furthermore, if you use the Codeigniter 4 framework, this setting is in the Database.php file which is in the App/Config folder. Open the file and change the 'charset' => 'utf8' and 'DBCollat' => 'utf8_general_ci' sections to:

/**
* The default database connection.
*
* @var array
*/
public $default = [
    ...
    'charset' => 'utf8mb4',
    'DBCollat' => 'utf8mb4_unicode_ci',
    ...
];

 

6. How to change Charset and Collation in Laravel

While in Laravel, to change these two components please enter the path config/database.php and open the file. In this file there are various configurations related to the connection with the database.

'mysql' => [
    ...
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    ...
],

Change the line of code in the charset and collation to utf8mb4 and utf8mb4_unicode_ci then save.

If the configuration in the database and application has been done, we can make sure you can save the emoticons into the database and display them on the front-end.

 

Conclusion

Actually, not only emoji can be stored in the MySQL database, with this change, you can also store several Unicode language characters such as Kanji (in Japanese, Hangeul (in Korean), Hanzi (in Chinese), Arabic (in Arabic). and many more. That's all the explanation this time, the use of emoji is now widespread to add an expressive and interesting impression in a content, you can use the HTML Editor to be able to use emoticons like CKEditor.

Share