Storing emojis in MySql database. Showing ??? in phpmyadmin

Question

Showing question mark ??? while storing emoji in a MySQL database. I have already set database and table collation to utf8mb4. But still, it's showing ??? ??? instead of emoji in the database. I also tried to change existing row in PHPMYADMIN console. But nothing is working. Showing ??? instead of Unicode characters. I have also changed Storage Engine for the table. InnoDB, MyISAM

SET NAMES utf8mb4;

ALTER DATABASE your_database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Linebreeding

What about displaying that emoji on a page, probably the phpmyadmin not showing it, probably will show in your app. This is all due to your unicode that you are using for the db, I know in sql server, it has UTF-16, so storing smileys or emoji's is possible. – Hejaz

Possible duplicate of MySQL utf8mb4, Errors when saving Emojis – Anyway

phpMyAdmin is showing the correct data; since you're seeing question marks there it means the data wasn't stored properly in the first place. – Anyway

Does this answer your question? How to store Emoji Character in MySQL Database – Pentangular

Answer 1

I finally got the solution. We will have to set our connection charset to utf8. After declaring connection variable set charset:

mysqli_set_charset($con,"utf8");

After that your emoji or any other unicode text will be encrypted and saved in mysql database.

Linebreeding

Answer 2

See this answer: you need to have utf8mb4 set everywhere on the way when saving and loading, PHP must know to use the encoding as well when communicating with database.

After you added screenshot: there most probably already is bad data in your database - I would guess the problem is in the way you save the emoji to the database, not in the way you load it (could be both though).

Amyloid

bro i have already tested emojis by inserting using phpmyadmin insert tab. Added directly through insert tab but still it is showing ??? in database. – Linebreeding

You need to post more info than this. How do you put the emoji in the database (specific code)? Is it through your PHP code (then posting the way you establish a database connection would be helpful), or phpMyAdmin? When you wrote "still not working", what steps have you taken differently? Please expand your question. – Amyloid

Missed your new comment when writing mine. Try saving it using your own code – I thought you were, because of the php tag. Old phpMyAdmin had this bug, see https://mcmap.net/q/1478260/-emoji-display-as-in-mysql-database – Amyloid

im experiencing a similar problem. i have no problem inserting emojis from PhpMyAdmin, but if i try to insert them via my PHP script, it just shows up as question marks... is there a way to encode it in PHP before inserting?? – Decemvir

i set the charset when connecting to the DB. ($connection = new PDO("mysql:host=$dbhost; dbname=$dbname; charset=utf8", $dbuser, $dbpass);) all my tables and columns are collated as utf8mb4_bin. not sure what im doing wrong here – Decemvir

instead of charset=utf8 use charset=utf8mb4 – Amyloid

See phpdelusions.net/pdo - very nice, comprehensive yet short intro to using PDO right – Amyloid

Answer 3

I had the same problem. I was using the CodeIgniter framework.
so I changed the below code in application\config\database.php

'char_set' => 'utf8mb4',
'dbcollat' => 'utf8mb4_unicode_ci',

and it started storimg emojis in database.

Frameup

Answer 4

I was also facing same issue in Codeigniter-4 of emojis reading ???? in mysql database after successful installation of emoji-picker

Thanks @ankit singh I was able to resolve it.

First had changed the COLLATE utf8mb4_bin on the comments table but it still continued showing ????.

So change in App->Config->Database.php settings to below helped

'charset'  => 'utf8mb4',
'DBCollat' => 'utf8mb4_bin',

Polemic

Answer 5

In PHPMyAdmin just change the type of the column to blob then it will work properly

Salvador