Emoji and ColdFusion

Question

I've been playing around with this issue for a while now. What I'm trying to achieve is to save to the MySQL database, and output to the window thereafter, the generally confirmed emoji such as 😀 😁 😂 (yep, 21st century problems)

After much of the tinkering around and following various tutorials and answers found on this site I have managed to achieve the ability to save them to the database.

I have a simple self submitting form as an example, and a MySQL database configured as UTF8mb4. If I paste emoji to the textarea and hit submit, it does save onto the database table as it should. When viewed using phpmyadmin, it does show the test emoji saved as expected. However, when I use cfquery to get the string from the database and output it to the window, it shows a simple '?'.

Any ideas?

Extra information: my test window does have the following tags included at the top:

<cfset setEncoding("form","utf-8")> 
<cfcontent type="text/html; charset=utf-8"> 
<cfprocessingdirective pageencoding = "utf-8">

The database connection string also has allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8, which seems to be working, as I can successfully insert the emoji. What am I missing?

Edit 1

As per request, below is the code used to select string containing the emoji from the database and to output it to the window:

<cfquery name="get_ci" datasource="test" username="#application.db_username#" password="#application.db_password#">
       SELECT _string
       FROM test_table
       WHERE _id = 1
       LIMIT 1
</cfquery>

<cfoutput>#get_ci._string#</cfoutput>

Edit 2 (solution)

After much deliberation and there was certainly a lot of it here's the solution that at least worked for me. In order to upgrade the MySQL server to support UTF8mb4 I used this guide: https://mathiasbynens.be/notes/mysql-utf8mb4 (please note that was done way before starting this thread and is not the solution to the issue raised). However, after getting it to work on our hosted VPS with the help of its technicians, I have managed to successfully match the settings and here's the answer: the guide asked to update my.ini the settings to the following:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

However, the handshake and collation ones were the two that were tripping the entire operation. As soon as I commented them out, wola! So just in case somebody else is struggling with this issue and resorting to … Try commenting these settings out and see if it works.

Updated my.ini with applied comments:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
#character-set-client-handshake = FALSE
character-set-server = utf8mb4
#collation-server = utf8mb4_unicode_ci

Answer 1

The Solution:

After much deliberation and there was certainly a lot of it here's the solution that at least worked for me. In order to upgrade the MySQL server to support UTF8mb4 I used this guide: https://mathiasbynens.be/notes/mysql-utf8mb4 (please note that was done way before starting this thread and is not the solution to the issue raised). However, after getting it to work on our hosted VPS with the help of its technicians, I have managed to successfully match the settings and here's the answer: the guide asked to update my.ini the settings to the following:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

However, the handshake and collation ones were the two that were tripping the entire operation. As soon as I commented them out, wola! So just in case somebody else is struggling with this issue and resorting to … Try commenting these settings out and see if it works.

Updated my.ini with applied comments:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
#character-set-client-handshake = FALSE
character-set-server = utf8mb4
#collation-server = utf8mb4_unicode_ci