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?
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.
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
0xA30xA3 encoding using UTF-8 results in the bytes 0xC2, 0xA30xC2 and 0xA3 are represented by the iso-latin-1 glyphs "£"