Getting weird characters when fetching value in mysql database

Question

I'm getting this kind of result in my database â€™ everytime i'm typing this character ’

I already use mysql_query('SET CHARACTER SET utf8'); when fetching data from my database.

I already have <html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />..... this one on my HTML.

What else is lacking here? Your help would be greatly appreciated and rewarded!

Thanks!

You've got a character set mismatch somewhere. Either the data wasn't in UTF-8 format when it was inserted into the database, or your database isn't in UTF8 in spite of what you think, or your PHP scripts aren't UTF8. Everything has to be the same charset through the entire application stack or problems like this will crop up. — GordonM
– GordonM, Commented Jul 14, 2012 at 14:57
Are you pasting copy from word into the database? Or maybe using a WYSIWYG editor? — Joe Flynn
– Joe Flynn, Commented Jul 14, 2012 at 14:57
The entire pipeline has to be UTF-8, or you'll get those wonky chars - make sure that the browser->server->database pipeline is set correctly as well. submitting from a (say) iso-8859 page but then working with utf-8 for the server->database part will still break, because the wrong charset was used for browser->server. — Marc B
– Marc B, Commented Jul 14, 2012 at 14:57
@joeshmo: I think my client just copy pasted this from a word. MarcB: So what you're saying is that everytime data is being sent to database, it should also be encoded UTF8? — PinoyStackOverflower
– PinoyStackOverflower, Commented Jul 14, 2012 at 15:03

batfastad · Accepted Answer · 2012-09-25 08:11:59Z

I notice that you're running this query... mysql_query('SET CHARACTER SET utf8');
Try changing that to this...

mysql_query("SET NAMES 'utf8'");

That should ensure that the connection is UTF-8.

Also try going through the list of items on this article... http://blog.loftdigital.com/blog/php-utf-8-cheatsheet
This lists the steps that are needed to make sure you're using UTF-8 from front to back in your site/application but in summary:

Check you've got PHP's mbstring extension and you have mb_internal_encoding('UTF-8'); set in your script.
Make sure you're running this MySQL query after connecting to your database mysql_query("SET NAMES 'utf8'"); which ensures the connection is UTF-8.
Set the HTTP header of your output... header('Content-type: text/html; charset=UTF-8');. This doesn't seem to be needed if you've set mb_internal_encoding() above but useful for debugging
Make sure the output encoding of your HTML page is set... <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

MilMike · Accepted Answer · 2012-07-14 15:10:23Z

1

try to do a select with a charset collate and use utf8_bin like this:

SELECT k COLLATE utf8_bin AS k1
FROM t1
ORDER BY k1;

if it works for you, you can change the collate for a column by using alter table, here is an example I used today for one of my dbs: (you could do this also with phpMyAdmin easily)

ALTER TABLE  `users` 
CHANGE  `name`  `name` VARCHAR( 255 ) 
CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

but make a backup first!

answered Jul 14, 2012 at 15:10

MilMike

12.9k16 gold badges69 silver badges85 bronze badges

Comments

Josep Pueyo · Accepted Answer · 2020-08-28 05:19:02Z

0

I had the same problem and I fixed with:

mysql>charset utf8mb4

I read somewhere that utf8 is broken in mysql and utf8mb4 must be used instead.

answered Aug 28, 2020 at 5:19

Josep Pueyo

7974 silver badges19 bronze badges

1 Comment

Rosh_LK Over a year ago

In short: MySQL’s “utf8mb4” means “UTF-8”. MySQL’s “utf8” means “a proprietary character encoding”. This encoding can’t encode many Unicode characters. Link:medium.com/@adamhooper/…

Collectives™ on Stack Overflow

Getting weird characters when fetching value in mysql database

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related