10

I have a database filled with values like ♥•â—♥ Dhaka ♥•â—♥ (Which should be ♥•●♥ Dhaka ♥•●♥) as I didnt specify the collation while creating the database.
Now I want to Fix it. I cannot fetch the data again from where I got it from at the first place. So I was thinking if it might be possible to fetch the data in a php script and convert it to the correct characters.
I've changed the collation of the database and the fields to utf8_general_ci..

Bibhas Debnath
  • 14,559
  • 17
  • 68
  • 96

2 Answers2

15

The collation is NOT the same as the character set. The collation is only used for sorting and comparison of text (that's why there's a language term in there). The actual character set may be different.

The most common failure is not in the database but rather in the connection between PHP and MySQL. The default charset for the connection is usually ISO-8859-1. You need to change that the first thing you do after connecting, using either the SQL query SET NAMES 'utf-8'; or the mysql_set_charset function.

Also check the character set of your tables. This may be wrong as well if you have not specified UTF-8 to begin with (again: this is not the same as the collation). But make sure to take a backup before changing anything here. MySQL will try to convert the charset from the previous one, so you may need to reload the data from backup if you have actually saved UTF-8 data in ISO-8859-1 tables.

Emil Vikström
  • 90,431
  • 16
  • 141
  • 175
  • Ok. Will these characters `♥•●♥ ` be saved exactly like this in `utf8_general_ci` charset? And how do I know what charset was used when these characters `♥•â—♥` were saved? – Bibhas Debnath Jul 11 '11 at 07:32
  • 1
    utf8_general_ci is not a charset, it's a collation. The actual charset is called utf-8 and is another property of the table. utf-8 is able to code most characters in existence, so yes (indeed, SO itself uses UTF-8 so you wouldn't be able to show the characters to me if the answer to your question was "no"). As to the second question, start by checking which charset your table as well as the connection had when you saved the text. – Emil Vikström Jul 11 '11 at 07:40
  • Thanks. I'm getting it now. :) Setting mysql_set_charset to utf8 is helping for new entries. Looking into the other details. :) – Bibhas Debnath Jul 11 '11 at 07:44
3

I would look into mb_detect_encoding() and mb_convert_encoding() and see if they can help you.

AlienWebguy
  • 76,997
  • 17
  • 122
  • 145
  • When I'm using mb_detect_encoding on `♥•â—♥ Dhaka ♥•â—♥` and `♥•●♥`, in both cases its saying that its UTF-8. Now what to do? – Bibhas Debnath Jul 11 '11 at 07:51
  • 1
    Sounds like a broken encoding issue. Read this: http://stackoverflow.com/questions/1344692/i-need-help-fixing-broken-utf8-encoding – AlienWebguy Jul 11 '11 at 07:56