PHP & MySQL special character encoding problem.

Question

When a user submits a special character ♠ it's stored in MySQL database as â� and if a user wants to change it instead of displaying it back as ♠ its displayed as â� how can I fix this problem so that its dsiplayed back as ♠ and saved as ♠?

On a side note how should I save my special characters using PHP?

I'm using PHP & MySQL

Answer 1

1. User types in data
2. You escape that data to avoid SQL injection (don't convert the special characters to html code equivalent yet)
3. Data gets stored in the database exactly how user typed it in
4. You pull the raw data back out
5. You run the raw data through a character encoding function or something equivalent to convert special characters to their html codes thus avoiding cross site scripting or html injection

Answer 2

That's could be a problem.
If you want to convert your special characters into entities, you have to htmlencode them twice when outputting into field value/textarea content. But it could mess with other characters - all become their entity representations - quotes, brackets and such. If it's what you're asked for - go ahead. But, in my opinion, it could be a terrible mess to edit such a text.
That's why it's better not to let users to use entities. Why can't they enter the symbol itself?

As for the special characters in your database - just use UTF-8 encoding in both database and HTML.