Multilingual Development (with PHP)
acoleman616
I'm currently attempting to prepare my site for the possibilities of
multilingual support in the future. For translations between
different languages, I tend to use some sort of implementation such as
Zend_Translate, which allows the user to set their language, and then
using a text-based file for the appropriate language, a PHP wrapper
function performs the translation of the text.
My question regarding MySQL is as follows. My web app has a few
tables along the lines of "categories", which store a list of pre-set
categories such as "business", "sports", "technology", etc. Would I
need to create extra DB tables to store translations of such terms in
different languages? Or would I be able to use the same technique
detailed above, and perform the translations *after* the English
values have already been fetched from the database into a PHP script?
I realize I can have MySQL store column values in different languages
(such as for users' names), but for fixed-value tables such as
"categories", I'm unsure about the proper implementation techniques
for multilingual support.
Any help is greatly appreciated, thanks.
Robert Hairgrove
I would store the translations in the database. However, creating one
table per language is probably not the way to go. You will need to make
sure that your DB design is properly normalized in order to be able to
support additional languages in the future.
Are the category terms also used as foreign keys in related tables?
Some more details on the existing schema design would be nice.
Johannes Keßler
Hash: SHA1
hello,
also you need a base to start the translation.
You can either use special keys which you look up in the translation table, but
this makes the code sometimes unreadable.
Or you can use a language as the key. Eg. english is the default language, then
use the english words as the key to look up the translation. This will prevent
to find keys which need to be unique.
regards,
johannes keßler
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.13 (GNU/Linux)
iEYEARECAAYFAksKRAoACgkQE++2Zdc7EtdNggCeIrWZCW3GQADjYwNXSu6I1hjw
jccAn33ok8WwwAHJqs4ePiz8d4jWxjCJ
=2ar7
-----END PGP SIGNATURE-----
acoleman616
wrote:
Thanks for the responses. As of right now, just to provide a
simplified example, assume the categories table has just two columns:
category_id and name. All of the 'name' values are currently stored
in english. Would an ideal solution be to, for each additional
language, create a new table - so for French, for example, create a
categories_fr table - which has two columns: name_key and name, where
categories.name=categories_fr.name_key ? That way, I can use the
existing English names as keys to look up the proper translations.
Johannes, I believe this is what you were suggesting, correct?
Jerry Stuckle
> Hi all,
>
> I'm currently attempting to prepare my site for the possibilities of
> multilingual support in the future. For translations between
> different languages, I tend to use some sort of implementation such as
> Zend_Translate, which allows the user to set their language, and then
> using a text-based file for the appropriate language, a PHP wrapper
> function performs the translation of the text.
>
A suggestion - don't even try to use an automated translation. The
results will always be poor - and it will be obvious to all. Get
someone to do the translations for you - preferably a native speaker of
the target language.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
acoleman616
> ==================
Right, understood. Sorry if I gave the wrong idea originally, but I
definitely don't plan to use automated translation.
Robert - sorry, I didn't fully respond to you before in terms of the
schema. The category ID's are are currently used as foreign keys in
related tables, but not the category names.
What about this alternative solution in terms of database structure:
just adding an extra column to the categories table for each new
language which provides the proper translation - for example, add a
name_fr column for the French translation - as opposed to creating new
tables for each new language. Would this be a better/worse solution?
Erick T. Barkhuis
> That way, I can use the
> existing English names as keys to look up the proper translations.
A bit as an aside: you will run into a few other details, that your
application must address:
1. Some terms will be used both within a sentence and at the start of a
sentence. Your application object/function will need a parameter, so it
knows whether or not an upper case for the first character is necessary.
2. Some terms will be used both as html and as plain text (in mails),
which may require a different form, especially if you allow quotes and
such in phrases. Your application object will require a parameter which
says 'html' or 'text', so it returns stuff like html entities if
necessary.
3. Plenty of stuff will probably NOT come from your database, and you
will need an additional translation table. Comes to mind: error
messages, html <title> elements, title/alt attributes for images and
perhaps mail contents.
When I developed a bi-lingual application for an educational
organization several months ago, I introduced "language packs", which
basically are large arrays with phrases, each tight to an array
entry...for each language a separate array. The application simply gets
the term from the proper language pack. This way, It's quite trivial to
add additional languages. The programming has been done...all you need
to do in that case is have someone fill the array with the proper
phrases for that new language.
--
Erick
"You spend your whole life believing that you're on the right track,
only to discover that you're on the wrong train."
acoleman616
acoleman616
wrote:
Great points, Erick, thanks. Did you use said "language packs" for
all of your translations throughout the entire site, or did you use
databases to store some translations as well?
Great point in terms of things like error messages. I suppose
something similar to your language packs may be useful in such a
situation. A lot of my error messages will be rendered via JavaScript
code, so I suppose I'd have to have some sort of JavaScript "language
pack" to perform proper translations. As for error messages (and
similar phrases) generated by backend PHP scripts, would you recommend
the same strategy of storing translations in the database, as
discussed for my "categories" previously?
acoleman616
wrote:
Great points, Erick, thanks. Did you use said "language packs" for
Erick T. Barkhuis
>Great points, Erick, thanks. Did you use said "language packs" for
>all of your translations throughout the entire site, or did you use
>databases to store some translations as well?
The application manages courses and lectures. Everything that is stored
in the database (categories, course descriptions, course series,
teachers, locations) can be edited by school personell. These data are
in the database (e.g. multiple 'course title' fields, one for each
language...yes, that would still require a database change when
languages are added)
Other (more static) text elements are in the language pack. Error
messages, welcome texts, page titles, form fields (enrolment forms and
paypal messages), you name it.
So, you could expect something like
$langitem['dearsirs'] = "dear Ladies and Gennamin";
$langitem['savebutton'] = "save";
in the english language pack, and
$langitem['dearsirs'] = "geachte dames en heren";
$langitem['savebutton'] = "opslaan";
in de dutch language pack.
>
>As for error messages (and similar phrases) generated by backend PHP
>scripts, would you recommend the same strategy of storing translations
>in the database, as discussed for my "categories" previously?
I can't quickly see another way to do it. The database elements and the
"general elements" are separate things. Of course, what I did with
arrays can also be done with database tables. That way, you could
create an admin system to manage the language packs.
In the application I mentioned, that was just one bridge too far for
the poor office people. Since the content of the language packs will
not be subject to change very often, I simply instructed one person to
manage the arrays, and wrote a cron job which creates language pack
backups once a week, just to be sure nothing gets really messed up.
To be honest, though: at the organization, the office people simply
don't have the time to maintain a full bi-lingual website with hundreds
of courses, workshops and lectures. For most courses, they only entered
one language version in the system, and filled the other language with
something like "This course description is available in dutch, only".
The bi-lingual idea came from the school manager, who didn't realize
that his staff wouldn't be able to handle the constant stream of
translations required. I still think the idea was OK (because many
foreign students enroll), but the Real World appears to be slightly
different.
--
Erick