Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 873
Vipul Sharma Avatar asked Dec 18 '22 20:12

Vipul Sharma


1 Answers

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! :)

like image 118
Vipul Sharma Avatar answered Jan 05 '23 17:01

Vipul Sharma