XOOPS 
Forum
Forum Index General Forums Community Support Modules Support Themes Support Development International Support Module Hack Reviews Theme Designer Talk
  1. Board index / 
  2. XOOPS Community Support forums / 
  3. 2.4.x Support 
  4.  / Converting from latin1 to utf8
Register
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:

le="color: #000000"><?php 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:

le="color: #000000"><?php 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

le="color: #000000"><?php mysqldump --add-drop-table -uroot -p "NAMEBASE" | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | MySQL -uroot -p "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 , 0 , strlen( $old_prefix ) + 1 ) !== $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 :)
(1) 2 »

Post Reply

      
0 + 8 = ?  
Input the result from the expression
Maximum attempts you can try: 10
* = Required

Search

Advanced Search

Recent Posts

Who's Online

188 user(s) are online (72 user(s) are browsing Support Forums)

Members: 0

Guests: 188

more...

Donat-O-Meter

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

Latest GitHub Commits