CODEWITHSUNDEEP
phpmysqlmysqliutf-8utf8mb4
NoobishPro
NoobishPro

Reputation: 2549

Handling mixed utf8 and utf8mb4 MYSQLI & PHP

Just now, I ran into a problem that I by mere chance had not encountered before:

In order to support emoji's in specific columns, I had decided to set my mysqli_set_charset() to utf8_mb4 and a few columns encoding within my database as well.

Now, I ran into the problem of PHP actually not correctly handling accented characters coming from normal utf8 encoded fields.

Now, I'm stuck with having mixed utf8 and utf8mb4 results. Since my data-handling is not very strong (used to work frameworks that handled it all for me) I'm quite unfamiliar with how I could best resolve this.

I have thought about the following options:

1 ) set my entire database to utf8mb4 collation instead of utf8 with a few exceptions.

2 ) use mysqli_set_charset() to change it, and simply make sure the queries getting said data are seperated

Now, neither of these seem like great ideas to me, but I can't really think of any better solution.

So then there's the remaining questions:

I'm at a real loss on this subject and I honestly can't guess which option is best. Googling on it didn't help too much as it only returned links explaining the differences of it or on how to convert your database to utf8mb4, so I would very much love to hear the thoughts on this of one of the wise SO colleagues!

Columns in this specific case:

enter image description here

My response including PHP's character encoding detection:

arri�n = UTF-8
bolsward = ASCII
go�nga = UTF-8
lo�nga = UTF-8
echt = ASCII
echteld = ASCII
echten (drenthe) = ASCII
echten (friesland) = ASCII
echtenerbrug = ASCII
echterbosch = ASCII

My MYSQLI charset: mysqli_set_charset($this->getConn(), "utf8mb4");

-- and I just realised the problem was with my mysqli_set_charset. there indeed used to be an underscore in there...

Upvotes: 2

Views: 1629

Answers (1)

Rick James
Rick James

Reputation: 142278

It is spelled utf8mb4 (no underscore).

See Trouble with utf8 characters; what I see is not what I stored . In particular, read "Overview of what you should do" in the answer.

You do not need to change the entire db. It is fine to specify utf8mb4 for only selected columns.

You do need to use utf8mb4 for the connection, but you specify 'UTF-8', which is the outside world's equivalent of MySQL's utf8mb4. MySQL's utf8 is a subset of utf8mb4. (Note: I am being precise in use of hyphens and underscores.)

utf8mb4 is not bigger, nor slower for transferring characters that are in common between utf8mb4 and the utf8 subset. Emoji are 4 bytes, so they are bigger than most other characters, but you are stuck with them being 4 bytes; don't sweat it.

Upvotes: 4

Related Questions