MySQL or PHP Is Appending a  Whenever the £ Is Used

MySQL or PHP is appending a  whenever the £ is used

What you're seeing is UTF-8 encoding - it's a way of storing Unicode characters in a relatively compact format.

The pound symbol has value 0x00a3 in Unicode, but when it's written in UTF-8 that becomes 0xc2 0xa3 and that's what's stored in the database. It seems that your database table is already set to use UTF-8 encoding. This is a good thing!

If you pull the value back out from the database and display it on a UTF-8 compatible terminal (or on a web page that's declared as being UTF-8 encoded) it will look like a normal pound sign again.

MySQL storing ââ¬Å in field

Your users' browsers are submitting data that is UTF-8 encoded, but you are connecting to a database which 1) presumes you are submitting latin1-encoded data 2) stores your data as latin1-encoded strings. Your data is therefore stored mis-encoded.

When you retrieve data from the database, it will be supplied as latin1 strings but because the latin1 strings are just mis-encoded UTF-8, and PHP naively treats all strings as binary strings, you just have your original UTF-8 string back again. When you echo this string onto an HTML page that is again declared as UTF-8 encoded, the string appears as it was submitted by the user.

However, if you view the contents of the database using software that has a sophisticated understanding of MySQL's treatment of character sets, that software will identify the column character sets as latin1 and make sure that the characters it displays are indeed the characters of the latin1 string, which as we noted was mis-encoded UTF-8. You therefore see mis-encoded data.

There are a number of steps you should take to remedy this. First, you are expecting to handle UTF-8-encoded data (as declared at the level of HTML), so you should make sure that that is how you are communicating with MySQL too. You do this by issuing SET NAMES 'utf8' whenever you form a connection to the database. (Note: your database API may provide a special function for changing a connection character set. I think the mysql API doesn't, but I'm not sure.)

Second, you should make sure that you are storing your data UTF-8 encoded. That means that your database columns' character sets should be utf8. A column character set can be changed using the ALTER TABLE ... MODIFY statement. Don't forget to also change the table default character set, too (this sets the character set for new columns added to the table for which you don't explicitly specify a character set). And change the database character set while you are at it.

However, when you change a column character set, MySQL presumes that the data already stored in rows of that table is not mis-encoded, but valid data stored in the old character set. It will therefore convert your mis-encoded UTF-8 data (which it treats as latin1-encoded data) into UTF-8-encoded data, so that you end up with data that is double-UTF-8-encoded. There is a trick to get around this: first convert the column character set to the binary character set, then from binary to utf8. That way, MySQL does not change the binary form of the data, because you are converting it via a format in which it is treated as arbitrary binary strings.

Good luck!

php mysql flex unicode

At the end of the day, this seemed to be an amfphp issue, as discovered at ghalex.com/blog/as3flexdb-and-utf8

How to remove  character

I had a similar issue a while ago, there are some useful comments and information here - it's PHP but I believe the theory would be the same:Question 386378

What is this character ( Â ) and how do I remove it with PHP?

"Latin 1" is your problem here. There are approx 65256 UTF-8 characters available to a web page which you cannot store in a Latin-1 code page.

For your immediate problem you should be able to

$clean = str_replace(chr(194)," ",$dirty)

However I would switch your database to use utf-8 ASAP as the problem will almost certainly reoccur.

Pound sign shows as A£ when entering it into mysql

You'll probably save yourself a lot of grief in the long run if change the design of your application to store the values and currency in separate columns.

value | currency
----------------
10    | GBP
16.42 | USD

You can then parse your user input before adding to the database, whether it be £10, 10GBP, or Ten Pounds Sterling, and format it appropriately when retrieving it from the database.

This will definitely make your life easier when it comes to supporting odder currencies, reacting to the change of a currency symbol (yes, that happens), dealing with exchange rates over time, and outputting data to places other than a website.

’ showing on page instead of '

Ensure the browser and editor are using UTF-8 encoding instead of ISO-8859-1/Windows-1252.

Or use ’.

Strange character appearing in text in mysql

It turns out that there was problem with the PHP library I was using to generate the input/textarea fields. For the textarea fields, it was replacing all the spaces in the value with nbsp special characters. So, things went a bit wonky when storing and re-storing that value to the database. I disabled that feature.

Thank you everyone for taking the time to offer your suggestions.

---

Related Topics