MySQL insert String error - UTF-8?

Question

I am inserting into a mysql database. I get the following error when trying to do the insert

Incorrect string value: '\xF0\x9F\x87\xB7\xF0\x9F...' for column 'field_4' at row 1

I thought I had figured out this error by simply changing the column encoding the to utf8mb4 and had tested but recently this error appeared again. I am using php to parse the string and run the following function before inserting...

function strip_emoji($subject) {
    if (is_array($subject)) {
        // Recursive strip for multidimensional array
        foreach ($subject as &$value) $value = $this->strip_emoji($value);
        return $subject;
    } else {
        // Match Emoticons
        $regexEmoticons = '/[\x{1F600}-\x{1F64F}]/u';
        $clean_text = preg_replace($regexEmoticons, '', $subject);

        // Match Miscellaneous Symbols and Pictographs
        $regexSymbols = '/[\x{1F300}-\x{1F5FF}]/u';
        $clean_text = preg_replace($regexSymbols, '', $clean_text);

        // Match Transport And Map Symbols
        $regexTransport = '/[\x{1F680}-\x{1F6FF}]/u';
        $clean_text = preg_replace($regexTransport, '', $clean_text);
        return 
}

There are several similar questions to this but I still have these errors. Any further advice on how to prevent this error? I realize that it is an emoji unicode character / sprite but not sure how to deal with it.

Was this ever solved? How?

randomx
– randomx

2017-08-02 17:06:18 +00:00
Commented Aug 2, 2017 at 17:06 — randomx
– randomx, Commented Aug 2, 2017 at 17:06

Joni · Accepted Answer · 2013-10-07 21:11:18Z

1

You are trying to insert a character that spans 4 bytes, so you have to convert the column to the utf8mb4 character set.

The utf8 character set is limited to characters that span 3 bytes (the Unicode characters U+0000 through U+FFFF).

answered Oct 7, 2013 at 21:11

Joni

112k14 gold badges151 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

becquerel · Accepted Answer · 2013-10-07 13:57:20Z

0

Do you have utf8 charset for the connection as well?

Adding ";charset=utf8" in the PDO-connection string, or executing the query "set names utf8".

answered Oct 7, 2013 at 13:57

becquerel

1,1317 silver badges11 bronze badges

1 Comment

eric MC Over a year ago

I'm using the codeigniter framework (doesn't really matter, just an FYI) and I currently have both the charset and db charset set to UTF8 with a collation of utf8_general_ci

Collectives™ on Stack Overflow

MySQL insert String error - UTF-8?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related