UTF8 data problem

Forum Moderators: open

Message Too Old, No Replies

UTF8 data problem

getting ВЈ100 when I expect Ј100

kila_m

5:25 pm on Mar 26, 2015 (gmt 0)

Im still trying to get my head around UTF8 character encoding and MySQL.

Recently I decided to encode in UTF8 because a plugin I was using needed the data to be in that format otherwise it wouldn't work.

Everything appears to be working but one problem I'm having is data from one field is coming back as:

ВЈ100

when I expecting:

Ј100.

However its just that one field as the other fields on the same page appear correct with similar content. The only difference I see is this one field is MEDIUMTEXT while the others are VARCHAR.

I converted the database already:

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

How can I get this field to display correctly, what am I doing wrong ?

Im using PHP as my scripting language with mysqli functions.

brotherhood of LAN

5:50 pm on Mar 26, 2015 (gmt 0)

Try

SELECT CAST(fieldname AS BINARY) AS fieldname FROM table

There's various layers of character collation in MySQL (field,table,database,client) and I've found this the simplest way to ignore it.

Also you may want to use htmlentities() if you're outputting to a web page.

lucy24

8:36 pm on Mar 26, 2015 (gmt 0)

Short answer: Your data is getting stored correctly. The problem is in the display. (I could tell this from the post title alone, making you the poster child for Correct Way To Name A Post ;)) *

The problem with entities is that they can make it impossible for the human user to override an encoding error; you'd have to use nothing but entities everywhere, starting with a plain-ASCII database. This seems an extreme measure unless you've got a lot of users with MSIE 5.

* This is assuming there are only two actions, in and out. If there are multiple actions, all bets are off.

kila_m

5:00 pm on Mar 29, 2015 (gmt 0)

I finally tracked the problem down.

SELECT HEX(dbfield) FROM datatable WHERE id=156;

This tells me that the field was double encoded.

Then there was an additional dom saveHTML() call which changed the field. Ive isolated the problem now.

Thanks all.