import export special characters

Hi, I have an odd problem in that my data appears in admin and catalog and through phpMyAdmin to have messed-up special characters. For example, I see España when I should see España.

Because I have so much of this kind of data, I was looking for a way to correct it all, and exported the database as an sql file, and opened it my editor. There, the characters appear correctly! Cluching at straws, I hoped this export process fixed things, and that a re-import would straighten the site out. It does not, I continue to get incorrect characters in admin, catalog and via phpMyAdmin.

Then I realised that my editor was just showing me what I wanted to see (RapidPHP) so I switched to TextPad, and saw the crud. So I then spent one hour going through replacing all the Ãetc with something useful. What a pain, but on loading this sql file to production, my characters are OK.

My host is using phpMyAdmin 2.9.0.2 with MySQL client version: 5.0.22. The MySQL server is MySQL 4.0.27-standard-log, with PHP 4.3.11 with Apache 1.3.33 on linux.

The database which I have loaded is the locally-migrated 1.3.5 version from the host enviroment. That is, I backed up my db from my host, created a db locally and loaded it with the sql backup. Then I ran an upgrade from 1.3.5 to 1.3.6. then I spent many many days working through various problems and bugs until I am now at the point where I need to move back to the host for final testing.

When I initially exported the sql, and tried to load it to the host, I ran into errors flagged on Collation and so on, as others have reported. So I edited the sql file, and made these changes:

Replaced all like this:

ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=38 ;
```  With something like

TYPE=MyISAM AUTO_INCREMENT=38 ;

entry_gender char(1) collate latin1_general_ci NOT NULL default '',

    ````
entry_gender` char(1) NOT NULL default '',
```When I look at the tables locally I see the collation column, where it does not exist on the host.  Also, locally I can Import and select the "Character set of the file"  but in the host phpMyAdmin environment, I do not have this option.

Could someone please try and enlighten me as to what is going on here, and give me some suggestion as to how to overcome the problem in the future.  I want to be able to use my local instance to make off line changes, run tests and so on, but can't afford to spend time like that editing the character content each time...

Locally I use phpMyAdmin 2.9.1 (MySQL client version: 3.23.49) and MySQL  Server version: 4.1.21-community-nt


cheers