CODEWITHSUNDEEP
mysqlutf-8utf8mb4
Marianne
Marianne

Reputation: 41

Strange characters when saving emoji in the database. Is this normal?

Emoji's are saved in my database (phpmyadmin) like 😋😉😎😀😃 (input via form). It seems weird characters to me or is this normal? They are displayed on my website in the right way. So, there's not a real problem, but I want to make sure these weird characters are right. My table and text fields have character sets utf8mb4_general_ci and and I use <meta charset=utf-8> on the html-page.

Update

I went further research, but I have not yet come to a solution. The mojibake have become question marks. These emoji’s now makes me cry… hopefully someone knows what I am doing wrong.

I have tried to convert everything to utf8mb4 and utf8mb4_unicode_ci (server,database, table and column).

I even set in my.cnf:

[client]

default-character-set = utf8mb4

[mysql]

default-character-set = utf8mb4

[mysqld]

character-set-client-handshake = FALSE

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

If I look at the global variables, the output looks good: enter image description here

If, however, I look at the variables, the following will happen: enter image description here

I also looked at the Q & A:

Question Marks (Señor for Señor):

  1. The bytes to be stored are not encoded as utf8 / utf8mb4. Fix this. How can i fix this?
  2. The column in the database is not CHARACTER SET utf8 (or utf8mb4). Fix this. (Use SHOW CREATE TABLE.). The column has the right character set (utf8mb4).
  3. Also, check that the connection is UTF-8. How can i check this?

My test-form is like this:

<?php

header('Content-Type: text/html; charset=utf-8');

$con = mysqli_connect('*','*','*','*') or exit();

mysqli_set_charset($con, "utf8mb4"); /// without this, it become latin1

///mysqli_query($con, "SET character_set_results = 'utf8mb4', character_set_client = 'utf8mb4', character_set_connection = 'utf8mb4', character_set_database = 'utf8mb4', character_set_server = 'utf8mb4'");


include ($_SERVER['DOCUMENT_ROOT'].'/assets/errors_and_warnings.php');

echo mysqli_character_set_name($con);

echo $_POST['action'];

if (!empty($_POST['bericht'])) {
    
    $bericht = $_POST['bericht'];
    
} else {
    
    $bericht = 'leeg';
}

echo $bericht;

if (isset($_POST['action'])) {

mysqli_query($con, "INSERT INTO test (bericht, datum)
                         VALUES ('".mysqli_real_escape_string($con, $bericht)."',
                                 NOW())")
            or exit (mysqli_error($con));
}

?>

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Formulier</title>
</head>

<body>
    
<br><br>
    
    <form accept-charset="UTF-8" action="<?php $_SERVER['REQUEST_URI'] ?>" method="post" name="test" id="test">

      <input name="action" type="hidden" id="action" value="topicplaatsen" />
        
        Bericht:<br />
        
        <textarea name="bericht" cols="40" rows="10" id="bericht">Proeftekst</textarea><br><br>
    
    <input name="imageField" type="image" src="../vormgeving/2010/button/verstuur_blauw.gif"/>
    
    </form>
    
</body>
    
</html>

I am using mysql version 5.5.60.

Upvotes: 0

Views: 1056

Answers (1)

Rick James
Rick James

Reputation: 142268

😋😉😎😀😃 is Mojibake for 😋😉😎😀😃.

It usually means that latin1 was involved somewhere in the processing. For Emoji, you need CHARACTER SET utf8mb4, not just the old utf8.

This Q&A should help you figure out what step is missing.

One (of several) things it says: HTML forms should start like <form accept-charset="UTF-8">

More

The GLOBAL VARIABLES look correct; the SESSION VARIABLES seem not to have picked up the global values or something is overriding them.

  • Did you fail to reconnect?

  • You could add $con->set_charset('utf8mb4'); after connecting and before doing the various other queries.

  • Check for errors after each use of $con.

Upvotes: 1

Related Questions