10

I have a DB - with the table articles.

I want to convert the title, and content field to utf8 now - all data looks like this: פורטל רעל נפתח רשמית! I want it to become normal hebrew characters.

Thanks

WEBProject
  • 1,337
  • 5
  • 16
  • 33
  • do with `phpmyadmin`, go to that table, alter the attribute...simple – xkeshav Mar 08 '11 at 12:22
  • Tried it already, the data on pma its self looks same. – WEBProject Mar 08 '11 at 12:23
  • 1
    It's difficult to say what to do without more information, but one thing to be aware of is that changing a table's character encoding in MySQL does *not* convert the data to the new encoding. The actual bytes stored in the database remain the same. – Rob Agar Mar 08 '11 at 12:28
  • Rob is right... first change and then insert new data and see the result – xkeshav Mar 08 '11 at 12:35
  • 1
    Even it does work when inserting new data - I need to convert the old data.. – WEBProject Mar 08 '11 at 12:42

5 Answers5

9

The following MySQL function will return the correct utf8 string after double-encoding:

CONVERT(CAST(CONVERT(field USING latin1) AS BINARY) USING utf8)

It can be used with an UPDATE statement to correct the fields:

UPDATE tablename SET field = CONVERT(CAST(CONVERT(field USING latin1) AS BINARY) USING utf8);
Siamak Motlagh
  • 5,028
  • 7
  • 41
  • 65
Evandro
  • 91
  • 1
  • 1
8

if you need to convert the whole database , you can back it as databaseback.sql file then form your command line iconv -f latain -t utf-8 < databaseback.sql > databaseback.utf8.sql

you can use the http://www.php.net/manual/en/function.iconv.php
to convert each row in php in case you don't have command line access

and lastly don't forget to convert the collation of each field in phpmyadmin , then you can resotre the utf8 back easily

update

if you got iconv is not recognized , it means that you don't have iconv installed

much more easier solution is : Migrating MySQL Data to Unicode

http://daveyshafik.com/archives/166-migrating-mysql-data-to-unicode.html

tawfekov
  • 5,084
  • 3
  • 28
  • 51
5

You can make mysqldump from this database. Then download something like Notepad++, open dump file, convert it to UTF8, then replace through the file all encodings to utf-8 including the first SET NAMES operator.

If you make dump to file via phpMyAdmin (with default settings) use output file encoding ISO-8859-1 instead of UTF-8 as you can see by default.

Smamatti
  • 3,901
  • 3
  • 32
  • 43
Vladislav Rastrusny
  • 29,378
  • 23
  • 95
  • 156
4

You can write a little php script which does the conversion. See http://www.php.net/manual/en/function.mb-detect-encoding.php and http://php.net/manual/en/function.mb-convert-encoding.php This is how I did this.

And remember to use strict mode! http://www.php.net/manual/en/function.mb-detect-encoding.php#102510

In pseudocode it would be sth. like this:

str = getDataAsString()
if(!isUTF8(str)) {
  str = convert2UTF8(str)
}
saveStr2DB()
Tobias
  • 7,238
  • 10
  • 46
  • 77
  • see http://www.php.net/manual/en/function.mb-detect-encoding.php#102510 - You have to set the last parameter to "true", then it will find out the correct encoding :) – Tobias Mar 08 '11 at 12:55
  • what should i put in the middle arg? – WEBProject Mar 08 '11 at 13:00
  • use this: mb_detect_encoding($str, 'UTF-8', true); – Tobias Mar 08 '11 at 13:06
  • I get UTF-8 , what should i do with it? if its utf-8, then how do i convert it? – WEBProject Mar 08 '11 at 13:13
  • if the database is set to utf8, so what´s about the output html page, do you view this with phpmyadmin or within your own script? check if your browser and the webpage is set to utf-8 as well – Tobias Mar 08 '11 at 13:16
2

try

ALTER TABLE `tablename` CHANGE `field_name` `field_name` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
xkeshav
  • 53,360
  • 44
  • 177
  • 245