CODEWITHSUNDEEP
phpmysqlunicodeutf-8utf8mb4
Vipul Sharma
Vipul Sharma

Reputation: 545

emojis show up as question marks after inserting into database php

I have used utf8mb4 as the encoding and character_set_connection and character_set_database have been set as utf8mb4. The charset in my webpages is set to utf8. I have used PDO and when I open the connection to the database I use utf8mb4 as the charset. The columns in the tables also have utf8mb4_unicode_ci as collation. The storage engine used is MyISAM.

The difference occurs because I can not edit character_set_server(since I am on shared hosting), it is set by default as utf8 and the collation_server by default is utf8_unicode_ui. I cannot change these by any means. It worked fine on localhost with similar character variables.

The connection variable is $conn which is opened as
$db_server = 'localhost'; $db_name = 'userdb'; $db_encoding = 'utf8mb4'; $db_user = 'test'; $db_pass = 'password'; $conn = new PDO('mysql:host='.$db_server.';dbname='.$db_name.';charset='.$db_encoding,$db_user,$db_pass);
The form for taking input is as simple as
<form action="" method="POST" accept-charset="UTF-8"><input type="text" class="form-control" name="inp"></form>
And the input is processed as
$test = $_POST['inp']; $it = $conn->prepare("INSERT INTO tbl_test(message) VALUES(?)"); $it->bindParam(1,$test,PDO::PARAM_STR); if($it->execute()) echo $test; else echo 'Error!';

So the question is:

  1. Why does the problem occur even when I have used utf8mb4 and utf8mb4_unicode_ci wherever possible?
    2.Could this be a php problem rather than being a MySQL problem since the problem occurs during INSERTION in database(I have used stripslashes(), trim() and htmlspecialchars() functions for the strings)?
    3.Interestingly the emojis(unicode characters) work finely on one page of the website that is the chat page. Could it be due to encryption? There seems no possible explanation!

Since I am on shared hosting, I cannot edit the MySQL configuration file.

Upvotes: 7

Views: 7370

Answers (3)

Mateusz Petkiewicz
Mateusz Petkiewicz

Reputation: 128

I've changed charset for PDO db connection to utf8mb4 and wchole database collation to utf8mb4_general_ci, and emoticons finally worked!

Upvotes: 0

Rick James
Rick James

Reputation: 142296

There are multiple things that need to be declared utf8mb4 -- the column, the connection, the form, etc. Offhand, it looks like you did all of them.

See this for what causes "question mark": Trouble with UTF-8 characters; what I see is not what I stored

You talked about 2 character_set variables, but not the 3 that are needed:

| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_results    | utf8mb4                    |

See if your php.ini has default_charset UTF-8

Do not use any form of encode/decode, it just avoids fixing the problem.

Please show us echo bin2hex($text); for some emoji.

Upvotes: 1

Vipul Sharma
Vipul Sharma

Reputation: 545

After wasting a lot of time with character variables and mysql configurations, I fixed the problem with a simple solution that is base64_encode.
Passing the input string through base64_encode and later using base64_decode to show them worked in this case. However the strings take up more space than earlier but this solution is really simple and worthy.
I just posted this question to know if someone ever faced something similar but people really do not get the main point of the question. That is where the people on SO get really exhausting. Anyways thanks for your help people! :)

Upvotes: 15

Related Questions