Several languages for content

--00032555a272aebbea04795c0816
Content-Type: text/plain; charset=ISO-8859-1

Hi,

This is my first post, and could use some points of view on the subject.
Have a project that will have several languages for its records, for places,
for example, whose names and descriptions will be in 3 languages.

Have already read several sites about it but always end up with several
perspectives:

1 - Tables for each language: places_pt, places_en, places_es, with fields:
id, name, description; Seems superfluous, but will leave less records on
each table, although adding a great amount of tables, the more languages i
have;

2 - Rows for each language: id, lang, name, description - the field "lang"
will have "pt", "en" or "es", and when displaying, i will have to lookup the
active language and get the proper record; Will add tremendously to the
record #;

3 - Fields for each language: table "places", with fields: id, name_pt,
name_en, name_es, description_pt,description_en,description_es - not likely
to add a new language, but still adding new field adds complexity.

Is there a "proper" way to do things? Any usual way of handling several
languages on a project? Any help is highly appreciated, thanks.

MV

--00032555a272aebbea04795c0816--

Re: Several languages for content

On Nov 27, 2009, at 4:34 PM, Miguel Vaz wrote:
> This is my first post, and could use some points of view on the =
subject.
> Have a project that will have several languages for its records, for =
places,
> for example, whose names and descriptions will be in 3 languages.
>=20
> Have already read several sites about it but always end up with =
several
> perspectives:
>=20
> 1 - Tables for each language: places_pt, places_en, places_es, with =
fields:
> id, name, description; Seems superfluous, but will leave less records =
on
> each table, although adding a great amount of tables, the more =
languages i
> have;
>=20
> 2 - Rows for each language: id, lang, name, description - the field =
"lang"
> will have "pt", "en" or "es", and when displaying, i will have to =
lookup the
> active language and get the proper record; Will add tremendously to =
the
> record #;
>=20
> 3 - Fields for each language: table "places", with fields: id, =
name_pt,
> name_en, name_es, description_pt,description_en,description_es - not =
likely
> to add a new language, but still adding new field adds complexity.
>=20
> Is there a "proper" way to do things? Any usual way of handling =
several
> languages on a project? Any help is highly appreciated, thanks.

This really depends on whether you have all content available in all =
languages. And if not, how you want to fall back to other languages. =
And how often you add new languages.



Liz=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Several languages for content

--0016e6d99cca93b6ce04795c9153
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I am really interested in the same answer...

I am making a script.. and as I will only have 2 languages I decided to use
you 3rd option (Fields for each language), because of less rows.

But I really would like to know if there is a better approach to this.

*eg. php function:*

function getRecipeName($id) {
global $database;
$query =3D mysql_query("SELECT name_english,name_spanish
FROM ".$database['prefix']."recipes
WHERE id =3D '$id'
LIMIT 1
") or die(mysql_error());

$RECIPES =3D mysql_fetch_object($query);

if (ACTIVE_LANG == "spanish" && $RECIPES->name_spanish !=3D "") {
$return =3D "".$RECIPES->name_spanish.""; }
elseif (ACTIVE_LANG == "spanish" && $RECIPES->name_spanish == "")=
{
$return =3D "".$RECIPES->name_english." [S=F3lo ingl=E9s disponible]"; }
elseif (ACTIVE_LANG == "english" && $RECIPES->name_english !=3D "") {
$return =3D "".$RECIPES->name_english.""; }
else {$return =3D "".$RECIPES->name_spanish." [Only spanish available]"=
; }

return $return;
}


Engel



2009/11/27 Elizabeth Mattijsen

> On Nov 27, 2009, at 4:34 PM, Miguel Vaz wrote:
> > This is my first post, and could use some points of view on the subject=
..
> > Have a project that will have several languages for its records, for
> places,
> > for example, whose names and descriptions will be in 3 languages.
> >
> > Have already read several sites about it but always end up with several
> > perspectives:
> >
> > 1 - Tables for each language: places_pt, places_en, places_es, with
> fields:
> > id, name, description; Seems superfluous, but will leave less records o=
n
> > each table, although adding a great amount of tables, the more language=
s
> i
> > have;
> >
> > 2 - Rows for each language: id, lang, name, description - the field
> "lang"
> > will have "pt", "en" or "es", and when displaying, i will have to looku=
p
> the
> > active language and get the proper record; Will add tremendously to the
> > record #;
> >
> > 3 - Fields for each language: table "places", with fields: id, name_pt,
> > name_en, name_es, description_pt,description_en,description_es - not
> likely
> > to add a new language, but still adding new field adds complexity.
> >
> > Is there a "proper" way to do things? Any usual way of handling several
> > languages on a project? Any help is highly appreciated, thanks.
>
> This really depends on whether you have all content available in all
> languages. And if not, how you want to fall back to other languages. An=
d
> how often you add new languages.
>
>
>
> Liz
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dengel@solunion.co=
m
>
>


--=20
Engel Sanchez
Solunion Group || Esfera Digital
www.solunion.com || www.esferadigital.com
E-mail: engel@solunion.com

--0016e6d99cca93b6ce04795c9153--

Re: Several languages for content

--00032555a4e63d576c04795d765b
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

My 3rd option seems to be the most simple, although i admit it doesnt sound
quite right. Having fixed table fields for languages doesnt seem correct.

I can see disavantages in all 3 options, thats why i wanted to ask
everybody's opinion on something like this, i mean, a assume that dealing
with a multi language project is something that everybody will come across
eventually, right? :-)


Regarding your case, Engel, i would go for something like session or cookie=
,
thatsets a "global" var, so you can do the query like this:

$active_lang =3D $_SESSION...whatever; // will be "en" or "es"

$query=3D"select name_".$active_lang." name from recipes";

$res =3D mysql_query($query) or die("blah blah");

.... retrieve always the field named "name", which will always be in your
active language. Hope it helps. :-)

MV


On Fri, Nov 27, 2009 at 4:13 PM, Engel Sanchez wrote:

> I am really interested in the same answer...
>
> I am making a script.. and as I will only have 2 languages I decided to u=
se
> you 3rd option (Fields for each language), because of less rows.
>
> But I really would like to know if there is a better approach to this.
>
> *eg. php function:*
>
> function getRecipeName($id) {
> global $database;
> $query =3D mysql_query("SELECT name_english,name_spanish
> FROM ".$database['prefix']."recipes
> WHERE id =3D '$id'
> LIMIT 1
> ") or die(mysql_error());
>
> $RECIPES =3D mysql_fetch_object($query);
>
> if (ACTIVE_LANG == "spanish" && $RECIPES->name_spanish !=3D "") =
{
> $return =3D "".$RECIPES->name_spanish.""; }
> elseif (ACTIVE_LANG == "spanish" && $RECIPES->name_spanish == ""=
) {
> $return =3D "".$RECIPES->name_english." [S=F3lo ingl=E9s disponible]"; }
> elseif (ACTIVE_LANG == "english" && $RECIPES->name_english !=3D "") =
{
> $return =3D "".$RECIPES->name_english.""; }
> else {$return =3D "".$RECIPES->name_spanish." [Only spanish available]=
"; }
>
> return $return;
> }
>
>
> Engel
>
>
>
> 2009/11/27 Elizabeth Mattijsen
>
> > On Nov 27, 2009, at 4:34 PM, Miguel Vaz wrote:
> > > This is my first post, and could use some points of view on the
> subject.
> > > Have a project that will have several languages for its records, for
> > places,
> > > for example, whose names and descriptions will be in 3 languages.
> > >
> > > Have already read several sites about it but always end up with sever=
al
> > > perspectives:
> > >
> > > 1 - Tables for each language: places_pt, places_en, places_es, with
> > fields:
> > > id, name, description; Seems superfluous, but will leave less records
> on
> > > each table, although adding a great amount of tables, the more
> languages
> > i
> > > have;
> > >
> > > 2 - Rows for each language: id, lang, name, description - the field
> > "lang"
> > > will have "pt", "en" or "es", and when displaying, i will have to
> lookup
> > the
> > > active language and get the proper record; Will add tremendously to t=
he
> > > record #;
> > >
> > > 3 - Fields for each language: table "places", with fields: id, name_p=
t,
> > > name_en, name_es, description_pt,description_en,description_es - not
> > likely
> > > to add a new language, but still adding new field adds complexity.
> > >
> > > Is there a "proper" way to do things? Any usual way of handling sever=
al
> > > languages on a project? Any help is highly appreciated, thanks.
> >
> > This really depends on whether you have all content available in all
> > languages. And if not, how you want to fall back to other languages.
> And
> > how often you add new languages.
> >
> >
> >
> > Liz
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dengel@solunion.=
com
> >
> >
>
>
> --
> Engel Sanchez
> Solunion Group || Esfera Digital
> www.solunion.com || www.esferadigital.com
> E-mail: engel@solunion.com
>

--00032555a4e63d576c04795d765b--

Re: Several languages for content

On Nov 27, 2009, at 6:17 PM, Miguel Vaz wrote:
> My 3rd option seems to be the most simple, although i admit it doesnt =
sound
> quite right. Having fixed table fields for languages doesnt seem =
correct.

Well, it all depends. If you need to add a column later, you need to do =
an ALTER TABLE. In a production environment, that may be killing (do =
you want your website to be down for 30 minutes why you do an ALTER =
TABLE ?? ).

Then again, if that table isn't updated a lot, you could build another =
version with the extra column, and do an atomic rename to put it in =
place.

It all depends on your environment...


Liz=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Several languages for content

On Fri, 27 Nov 2009 15:34:56 +0000 Miguel Vaz said:

> Hi,
>
> This is my first post, and could use some points of view on the subject.
> Have a project that will have several languages for its records, for places,
> for example, whose names and descriptions will be in 3 languages.
>
> Have already read several sites about it but always end up with several
> perspectives:
>
> 1 - Tables for each language: places_pt, places_en, places_es, with fields:
> id, name, description; Seems superfluous, but will leave less records on
> each table, although adding a great amount of tables, the more languages i
> have;
>
> 2 - Rows for each language: id, lang, name, description - the field "lang"
> will have "pt", "en" or "es", and when displaying, i will have to lookup the
> active language and get the proper record; Will add tremendously to the
> record #;
>
> 3 - Fields for each language: table "places", with fields: id, name_pt,
> name_en, name_es, description_pt,description_en,description_es - not likely
> to add a new language, but still adding new field adds complexity.
>
> Is there a "proper" way to do things? Any usual way of handling several
> languages on a project? Any help is highly appreciated, thanks.
>
> MV

Back when I internationalized my site, I used a table thus:

| locale | varchar(5) | NO | PRI | en | |
| strid | varchar(127) | NO | PRI | | |
| strval | tinyblob | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
and populated it w/ the english phrases first:
('en', 'Country', 'Country')
('en', 'Total', 'Total')
('en', 'Day', 'Day')
.... etc.

Then I added different languages:
('de', 'Country', 'Land')
('de', 'Total', 'Summen')
('de', 'Day', 'Tag') ...

When the user logs in, it scans all the 'en' (english) tokens and stores
it in a PHP array('Day' => 'Day', 'Total' => 'Total' ...

Once you figure out the user's locale (either from a user table or
parsing the http headers) you re-scan using the proper locale,
modifying the array as necessary: 'Day' => 'Tag', 'Total' =>
'Summen' ...

That way if you don't know or haven't yet found the proper translation
for a phrase it will default to the english language.

* Note the locale field is varchar(5).
There is a de, a de_DE, and a de_CH locale!

Have fun!

--
Don Read don_read@att.net
It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org