PHP not displaying emoji from MySQL/MariaDB database (SOLVED)

When retrieving rows from a MySQL (MariaDB) database, you may encounter a situation where all the text is displayed correctly, but question marks are displayed instead of emoji.

The most likely problem is an incorrectly used encoding when executing queries against the MySQL (MariaDB) DBMS. There are two ways to fix this situation.

1. Select utf8mb4_unicode_ci encoding when creating database

Strictly speaking, the problem of disappearing emoji or replacing them with question marks should not exist at all.

If this problem exists, then most likely your database is encoded in utf8_general_ci or some other obsolete one. The currently recommended collation for databases is utf8mb4_unicode_ci. For details, see the article “What is the difference between utf8_general_ci, utf8_unicode_ci, utf8mb4_general_ci, utf8mb4_unicode_ci collations. Which collation, character set and encoding to choose for MySQL database”.

With the correct database encoding selected, emoji should be displayed.

Note: more precisely, utf8mb4_unicode_ci is not exactly an encoding, in MySQL terms it is called “collation” and includes a character set, as well as comparison and sorting rules. That is, utf8mb4_unicode_ci is a COLLATION, and utf8mb4 is a character set, and UTF-8 is already a variable length encoding.

By the way, when creating tables, you also need to specify “CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci” for the table as a whole and “COLLATE utf8mb4_unicode_ci” for text fields.

This is exactly the situation I encountered on hosting:

• hosting databases can only be created in the web interface of the hosting control panel. The databases are created with utf8_general_ci encoding. And there is no way to fix this, because in addition to the database name and username, the web interface does not allow you to specify other parameters
• however, the tables I imported into the databases use the utf8mb4 character set – thanks to this, the data, including four-byte emoji characters, remained intact
• but when retrieving data from tables with a PHP script, instead of emoji characters, question marks are shown

Since I can't fix the database encoding in this case, there is one more solution.

2. Specify the character set utf8mb4 to connect to MySQL (MariaDB) from the PHP script

You can specify the correct character set using an SQL query:

SET NAMES utf8mb4;

This statement sets three session system variables: character_set_client, character_set_connection, and character_set_results to the specified character set.

In a PHP script this can be done like this:

<?php

$mysqli = new mysqli('localhost', $db_user, $db_password, $db_name);
if ($mysqli->connect_errno) {
	printf("Somehow failed : %s\n", $mysqli->connect_error);
	exit();
}
$mysqli->query("SET NAMES utf8mb4");

Selecting the correct character set didn't fix the issue – emoji are not displaying correctly

An unpleasant situation is possible when the data in databases and tables is damaged and setting the correct character set does not fix the situation.

If text with emoji was saved to a table with the utf8mb3 character set, that is, as UTF-8 stores a maximum of three bytes per code point, then four-byte characters (emoji and some Chinese characters) in it are most likely irretrievably lost.