Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have 'inherited' an osCommerce 2.2, PHP4 and MySQL4 setup which makes trouble. The problem is the character encoding. While the tables are defined to contain UTF8 they actually contain latin1. To mix something completely new into this, it seems like the old coder manually changed this by setting SET CHARACTER SET 'utf8' when the connection to the database is made.

It now looks like this:

  • MySQL: UTF8 but actually contains latin1.
  • PHP: MySQL-Connection gets manually set to UTF8.
  • HTML: is delivered as ISO-8859-1 aka latin1

This setup worked until a week ago when the provider changed something (they actually denied touching the database or any other stuff at all, but they had server problems).

The problematic part is that german special characters öäüß are displayed correctly, but the moment you want to insert them into the database the whole osCOmmerce sessions breaks and only the text until that character is inserted.

  • Text to insert: Das _ä_ ist ein Umlaut
  • Text which gets saved: Das _

The moment I change the manual intrusion by setting the character set to latin1 when the connection is established, every insert is fine, but the database is only delivering ? instead of the special characters.

I have no idea how to resolve this, if I change the database columns to latin1 I lose all special characters, if I change the encoding on the connection either the displaying or inserting fails. I'm completely out of ideas.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

When you are changing data that is in another character set you have to first change it to blob and then to the target character set (latin1). If you don't mysql will do the conversion for you and you don't wont that, since you've already got the data in latin1?

AFAIK when you use set chatacter set in a connection (ie php) mysql automatically converts all the data in the table to the connection's character set.

share|improve this answer

Not elegant but usually working solution:

  1. dump whole database to SQL file - check if you have proper encoding in text file,
  2. fix queries for tables, to create UTF-8 table
  3. check settings of your database - use UTF-8
  4. import whole database again
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.