-1

I am trying to save emoji symbol to database record but each time it's not saving properly. I've referred this How to store Emoji Character in My SQL Database but still not working.
As per solution on above question, I tried to change the character set from utf8 to utf8mb4 and collation from utf8mb4_bin.

I tried everything like resetting to default and then changing it in the database table. I tried utf8mb4_unicode_ci, utf8_unicode_ci and utf8mb4_bin but it's not working.

I am using MySQL 5.6 version. And I am changing the collation with below query

alter table `users` convert to character set utf8mb4 collate utf8mb4_bin;

The above code is working fine, it's changing the UTF type in database. But emoji is not saving properly it's saving as question marks (????)

Below is my database table structure example: 

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'System generated id used for uniqueness',
  `introduction` longtext COLLATE utf8mb4_bin,
  `other_details` longtext COLLATE utf8mb4_bin,    
   PRIMARY KEY (`id`),
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=41213 ;


To save emoji using below PHP code: 

$dom = new DOMDocument('1.0', 'UTF-8');
$strWithEmoji = "";
$fullContent = '<meta http-equiv="content-type" content="text/html; charset=utf-8">'
               + $strWithEmoji;
$this->save($fullContent); // Function for saving into database


I don't know exact how many bytes are storing but I am trying to save only this emoji character "" and it's storing it as 4 question marks (????).

I tried all solutions from below links, but didn't worked for me:
How to store Emoji Character in My SQL Database
UTF-8 all the way through
The ultimate emoji encoding scheme

KRY
  • 103
  • 1
  • 2
  • 9
  • @Liam I tried all solution which is already asked, but it's not working – KRY Oct 09 '17 at 08:43
  • 1
    You've added pretty much nothing from your original question 2 days ago. Please don't ask the same question multiple times. It's not working isn't a question. – Liam Oct 09 '17 at 08:43
  • @Liam The question is deleted – KRY Oct 09 '17 at 08:45
  • Deleting the duplicate isn't going to help here. In fact it's making it look worse... There was a reason that that question was flagged (by a moderator) as duplicate of other questions. BTW people who can vote to close this can see the question even if you've deleted it. – Liam Oct 09 '17 at 08:47
  • @Liam I've specified all things which were not implemented in the previous question and it didn't help me. This is something another case I am trying to explain, then please specify how it is a duplicate. – KRY Oct 09 '17 at 08:51
  • 3
    Please provide exact bytes that are tried to be stored, and exact bytes actually stored. Also provide actual `SHOW CREATE TABLE` for the table in the question. Also, remove all frameworks/3rd party libraries from your question and provide a php+pdo/mysqli only code that demonstrates the problem. – zerkms Oct 09 '17 at 09:00
  • @zerkms Updated the question, I don't exact bytes, I am trying to save only single emoji character that I've mentioned in the question title. Thanks. – KRY Oct 09 '17 at 09:26
  • 1
    Also remove laravel or whatever you have and only run php+pdo/mysqli. That way you will isolate the problem. Then make sure that your file that is only php+pdo is in utf8 encoding and your pdo connection is also utf8. – zerkms Oct 09 '17 at 09:27
  • @zerkms I don't think the problem is with PHP code because the code is working fine on localhost machine but for only live servers the issue is occurring. – KRY Oct 09 '17 at 10:04
  • 2
    @KRY what kind of help you are seeking for if you don't want to isolate the problem yourself? "I don't think" --- good. What do you suggest then? – zerkms Oct 09 '17 at 10:05
  • @zerkms The code is working on my local machines but not working on live servers so clearly it's problem of MySQL, only I am not capturing what problem is there with MySQL and I've already updated the PHP code in question. – KRY Oct 09 '17 at 10:13
  • 3
    @KRY "so clearly it's problem of MySQL" --- good, if it's clear for you - you probably should know the answer. Otherwise, I'm not sure what that statement is based on. If you're not willing to properly isolate the problem - I'm not sure I'm ready to play the guessing game. Good luck then :-) "and I've already updated the PHP code in question." --- you have not. Isolation implies you don't have any frameworks or other 3rd party dependencies, just php+pdo (that I could run by myself on my local machine as-is), nothing else. – zerkms Oct 09 '17 at 10:20
  • @zerkms Why don't you try other questions? – KRY Oct 09 '17 at 10:42
  • @KRY well, I wanted to help you isolate the problem, and when it's isolated if it still persists - I wanted to reproduce it locally to help you identify your problem. But you decided that you don't need my help. Well, after 8 hours did you make any progress? Do you still think the strategy you've chosen is efficient? – zerkms Oct 09 '17 at 19:05

1 Answers1

18

The connection needs to specify utf8mb4 to MySQL. What is under the covers in DOMDocument?

is hex F09F9880, which should work in a column of CHARACTER SET utf8mb4 with any utf8mb4_* collation.

And here is another link: Trouble with UTF-8 characters; what I see is not what I stored

If all else fails, execute SET NAMES utf8mb4 from PHP after establishing a connection.

Rick James
  • 135,179
  • 13
  • 127
  • 222