How Are You Accomplishing Your i18n?

cobrabyte asks: "My team has recently been given the task of implementing internationalization (i18n) in our MySQL databases (PHP-interfaced). Essentially, for every article X, we need it presented in any number of languages (once translated). As we were working on gathering the necessary procedures, we were very surprised to find that there's not much organized information regarding i18n using MySQL and PHP. Is the topic of i18n too new to garner any usable info?"

Comments

Have your looked at PEAR?

[33degrees]

I haven't tried any of them, but PEAR has a number of packages for dealing with internationalization. You might want to try looking there for insight.

Easy way, using SQL

[jd]

Simply define a strings table with two key fields. The first key defines the string ID, the second defines the language ID. The sole attribute would then be the string itself.

Adding a new language then just becomes a case of adding a new language ID to the system, and adding a new string becomes adding a string ID.

Any place that you want to generate an output string, simply insert a token which represents the string ID. Your translation code scans for the tokens, gets the current language from the environment, and then searches your strings table for the substitution string.

(For those who remember the Commodore PET computer, this is very similar to how it worked. The Print command, for example, was stored internally as a "?" token. It substituted when displaying.)

You do not need a table for the string IDs, an enumerated type would be sufficient to track what IDs are in use and what for. You WOULD want a table for the language, with the language ID as the key field (preferably as an enumerated type) and the font ID as the attribute. If you are not using fonts (eg: plain-text output) then again you can just use the enumerated type.

Because you would NOT be encoding font data into the string (NEVER, EVER do that, by the way, as you're just padding the data with redundant information, and introducing extra complexity), you can replace the font at will, provided it conforms to the mapping standards for international character sets.

i18nHTML

[Mind Booster Noori]

Well, I think you're looking for this.

Re:What's the question?

[plcurechax]

-without using some crappy 'BabelFish' layer

Ask any government that supports multiple official languages (Canada, Switzerland, ...). You translate into the other language(s) using professional translators. Period. You can give them the most powerful automatic translation tools available, and multiple language dictionarys (e.g. English-French) but in the end you need a human professional translator to make translations worth reading.

-without having to write a complete localized version for each language.

You need to make the content management system (CMS) language aware, and you need to localize all your templates. Then you need to add a key to your article database for language, so the user can retrieve article 101 in either english or french. (think a long the lines of http://localhost/cms/display.php?article=101&lang= en ).

I know nothing about PHP programming, so I cannot comment on that, or MySQL (main gotcha I expect is datatype, UTF-8, iso8859-1, vs. windowspage1574). Two articles I found useful in general about internationalization are

UTF-8 and Unicode FAQ for Unix/Linux by Markus Kahn
How do I have to modify my software?
http://www.cl.cam.ac.uk/~mgk25/unicode.html#mod

The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)
http://www.joelonsoftware.com/articles/Unicode.htm l

Here's a link for Rails

[Anonymous Coward]

I found the following Rails article quite helpful:

http://manuals.rubyonrails.com/read/chapter/82

In particular it links to the following:

http://www.quepublishing.com/articles/printerfrien dly.asp?p=328641&rl=1

Which is a very good discussion of characters sets in MySQL. I didn't realize it was so thorough. For instance you can have different character sets on tables, connections, and the server itself. Finally, it seems MySQL got something right. :-)

Smarty + preparse plugin

[DamienMcKenna]

I did this in 2003 for a CMS+ecommerse system I did for a company. You had Smarty templates which had things like {productstr1} in them. The text strings were referenced by language and string ID, and if the string didn't have a specific version for your language it defaulted to English. This string was loaded from the database in a preparse plugin and was cached in a per-language directory. It worked ok, a bit kludgy but sufficient to get the job done.

Damien

Some answers

[JavaRob]

I18n/localization is one of those tasks that has *lots* of questions that will need to be resolved... often you won't even know about all of the issues to resolve until you start digging into it.

I sorted out the i18n design for a project recently, so I can share some insights on the process. My project used Java/JSP, but the problems are mostly the same. One of the most important points to be made is that you *need* to sit down and design it all the way through -- this is not a "feature" that can be easily added in when you need it later (and extreme programming teams can get hosed on this one pretty easily).

Things to consider (in the sequence of a request for simplicity's sake):
1) How will you know what language a user wants (first time, and on subsequent pages)? The user should be able to select/change their preference (though you could use their browser-reported locale as a guess), and they should be able to *bookmark* the homepage in their language. You could use a cookie, and redirect from the basic homepage based on that. Personally I avoid depending on cookies where possible, I didn't want to have duplicated directory structures, and I didn't want an added param on every request, so I used multiple *subdomains*, one per supported locale. They all mapped to the same IP, same application -- but in the web application I could check the requested URL and set the locale (and build the page) correctly using that. There were links on the top of the homepage to switch languages -- which would just flip to the proper subdomain. (Important note -- this complicates getting a cert for SSL, since that's tied to the domain... keep that in mind).

Once you know what language you're using, build the page... this will probably involve getting data out of the database and displaying some of it.

First, make sure your tables support whatever character set the languages will need. Then make your data design carefully. You need to make sure that any data in the database that will show up onscreen: product descriptions, category names, and ALSO prices (you probably have to give prices in various currencies, right?).

Building the page -- you'll need more PHP-specific advice here, but the idea is that you need to get text and possibly images that are language-specific for each page. The general choices are:
* Use a single PHP file for the content (e.g., a form for registration info), and get the text displayed from locale-specific files (so for the "name" label over that field, you'd grab the proper translation).
* Maintain a separate PHP file for the content in each language, plug the proper one into the template.

The first option is better if your content is mostly short bits of text -- but if there are larger chunks of text it gets hard to read (and if the whole page is text -- like a privacy policy page, etc. -- the second option may make more sense). Personally, I supported both options.

What else? Don't forget that formatting of currency, numbers, dates, and times will vary by locale. Don't forget to review any Flash animations, dropdown menus, popup calendars, etc.. these will need to support changes based on locale. Organize your resources carefully, so that a simple substitution in the path will get you the right image, content file, etc. (e.g., images/fr_CA/whatever.gif).

HTH.