problem with hebrew encoding (please help!!!!)

[abdoul]

hello

I was supposed to fix some encoding problem at the database at work.. although im new to mysql i did a little change with the colllation of one of the fields at the problematic table.. this didn't work.. but this isnt the bad story.. the disaster is that when i changed back the field collation to the first value i noticed that more letters are suffiring of bad encoding...

now to the detailes:
im using MySQL 4.1.20 with phpmyadmin

at the homepage of the phpmyadmin i see the following:
MySQL charset: UTF-8 Unicode (utf8) 
MySQL connection collation: utf8_general_ci

at the problematic table the collation is set to: latin1_swedish_ci

and these are the first four fields of my table:
Field          Type          Collation   
id              int(11)   
name_he    text            latin1_swedish_ci 
name_en    text            latin1_swedish_ci 
district        text            latin1_swedish_ci

now the problem was that the data is written in the database in hebrew.. and it showed up great at the site but two letters: the letter "Alef" and "Mem".. where it showed me some square sign instead of the letter itself..

so what i did was:
1) changing the collation of the field "name_he" to utf8_general_ci..
2) adding the following code line just after connecting to the database: mysql_query("SET NAMES 'utf8'") or die(mysql_error());

and when i noticed it didnt help i tryed to get this field collation back to latin1_swedish_ci.. i deleted the code line i added before, but now.. the name_he field is showing me all letters missed instead of two letters.. the other fields wasnt affected, which means that it shows all letters properly but the two letters "alef" and "mem"



pleaaaaaaaaaaaaaaaaaaaaaaaaaaassssseee  heelpppppppppppppppppppppppppp!!!!!!!

its gonna be a big disaster  :hf:

thanks in advnace,
abed

[fenway]

I'm not sure I understand... how was the collation supposed to fix the problem?

[abdoul]

well..

it's something with the charset and collation settings of the table.. i can tell so because i built a new database with the following settings:

Table Collation: hebrew_general_ci
Hebrew Text Field Collation: utf8_general_ci

i also added the following line to my php script just after the connection to the database:
mysql_query("SET NAMES 'utf8'") or die(mysql_error());

and it worked fine..

but it works only with the data that was entered after the new settings were set.. which means that i still cant view my old table properly..

so the question now is:
is there anyway to convert my old data to these new settings?

abdoul

[fenway]

You should be apply to apply the new collation, but only if the conversion would "work".

[abdoul]

hi

im sorry but i didn't understand anything of what u were trying to say... can u please explain?

[fenway]

I'm just concerned that you're going to lose something in the conversion.

[abdoul]

hi again

about the conversion.. am i supposed to build a new table with the correct collation and simply copy the rows to the new table or what? if so then its not working cause i tryed it and the rows were copied with the same problems... i still see the letters as ? marks instead of the correct letters.

here is how i did the copy of the 3rd row as an example:
INSERT * INTO NewTable SELECT * FROM OldTable WHERE id = 3

the NewTable has the correct collation.. but is there anything i still need to add to this query to force the translation?

[fenway]

I'm still confused -- if the "old" table has the wrong collation, than those characters may be permanently lost.