Joom!Fish, encodings and UTF-8 - How to or Newies read first

How to support more than one language on your site.

Moderator: General Support Moderators

Forum rules
ivo.apostolov
I've been banned!
Posts: 1868
Joined: Thu Aug 25, 2005 2:33 pm

Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by ivo.apostolov » Sun May 27, 2007 11:38 pm

Due to the many questions in the last weeks, I will try to summarize some issues with encoding on multilanguage sites with 1.0 series.
Basically, bilanguage sites, where one of the languages is English do not have problems with encoding, but if you experience such, I would highly advise you to put the Non-English language as default. This will allow you to run your Joomla! Backend on that language and in most of the cases, this solves 99% of the encoding problems and database missmatches in collations.

If you still experience problems with the encoding of the other language, this is caused almost in all of the cases by the apache (or other server) default encoding, that is preset. On some servers, this encoding is sent as a header in the cookie and causes issues. In such case, there is easy and fast solution:

- Open the file globals.php and at the end of it, right after the last

Code: Select all

}
and right before

Code: Select all

?>
put the following code:

Code: Select all

header("Content-Type: text/html; charset=ENCODING");
where ENCODING should be replaced by the encoding used for the language that is not English. Example (Windows Cyrillic):

Code: Select all

header("Content-Type: text/html; charset=windows-1251");
While with bilingual sites it is more or less easy and problemless, when it happens that you need to build a web site in three languages or even in two languages, where these use different charsets and none of them is English, I would highly recommend you to consider a fully UTF-8 web site.

How to do it?
Please do these actions, before installing Joomla!
1. Create your database manually and go to operations, there set a collation of the database as utf8_general_ci (Screenshots are available at http://www.joomfish.net).
2. Open the file includes/database.php go to line 102 and change the following code from:

Code: Select all

//        @mysql_query("SET NAMES 'utf8'", $this->_resource);
to:

Code: Select all

        @mysql_query("SET NAMES 'utf8'", $this->_resource);
Now save the file.
3. Open the file globals.php and at the end of it, right after the last

Code: Select all

}
and right before

Code: Select all

?>
put the following code:

Code: Select all

header("Content-Type: text/html; charset=UTF-8");
4. Open the file language/english.php and go on line 421. Change the following code from:

Code: Select all

DEFINE('_ISO','charset=iso-8859-1');
to:

Code: Select all

DEFINE('_ISO','charset=UTF-8');
5. Upload Joomla! and install Joomla! in the normal way.
6. When you install new languages, please be sure that those are saved as UTF-8 without BOM. Very cool editors that allow this are Crimson Editor and Notepad++

That's it. Now everything is expected to work like a charm. More information about how Joomla! 1.0.x handles UTF-8 is available in the following post:
http://forum.joomla.org/index.php/topic,55065.0.html
You can also consider to use the hack that allows generation of PDF documents in UTF and works for probably any language:
http://forum.joomla.org/index.php/topic,110713.0.html

Author note:
Older versions of MySQL doesn't supports databases with collation utf8_general_ci, then you most probably would not be able to create a fully compatible UTF-8 site.

Source: http://www.joomfish.net/content/view/39 ... g,english/
Last edited by ivo.apostolov on Mon May 28, 2007 9:14 am, edited 1 time in total.

User avatar
infograf768
Joomla! Master
Joomla! Master
Posts: 19133
Joined: Fri Aug 12, 2005 3:47 pm
Location: **Translation Matters**

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by infograf768 » Thu Aug 02, 2007 5:56 am

Other aspect not to underestimate:

It is suggested you enter your contents as raw opposed to html entities.

JCE editor does that by setting the right parameters in the mambot.

This will solve, among others, the search issues on your site.
Jean-Marie Simonet / infograf
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group

heyyo
Joomla! Fledgling
Joomla! Fledgling
Posts: 4
Joined: Sat Feb 04, 2006 4:12 pm

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by heyyo » Sun Sep 09, 2007 4:47 am

I have followed this tutorial on my website, but I still have problem with the module title and the html title. It was'nt possible for me to create my database, because it was already online, so I converted it with phpmyadmin(the database it self and then table by table).
The characters of the module title are stored in the database with only "?? ?? ??".
Any help would be appreciated.

My website : http://www.3u4u.biz.

heyyo
Joomla! Fledgling
Joomla! Fledgling
Posts: 4
Joined: Sat Feb 04, 2006 4:12 pm

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by heyyo » Mon Sep 17, 2007 6:55 am

My fault the joomfish tables wasn't in utf8_general_ci.

It will be great also to make a tutorial to convert a existing website to UTF-8 and not only from scratch.
A tool to convert the database and all tables in utf8_general_ci would be great( it was for me the longest part)

ivo.apostolov
I've been banned!
Posts: 1868
Joined: Thu Aug 25, 2005 2:33 pm

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by ivo.apostolov » Mon Sep 17, 2007 7:14 am

You can export your database, convert the file using the above mentioned ways and import it.

m1x
Joomla! Apprentice
Joomla! Apprentice
Posts: 16
Joined: Tue Jan 08, 2008 12:03 pm

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by m1x » Tue Jan 08, 2008 1:00 pm

to ivo.apostolov

I am going to use 3 lang. Russian,  English and Germany. Should I do actions which you wrote above before installing Joomla?

ivo.apostolov
I've been banned!
Posts: 1868
Joined: Thu Aug 25, 2005 2:33 pm

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by ivo.apostolov » Tue Jan 08, 2008 1:01 pm

m1x wrote: to ivo.apostolov

I am going to use 3 lang. Russian,  English and Germany. Should I do actions which you wrote above before installing Joomla?
Yes, it is recommended.

m1x
Joomla! Apprentice
Joomla! Apprentice
Posts: 16
Joined: Tue Jan 08, 2008 12:03 pm

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by m1x » Tue Jan 08, 2008 1:05 pm

I am sorry. but I did that, and when istalling Joomla it gave me error that can't create some tables ((
Maybe I need change only globals.php file?
Before I install without any problems. Then I removed all folders and databes (with phpmyadmin) and changed some scripts which you describe above and when started to install joomla it gave me error.((
Last edited by m1x on Tue Jan 08, 2008 1:10 pm, edited 1 time in total.

ivo.apostolov
I've been banned!
Posts: 1868
Joined: Thu Aug 25, 2005 2:33 pm

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by ivo.apostolov » Tue Jan 08, 2008 1:28 pm

What is your database version?

m1x
Joomla! Apprentice
Joomla! Apprentice
Posts: 16
Joined: Tue Jan 08, 2008 12:03 pm

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by m1x » Tue Jan 08, 2008 1:42 pm

Server version: 5.0.18-nt
I think your recommendations not for me. Beacuse I always changing encoding so it is not good (every time when open any new page)

ivo.apostolov
I've been banned!
Posts: 1868
Joined: Thu Aug 25, 2005 2:33 pm

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by ivo.apostolov » Tue Jan 08, 2008 1:46 pm

If you thinks so  ;)

m1x
Joomla! Apprentice
Joomla! Apprentice
Posts: 16
Joined: Tue Jan 08, 2008 12:03 pm

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by m1x » Wed Jan 09, 2008 7:49 pm

UTF-8 without BOM
What does BOM mean?

User avatar
infograf768
Joomla! Master
Joomla! Master
Posts: 19133
Joined: Fri Aug 12, 2005 3:47 pm
Location: **Translation Matters**

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by infograf768 » Wed Jan 09, 2008 8:19 pm

Jean-Marie Simonet / infograf
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group

User avatar
snock
Joomla! Apprentice
Joomla! Apprentice
Posts: 15
Joined: Mon Dec 19, 2005 10:10 am
Contact:

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by snock » Fri Jan 18, 2008 1:30 pm

Nice tip but doesn't always work.
In my case i had to translate to several languages: dutch, french, russian and german.
my base language is english.

I had the same problem with the russian characters. they didn't look like russian. Even with my knowledge of russian i could see there was something strange. So i looked in this forum and saw this tip about changing the globals.php. and voila (french) it worked.
But my when i looked at the french translation there was a new problem. it was missing characters. again with my knowledge of french(read poor) I discovered that my self.

So a new problem.
But at least i know where to search for it.
So i looked further and discovered that the problem was in the joomla head code.
I use dreamweaver as editor and I also use Joomlasolution wich is an extension for creating joomla templates.
in this extension there is a button who puts the joomla headcode in the template index.php file for you.
saves work but there is fault in the code according the charset.
it puts:

Code: Select all

<meta http-equiv="Content-Type" content="text/html;><?php echo _ISO; ?>" />
Where it should be:

Code: Select all

<meta http-equiv="Content-Type" content="text/html;<?php echo _ISO; ?>" />
There is a > to much after text/html;
I changed it and all my translations are fine.

Conclusion, If you are using this same extension from joomlasolution then delete this character and maybe you save yourself hours of work finding a solution..

Hopefully it will help others too...

User avatar
infograf768
Joomla! Master
Joomla! Master
Posts: 19133
Joined: Fri Aug 12, 2005 3:47 pm
Location: **Translation Matters**

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by infograf768 » Fri Jan 18, 2008 4:53 pm

Nice tip but doesn't always work.
Tip works...the extension does not... hehe
I suggest you let that company know about the bug.

Thanks for posting your findings.
Jean-Marie Simonet / infograf
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group

User avatar
snock
Joomla! Apprentice
Joomla! Apprentice
Posts: 15
Joined: Mon Dec 19, 2005 10:10 am
Contact:

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by snock » Sat Jan 19, 2008 2:12 pm

I don't think they support. but I'll give it a try..

User avatar
kai920
Joomla! Guru
Joomla! Guru
Posts: 542
Joined: Sun Sep 04, 2005 3:59 pm
Location: Hong Kong

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by kai920 » Mon Feb 25, 2008 8:56 am

ivo.apostolov wrote:You can export your database, convert the file using the above mentioned ways and import it.
Hi, I am trying to convert a test site to UTF-8. I exported my database, and created a new db using utf8_general_ci and re-imported. I now have a copy of my test site after following your instructions, but the tables are not in utf-8.
noname.jpg
This link states to "convert the language files to utf-8 (all language files including for editors, components etc.). Make sure NOT to save with the utf-8 BOM header option." I've only installed Jomcomment and SOBI2 on top of the default Joomla install, so do I only need to look in these two components' admin and component directories?

Are there any other steps I need to perform? Thanks.
You do not have the required permissions to view the files attached to this post.

User avatar
infograf768
Joomla! Master
Joomla! Master
Posts: 19133
Joined: Fri Aug 12, 2005 3:47 pm
Location: **Translation Matters**

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by infograf768 » Mon Feb 25, 2008 9:29 am

When exporting, the tables may contain a call to create them in the original encoding i.e. latin1

Use a text editor and change globally all these calls to

) TYPE=MyISAM

or
) TYPE=MyISAM CHARACTER SET `utf8`

keep the increments defined
Jean-Marie Simonet / infograf
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group

User avatar
kai920
Joomla! Guru
Joomla! Guru
Posts: 542
Joined: Sun Sep 04, 2005 3:59 pm
Location: Hong Kong

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by kai920 » Mon Feb 25, 2008 9:34 am

Thanks for your quick reply :)

Here is an example of a table I copied and pasted from my .sql export. Do I only need to change the last line and not the fields in between that say "collate latin1_general_ci"...?

Code: Select all

DROP TABLE IF EXISTS `jos_banner`;
CREATE TABLE `jos_banner` (
  `bid` int(11) NOT NULL auto_increment,
  `cid` int(11) NOT NULL default '0',
  `type` varchar(10) collate latin1_general_ci NOT NULL default 'banner',
  `name` varchar(50) collate latin1_general_ci NOT NULL default '',
  `imptotal` int(11) NOT NULL default '0',
  `impmade` int(11) NOT NULL default '0',
  `clicks` int(11) NOT NULL default '0',
  `imageurl` varchar(100) collate latin1_general_ci NOT NULL default '',
  `clickurl` varchar(200) collate latin1_general_ci NOT NULL default '',
  `date` datetime default NULL,
  `showBanner` tinyint(1) NOT NULL default '0',
  `checked_out` tinyint(1) NOT NULL default '0',
  `checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `editor` varchar(50) collate latin1_general_ci default NULL,
  `custombannercode` text collate latin1_general_ci,
  PRIMARY KEY  (`bid`),
  KEY `viewbanner` (`showBanner`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;
The last line should read something like this?

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=3 ;

User avatar
infograf768
Joomla! Master
Joomla! Master
Posts: 19133
Joined: Fri Aug 12, 2005 3:47 pm
Location: **Translation Matters**

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by infograf768 » Mon Feb 25, 2008 10:08 am

IN fact, once the db is created as utf8, last line can be
) ENGINE=MyISAM AUTO_INCREMENT=3 ;

oh, yeah and take off all these collate too inside the table
Jean-Marie Simonet / infograf
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group

User avatar
tharkun
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 138
Joined: Fri Feb 01, 2008 12:23 pm

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by tharkun » Tue Feb 26, 2008 3:04 pm

I recently needed to convert my whole joomla-db to utf8... not only the tables but also the fields and not only the new stuff but also all the existing stuff... and I didn't want to do that in phpMySql manually for days... so I looked for code and found something which I adpated to my needs...

I thought some of you might find it usefull:

Code: Select all

<?php
$db = mysqli_connect('localhost','username','password', 'database');
if(mysqli_connect_errno())
{
echo "Cannot connect to the database - incorrect details";
}


$sql = 'SHOW TABLES';
if ( !( $result = $db->query( $sql ) ) ) {
echo '<span style="color: red;">Get SHOW TABLE - SQL Error: <br>' . "</span>n";
}


while ( $tables = $result->fetch_row() ) {
echo $tables[0];
# Loop through all tables in this database
$table = $tables[key($tables)];

if ( !( $result2 = $db->query("ALTER TABLE ".$table." COLLATE utf8_general_ci") ) ) {
echo '<span style="color: red;">UTF SET - SQL Error: <br>' . "</span>n";

break;
}

print "$table changed to UTF-8 successfully.<br>n";

# Now loop through all the fields within this table
if ( !($result2 = $db->query("SHOW COLUMNS FROM ".$table) ) ) {
echo '<span style="color: red;">Get Table Columns Query - SQL Error: <br>' . "</span>n";

break;
}

while ( $column = $result2->fetch_assoc() )
{
$field_name = $column['Field'];
$field_type = $column['Type'];

# Change text based fields
$skipped_field_types = array('char', 'text', 'enum', 'set');

foreach ( $skipped_field_types as $type )
{
if ( strpos($field_type, $type) !== false )
{
$sql4 = "ALTER TABLE $table CHANGE `$field_name` `$field_name` $field_type CHARACTER SET utf8 COLLATE utf8_general_ci";
$result4 = $db->query($sql4);

echo "---- $field_name changed to UTF-8 successfully.<br>n";
}
}
}
echo "<hr>n";
}
$result->free();
$result2->free();
$result4->free();
?> 

mortal
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Mon Mar 24, 2008 3:20 pm

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by mortal » Mon Mar 24, 2008 3:49 pm

Hey all,

first of all thanks to snock... ur post gave me a soloution to a big problem. After i has set up the whole Site in german,english and arabic a friend told me that the site looks not ok in IE 6 and 7 . When choosing arabic Language some transloations were not arabic ... after a while i found out, that ie is not changing coding. I didnt realize that, becouse i never use ie and had no probs with firefox.

So 4 al people having display problems (coding) with arabic in IE just delete this ">" in ur index.php in ur used template and evrything is working fine :)

great job


greets

cchevy365
Joomla! Intern
Joomla! Intern
Posts: 57
Joined: Wed Aug 30, 2006 4:11 am

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by cchevy365 » Mon Apr 14, 2008 2:34 pm

It's like this...I have time and date of creation hidden, but when I hit the ENG translation, it is displayed on content items on frontpage. Also, pathway HOME is displayed. What file do I configure?

feritG
Joomla! Apprentice
Joomla! Apprentice
Posts: 6
Joined: Fri Dec 04, 2009 9:52 pm

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by feritG » Fri Dec 04, 2009 9:55 pm

tip says do it before installing the joomla.
i've already been installed and i didn't had any character problem till now. but now i set fireboard up at my site and it doesn't work fine. letters "ı" "ş" doesn't work. isn't there any solution for already installed sites?

User avatar
infograf768
Joomla! Master
Joomla! Master
Posts: 19133
Joined: Fri Aug 12, 2005 3:47 pm
Location: **Translation Matters**

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by infograf768 » Sat Dec 05, 2009 8:12 am

feritG wrote:tip says do it before installing the joomla.
i've already been installed and i didn't had any character problem till now. but now i set fireboard up at my site and it doesn't work fine. letters "ı" "ş" doesn't work. isn't there any solution for already installed sites?
As far as I remember this due to Fireboard wrong language files encoding. They should be UTF8 No BOM.
Jean-Marie Simonet / infograf
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group

feritG
Joomla! Apprentice
Joomla! Apprentice
Posts: 6
Joined: Fri Dec 04, 2009 9:52 pm

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by feritG » Sat Dec 05, 2009 2:50 pm

how can i turn them to utf8? i'm kinda noob at these things. ;)

User avatar
infograf768
Joomla! Master
Joomla! Master
Posts: 19133
Joined: Fri Aug 12, 2005 3:47 pm
Location: **Translation Matters**

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by infograf768 » Sat Dec 05, 2009 5:53 pm

Open them in an utf8 aware Text editor (Notepad++ on Windows, TextWrangler on Macintosh) and save with the right encoding
Jean-Marie Simonet / infograf
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group

feritG
Joomla! Apprentice
Joomla! Apprentice
Posts: 6
Joined: Fri Dec 04, 2009 9:52 pm

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by feritG » Sat Dec 05, 2009 6:53 pm

the language file (turkish.php)(added to message) is already utf8?
You do not have the required permissions to view the files attached to this post.

User avatar
infograf768
Joomla! Master
Joomla! Master
Posts: 19133
Joined: Fri Aug 12, 2005 3:47 pm
Location: **Translation Matters**

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by infograf768 » Sun Dec 06, 2009 5:55 am

It was, but with BOM.
Try this one.
turkish.php.tgz
You do not have the required permissions to view the files attached to this post.
Jean-Marie Simonet / infograf
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group

feritG
Joomla! Apprentice
Joomla! Apprentice
Posts: 6
Joined: Fri Dec 04, 2009 9:52 pm

Re: Joom!Fish, encodings and UTF-8 - How to or Newies read first

Post by feritG » Sun Dec 06, 2009 9:34 am

thank you but i still tkae this messaage when i try to write message with turkish letters

Code: Select all

Serious db problem:Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' SQL=SELECT id FROM jos_fb_messages JOIN jos_fb_messages_text ON id=mesid WHERE userid=62 AND name='admin' AND email='[email protected]' AND subject='ışğüçö' AND ip='78.172.162.69' AND message='ış' AND time>='1260090206'


Locked

Return to “Joom!Fish and Multilingual Sites”