collation and charset mismatch

[funkathustra]

I just realized that although my page was being rendered using UTF-8, and my database tables are all utf8_general_ci, my PHP mysql connection was the default latin1... so all the data being stored in the database isn't really in the right format. Everything looks fine (since everything is going in Latin1 and is being pulled out at Latin1, but I want to fix it before the client starts dumping even more data into the tables.

 

I already figured out that I have to run a set names query in my PHP to get it to go into UTF-8... and any NEW data I put in the database will be stored correctly after that.

 

However, how do I convert the existing Latin1 data that's (mis)stored in a UTF8 database to UTF8 data? I would think this would be a common problem (since many people are using UTF-8 in the charsets and UTF-8-based collations these days), but I haven't found a good answer.

 

Thanks for the help!

 

[Mchl]

Several possible ways:

 

1. Use iconv to convert charset of existing data.

2. Dump all data, convert encoding of the dump, import data

3. Open two connections in PHP, one latin1, second utf8, use one for reading, second for saving

[funkathustra]

Several possible ways:

 

1. Use iconv to convert charset of existing data.

2. Dump all data, convert encoding of the dump, import data

3. Open two connections in PHP, one latin1, second utf8, use one for reading, second for saving

 

Great! That makes a lot of sense. Just so others who may be reading this have a bit more direction, I exported the entire table to an SQL script, I opened it in a text editor that supports conversion and such. I converted it to Latin1, and then assigned it UTF-8 encoding. Saved it. Imported it. Bam! Worked like a charm.

 

Thanks for the suggestion!