1
xsell
Converting from latin1 to utf8
  • 2009/12/30 4:30

  • xsell

  • Quite a regular

  • Posts: 245

  • Since: 2008/9/2 1


Hello

I know this Quastion is not easy to asnwer , i tried my best Google this Problem . and i Came up with a tool that Helped me Convert My database from latin1 to utf8 but the Problem is that My data Dose not Show up at all , when Check the MySQL Size , it shown about the Same size before Convert it .

So the Quastion has any one Come with a Good way to Convert from Latin1 or any non utf8 to utf8 without Lose any Data ..

I also Have Searched XOOPS and i came up in different soluations but none worked .

2
Marco
Re: Converting from latin1 to utf8
  • 2009/12/30 7:53

  • Marco

  • Home away from home

  • Posts: 1256

  • Since: 2004/3/15


yep, i've the same need, e.g i converted my 2.0.18 sites into 2.3 with the choice "none" in the upgrader, e.g to keep current collation/character set. i've too to convert my data, and i think many of us have to.

3
andrey3761
Re: Converting from latin1 to utf8

For database conversion in the coding utf8 it is necessary to fulfil queres:

ALTER TABLE table1 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
ALTER TABLE table1 
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

ALTER TABLE tableN CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
ALTER TABLE tableN 
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

ALTER DATABASE 
`database` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci


In mainfile.php to replace the connection coding:

define('XOOPS_DB_CHARSET''utf8');


I have made a script of automatic conversion of a database. If it is necessary, I can place on a site.

4
ghia
Re: Converting from latin1 to utf8
  • 2009/12/30 11:30

  • ghia

  • Community Support Member

  • Posts: 4953

  • Since: 2008/7/3 1


Warning:
This will not always lead to the desired result, because altough the original collation may be latin, mosttimes a winxxxx characterset is stored. This may leave untranslated characters, which are also neither UTF-8, producing question marks or diamonds on display in the navigator.

Always take a verified backup before trying this kind of operations!

5
frankblack
Re: Converting from latin1 to utf8
  • 2009/12/30 11:48

  • frankblack

  • Just can't stay away

  • Posts: 830

  • Since: 2005/6/13


I guess that xsell is using a Windows-1256 charset which gives you more than one problem.

Any script using htmlentities will fail on this charset for instance. More weird: my database collation is utf8_general_ci. When I add a file the field is filled with (hope this will not be destroyed):
Quote:
ÚÈÏ ÇáÈÇÓØ??? ??????


When I edit the file the database field shows the following:
Quote:
ÚÈÏ ÇáÈÇÓØ??? ??????


Some strange characters at the beginning and arabic characters after the tenth character (which are lost here).

edit: first quotes original entry was (have to put blanks between them):
Quote:
& # 218;& # 200;& # 207; & # 199;& # 225;& # 200;& # 199;& # 211;& # 216;& # 1593;& # 1576;& # 1583; & # 1575;& # 1604;& # 1576;& # 1575;& # 1587;& # 1591;


edit: WEIRD! On first submission, before editing this quote one WAS HOLDING ARABIC CHARS, after editing they were replace by questionmarks. ???

6
Dylian
Re: Converting from latin1 to utf8
  • 2009/12/30 12:20

  • Dylian

  • Friend of XOOPS

  • Posts: 237

  • Since: 2007/7/21


You could try this (altough it's a wierd way to do it, it should work). 1. Dump your MySQL database to a file on your pc; 2. Make a copy of the dump file (as backup); 2. Open the file with Notepad++; 3. Click on Format > Convert to UTF-8 without BOM; 4. Save the file; 5. Import the converted dump file in your database; 6. Run this php script:
<?php
// Database info
$dbhost 'localhost';
$dbuser 'db_user';
$dbpass 'password';
$dbname 'db_name';
//---------------
header('Content-type: text/plain');
$dbconn mysql_connect($dbhost$dbuser$dbpass) or die( mysql_error() );
$db mysql_select_db($dbname) or die( mysql_error() );

mysql_query("SET CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'"$db);

mysql_close($dbconn);
?>
7. Or if it still didn't work you could try this script:
<?php
// Database info
$dbhost 'localhost';
$dbuser 'db_user';
$dbpass 'password';
$dbname 'db_name';
//---------------
header('Content-type: text/plain');
$dbconn mysql_connect($dbhost$dbuser$dbpass) or die( mysql_error() );
$db mysql_select_db($dbname) or die( mysql_error() );

$sql 'SHOW TABLES';
$result mysql_query($sql) or die( mysql_error() );
while ( 
$row mysql_fetch_row($result) )
{
$table mysql_real_escape_string($row[0]);
$sql "ALTER TABLE $table DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
mysql_query($sql) or die( mysql_error() );
print 
"$table changed to UTF-8.n";
}

mysql_close($dbconn);
?>
Didn't try it but it's a combination of converting methods i found with google. Greets Dylian.

7
kris_fr
Re: Converting from latin1 to utf8
  • 2009/12/30 12:28

  • kris_fr

  • Theme Designer

  • Posts: 1009

  • Since: 2005/12/31


Also

mysqldump --add-drop-table -uroot -"NAMEBASE"  replace CHARSET=latin1 CHARSET=utf8 iconv -f latin1 -t utf8 MySQL -uroot -"NAMEBASE"


@+

8
andrey3761
Re: Converting from latin1 to utf8

Conversion of all database XOOPS in utf8

<?php

ini_set 
('max_execution_time''6000');
include_once 
'mainfile.php';
// ??????????? ??????
if( ! empty( $_POST['convert'] ) ) {

    include 
XOOPS_ROOT_PATH.'/header.php';
    
    
$old_prefix XOOPS_DB_PREFIX;

    
$srs $xoopsDB->queryF'SHOW TABLE STATUS FROM `'.XOOPS_DB_NAME.'`' ) ;

    if( ! 
$xoopsDB->getRowsNum$srs ) ) die( "You are not allowed to copy tables" ) ;

    
$count 0;
    while( 
$row_table $xoopsDB->fetchArray$srs ) ) {
        
$count ++ ;
        
$old_table $row_table['Name'] ;
        
// ???? ?????? ??????? ?? ???????? ???????? ??????, ?? ??????????...
        
if( substr$old_table strlen$old_prefix ) + ) !== $old_prefix '_' ) continue ;
        
        
// ???????????? ??????? ? utf8
        
$sql 'ALTER TABLE `'.$old_table.'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci';
        if(
$xoopsDB->queryF($sql)) {
            echo 
'ok - '.$sql.'<br />';
        } else {
            echo 
$xoopsDB->error().' - '.$sql.'<br />';
        }
        
// ????????????? ????????? ????????????? ??????
        
$sql 'ALTER TABLE `'.$old_table.'` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci';
        if(
$xoopsDB->queryF($sql)) {
            echo 
'ok - '.$sql.'<br />';
        } else {
            echo 
$xoopsDB->error().' - '.$sql.'<br />';
        }

    }
    
    
// ????????????? ????????? ???? ??????
    
$sql 'ALTER DATABASE `'.XOOPS_DB_NAME.'` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci';
    if(
$xoopsDB->queryF($sql)) {
        echo 
'ok - '.$sql.'<br />';
    } else {
        echo 
$xoopsDB->error().' - '.$sql.'<br />';
    }
    include 
XOOPS_ROOT_PATH.'/footer.php';

// ????? ?????
} else {
    include 
XOOPS_ROOT_PATH.'/header.php';
    
xoops_confirm(array('convert' => 1), 'convert.php''Convert in utf8?');
    include 
XOOPS_ROOT_PATH.'/footer.php';
}



?>

9
Marco
Re: Converting from latin1 to utf8
  • 2009/12/30 19:11

  • Marco

  • Home away from home

  • Posts: 1256

  • Since: 2004/3/15


yep, i would be great to add this into XOOPS upgrader.
there is already one converter, but it is deactivated in the upgrade script.

10
xsell
Re: Converting from latin1 to utf8
  • 2009/12/30 23:21

  • xsell

  • Quite a regular

  • Posts: 245

  • Since: 2008/9/2 1


Thank Ya All , I will try Out wht u Guys Suggested and Update this Thread later..

@frankblack Actualy i use Latin1 but the Best for me is windows-1256 But Some how i ended up with latin1 :)

Login

Username:
Password:

Lost Password? Register now!

Who's Online

78 user(s) are online (45 user(s) are browsing Support Forums)


Members: 0


Guests: 78


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: May 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits