CODEWITHSUNDEEP
mysqlencodingcharacter-encoding
Lance
Lance

Reputation: 4820

Storing Emoji's in MySQL

I know by now that in order to properly store Emojis in MySQL, the encoding and collation must be set to 'utf8mb4.' The problem is that I already have a lot of rows in my DB that were inserted while the table didn't have the correct encoding and collation. Is there any way that the current information in the rows can be used to update each row so that the emojis appear?

I changed the collation and encoding to the correct values. But, I can't seem to do

 $sql = "SELECT * FROM users";
 $result = mysqli_query($con, $sql);
 $count = mysqli_num_rows($result);
 $i = 0;

 while($row = mysqli_fetch_array($result)) { 
     $id[$i] = $row['id'];
     $bio[$i] = $row['bio'];

    $i++;
} 

for($i=0;$i<$count;$i++) {
    $sql = "UPDATE users SET bio = '".$bio[$i]."' WHERE id = '".$id[$i]."'";
    mysqli_query($con, $sql);   
}

I was hoping to just query for all of the rows and update each one now that i have the correct setting. But, even after updating each row, the emojis don't appear. Any thoughts?

Upvotes: 1

Views: 1106

Answers (1)

ivan.sim
ivan.sim

Reputation: 9268

Assuming that you have set the encoding and collation properties of your database, tables and columns correctly, then you shouldn't need to query all the rows and re-update them.

The first simple thing to try is to specify the character set in your PHP client connection by adding:

mysqli_set_charset($your_mysqli_connect_link, 'utf8mb4');

before making any mysqli_query() calls.

Now ensure that you have all the character set and collation properties set correctly on the server side too. Try this on the CLI:

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' 
       OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

Make sure your /etc/my.conf have all the correct configurations:

[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

Upvotes: 1

Related Questions