1

I have a form that saves the price into the database but whn a user enters a £ sign it shows as A£ with a reflex accent over the A (dont know how to write one of those). It does not do this when i enter it in manually in phpmyadmin. The coalition of the price field is latin1 and it is a varchar so I don't see any problems with this. In the mysql query in the php the price has htmlspecialchars and mysql_real_escape_string on but I have taken thes off and the problem still persists.

Last of all it is not just displaying The £ sign differently, it is actually saving it into the database differently. How can I fix this?

Jacob Windsor
  • 6,750
  • 6
  • 33
  • 49
  • what is the meta tag charset on your page set as? – gunnx Jun 20 '12 at 12:09
  • possible help: http://stackoverflow.com/questions/1703636/how-to-store-euro-symbol-in-mysql-database – Kris.Mitchell Jun 20 '12 at 12:09
  • can you show us what code you are using....are you using "£" for pound sign..check space if its the somewhre before pound sign ..if yes trim..check meta tags it if it solves your problem let me know then – swapnesh Jun 20 '12 at 12:10
  • "dont know how to write one of those" With copy & paste? – glglgl Jun 20 '12 at 12:20
  • try this http://stackoverflow.com/questions/11888981/cannot-get-record-to-insert-into-mysql-data-using-php/11891450#11891450 – Parag Feb 24 '14 at 05:25

2 Answers2

6

This is usually caused by the character set of the connection and the user of the string disagreeing. For example, the connection character set could be returning UTF-8 and the PHP script might treat the string as iso-latin-1 which would cause exactly this. I'm not sure how these properties are set in PHP.

In the mysql CLI you can see the various properties using SHOW VARIABLES:

mysql> show variables like '%char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | latin1                                                  |
| character_set_connection | latin1                                                  |
| character_set_database   | latin1                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | latin1                                                  |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+

I would start looking in that direction. The indication here is that you are seeing multiple characters when you are expecting a single character. Anything outside of the 7-bit range will be encoded into multiple UTF-8 octets.

UTF-8 Details

  • "£" is the iso-latin-1 codepoint 0xA3
  • 0xA3 encoding using UTF-8 results in the bytes 0xC2, 0xA3
  • 0xC2 and 0xA3 are represented by the iso-latin-1 glyphs "£"
D.Shawley
  • 58,213
  • 10
  • 98
  • 113
3

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.

Colin Pickard
  • 45,724
  • 13
  • 98
  • 148
  • This is definitely the appropriate solution if you are doing anything other than simple data display. Never store currency information as anything other than a number and currency indicator tuple! That is the road to madness. – D.Shawley Jun 20 '12 at 12:19
  • I think this is the option I will take. Lack of caffiene has stopped me from thinking straight – Jacob Windsor Jun 21 '12 at 14:07