Multilingual Database Application

[eluser]Federico Gonzalez[/eluser]
Hi,

I need to do a multilingual application. I know the Language Class, but.. How can I must to manage the database (mysql)?

Could you give me a simple example, please?

Thanks! Federico
Ushuaia, Argentina

[eluser]Isern Palaus[/eluser]
Hello,

Read my reply to davidino86, it can be useful.
http://ellislab.com/forums/viewthread/146018/#713596

Wish it helps,
Isern

[eluser]parvendra[/eluser]
Hi Federico,

To do mutilingual in database.
here are the steps

1. create a database with UTF8 encoding.
2. capture input from UI, encode that into UTF8 and store in database.
3. while displaying decode the content back to text and display on browser simply.
4. while displaying ,make the browser UTF8 encoding by setting the header.


Thanks
Parvendra

[eluser]Federico Gonzalez[/eluser]
Parvendra, Isern Palaus,

Thanks for your replies!

Parvendra: My question is for conveniences in database model; I haven't coding problems.

Isern: Your reply to davidino86 works for me, but in idioma Table you are repeating some fields like fecha. Tath field isn't necessary to repeat. I'll write you in davidino86 post. Do you speak spanish?

Federico

[eluser]gvillavizar[/eluser]
I have a problem with the approach to this one, and those are the id's in the database.

If you have that structure, then each article in each language will have it's own id, and that means when you call:

Code:

public function get_new($idnews = 1, $ididioma = 1)
{
    $this->db->from('news')
             ->join('news_description','news.idnews = news_description.news_idnews','left')
             ->where('news_description.idioma_ididioma', $ididioma)
             ->where('news.idnews', $idproducto)
             ->limit(1);
    
    return $this->db->get();
}

If I change the language while reading an article (for any reason, maybe I didn't noticed the languages when i reach that page and now i noticed and want to change it to another one) then the id you are passing is wrong, because there is no new with id = x and idioma = y.

I don't know if this is the best approach there is, but I usually just have the database extended while creating more articles, either by having the same records twice in the same table. Example:

Code:

news
=========
id_news
text_en
text_es

OR by having the same table for each language.

Code:

news_es
=========
id_news
text

news_en
=========
id_news
text

This is just an example of my opinion on this, hope you find it useful.

[eluser]parvendra[/eluser]
Hi Federico,

I do not speak Spanish .Please write in English. I would help u out till my extent.
here is simple example
receive the data from form and convert it by utf8_encode('YOURDATAHERE');
and save into utf8 database.


Thanks
Parvendra

[eluser]n0xie[/eluser]

I don’t know if this is the best approach there is, but I usually just have the database extended while creating more articles, either by having the same records twice in the same table.

In your case it would make more sense to just use joins. If you have an article which has a translation of it, this makes more sense:

Code:

tbl_news
=====
news_id
title
slug // this is where you usually identify the article by if you want SEO friendly url's
...

tbl_news_article
=====
news_article_id
news_id
language_id
article
...

Point is that you have 1 news 'item' which has 1 to many translations. Now if you join the two tables with a where clause for the current language, you always return the right article.

Sidenote: instead of using news_article_id as primary key, you could opt to use news_id and language_id as a combined primary key. This is the more DBA way to do it, but I'm not really a fan of it tbh.

[eluser]Isern Palaus[/eluser]
[quote author="Federico Gonzalez" date="1269535066"]
Isern: Your reply to davidino86 works for me, but in idioma Table you are repeating some fields like fecha. Tath field isn't necessary to repeat. I'll write you in davidino86 post. Do you speak spanish?[/quote]

Hello Federico,

Yes, I speak Spanish and I try to write in English hehe.

I've a field 'fecha' on 'database' only to control the date has been added. They are needed for nothing more, only to control. I always use a time and time modified to control if a client has modified and then come to you and says: "No, I didn't edited. It was automatic."

Wish it help you to understand.

Isern

[eluser]Isern Palaus[/eluser]
Hello gvillavizar,

[quote author="gvillavizar" date="1269541031"]I have a problem with the approach to this one, and those are the id's in the database.

If you have that structure, then each article in each language will have it's own id, and that means when you call:

Code:

public function get_new($idnews = 1, $ididioma = 1)
{
    $this->db->from('news')
             ->join('news_description','news.idnews = news_description.news_idnews','left')
             ->where('news_description.idioma_ididioma', $ididioma)
             ->where('news.idnews', $idproducto)
             ->limit(1);
    
    return $this->db->get();
}

If I change the language while reading an article (for any reason, maybe I didn't noticed the languages when i reach that page and now i noticed and want to change it to another one) then the id you are passing is wrong, because there is no new with id = x and idioma = y.[/quote]

You're wrong. Every new will have a unique id. A new will respond to ID but if you look for this ID in the news_description (where there are the title, text, etc. in other langauges) you will find various records. Then you have look for a idnew and a idlanguage in news_description to get the correct language.

[quote author="gvillavizar" date="1269541031"]
I don't know if this is the best approach there is, but I usually just have the database extended while creating more articles, either by having the same records twice in the same table. Example:

Code:

news
=========
id_news
text_en
text_es

OR by having the same table for each language.

Code:

news_es
=========
id_news
text

news_en
=========
id_news
text

This is just an example of my opinion on this, hope you find it useful.[/quote]

I think that this is a bad practice. What I was looking for when I finally split my multilanguage contents in database in news, news_description; products, products_description... is to be scalable. What will yo dou if you need to add a language in a large scale application like a e-commerce system? You will need to rewrite aaaaaaall the queries and have one query for each language. This is not usable, I think.

Use news_es/news_en will be fastest at the first attempt but when you've all spplited you will see that is the way to do !

Thanks!!

[eluser]Federico Gonzalez[/eluser]
Hi,

I agree with Isern Palaus, but if you table is a lot more complex, you must split only the multilanguage texts.

Example:

products
--------
product_id
category_id
price
weight
size

products_texts
--------------
product_id
language_id
title
description

You need to translate only text fields, because dates and numbers have no language

Saludos! Federico