CODEWITHSUNDEEP
mysqlphp
Pedro Quezado
Pedro Quezado

Reputation: 141

Inserting emoji - smartphones in MySQL database with PHP?

Well with the the asks from is quite high explanatory will only strengthen the question.

APP have a chat system which be late the HTML , send it to PHP with ajax , but the usurer try to insert some emoji (emotion) of your device in the database he gets: "???" as a message.

How can I change this so that receive some kind of code for each emotion and the site list according to the value of it?

enter image description here

Upvotes: 3

Views: 2509

Answers (2)

Rick James
Rick James

Reputation: 142366

This is a character set issue, not just an emoji issue.

Use CHARACTER SET utf8mb4 throughout.

  • In the client
  • In the connection (mysqli_set_charset or PDO equivalent)
  • In the table (or column) definitions
  • <meta ... utf-8> in the html header.

Upvotes: 6

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

I have this sinking feeling inside that you have a character set problem if you are using InnoDB.

When you connect to MySQL, run this query

select
    A.variable_name,
    A.variable_value global_value,
    B.variable_value session_value
from
(
    select * from information_schema.global_variables
    where variable_name like 'collation%'
) A
inner join
(
    select * from information_schema.session_variables
    where variable_name like 'collation%'
) B
using (variable_name);

My guess is you are going to see a difference, especially with collation_connection.

My suggestion: Make the collation_connection for the session the same as the global value.

select A.variable_value,B.variable_value,C.variable_value
into @coll_conn,@coll_db,@coll_srvr from
(
    select * from information_schema.global_variables
    where variable_name like 'collation_connection'
) A,
(
    select * from information_schema.global_variables
    where variable_name like 'collation_database'
) B,
(
    select * from information_schema.global_variables
    where variable_name like 'collation_server'
) C;
set session collation_connection = @coll_conn;
set session collation_database = @coll_db;
set session collation_server = @coll_srvr;

You can then verify the change within your session by running the first query again.

GIVE IT A TRY !!!

NOTE: I am only suggesting this because a Developer approached me with this situation last week and I ran the same queries, showing him that the database was not at fault. The app was changing the collation in the session after authenticating.

Not The Database

Upvotes: 8

Related Questions