i18n best practice

@Sid3335May 22.2009

Hi guys,

Wondered if anyone has some good advice regarding internationalization.

i’ll be using php and mysql and i want the user to supply all the copy (no translation tools)

if i have a product, should i replicate the table with a prefix?
en_product
fr_product…etc

that seems a bit of a chore considering the number fo tables i could possibly have.

I could also store the fields as a serialized array but again doesn’t seem the best way to me.

any advice greatly appreciated.

to post a comment

PHP

8 Comments(s) _↴

@MindzaiMay 22.2009 — #You'll probably find it far quicker and easier to maintain language files for translation. You just need to store flat files with key/value pairs where the English (or default language) is the key and the translation is the language, so for example you might have a line like this in a fr file:

<i>
 </i>buy now=achat maintenant

Then when you echo something in your script, you have a function which you pass the English default as an argument. If no alternative language is selected, it just echoes out the key directly, otherwise it looks up the corresponding value in the relevant language file and uses that instead.

By convention this function is usually called __ (two underscores) as it is pretty unobtrusive.

[code=php]echo __('buy now'); // outputs 'buy now' if english or no language selected, 'achat maintenant' if french selected[/code]

@Sid3335authorMay 23.2009 — #thanks for the reply, however...

I don't see this as a viable option for 10,000 product descriptions, fine for a site without dynamic data or a simple system.

I've decided (unless there are better suggestions) to go for a single language table where strings of data are stored and referenced from other tables by id.

therefore when a product is requested in english it will ook for a product_id/en pair.

@MindzaiMay 23.2009 — #I dont see how that's any different except this way you have the overhead of a database query?

@NogDogMay 23.2009 — #I think I might use the language files approach for the "boiler plate" parts of the pages (the general framework of the page that does not really vary).

For the product database, I might consider creating separate tables for each part of the product specification that might change by language; for instance a product_name table and a product_description table. Such a table might be structured as:

<i>
 </i>product_name
 =============
 id (int, primary key)
 product_id (int, points to main product table)
 language_id (int, points to language table)
 name (varchar, name of product in this language)

You could do the same thing for product_description, then your query might be something like:

[code=php]
 $sql = "
 SELECT p.*, pn.name, pd.description
 FROM product AS p
 INNER JOIN product_name AS pn ON pn.product_id = p.id
 INNER JOIN product_description AS pd ON pd.product_id = p.id
 WHERE 
 p.id = $id AND
 pn.language_id = $lang_id AND
 pd.language_id = $lang_id
 ";
 [/code]

@Sid3335authorMay 23.2009 — #Hi Guys,

Thanks for the replys.

Nog Dog:

Good Idea, only problem i see with that is what if i have many fields. I'd be doing the same for categories, manufacturers, promotions. It might get really messy.

if i had a single table:

<i>
 </i>translation_table
 =============
 id (int, primary key)
 table (var, points to table (product, category, etc))
 field_name (var, points to field in table (product_desc, product_name etc))
 language_id (int, points to language table)
 translation (longtext, replacement translation)

do you think that would work? seems to work in my head, but again, bit messy.

Another issue i have to consider is the fact i want to version everything too. might just be able to add a last_modified field in and use the latest one.

i've decided that if a translation isn't available i'm going to fall back to using google translate which i've just found is incredibly easy to access with php via the API.

@Sid3335authorMay 23.2009 — #I may revisit the serialized array storage method after reading that back.

i always start with simplicity in mind. ?

@NogDogMay 23.2009 — #As I think about it, there's no real reason I can see to have a separate table for each field, just one separate table to collect all the fields that vary by language. So the main product table might have product serial number, price, weight, etc. -- all the things that are the same regardless of language. Then you could have another table with a field for each textual item that could vary by language: name, description, comments, etc. Then you would only have to join on the main product table and the matching row in the "language" table that matches that product id and the desired language id.

@MindzaiMay 23.2009 — #I suppose one advantage of that approach is that the link is by PK not the phrase itself so updating the phrase in the default language (to fix a typo or something) is not going to require editing the language files. I guess you are also querying the database anyway for the product, but on the other hand having one table for all translations could result in some fairly large result sets to chew through if you start doing multiple joins. I think I'd personally still stick to language files but it's whatever you think suits you best.

Also in #PHP _↴

echo problem in PHP php-mysql problems with apostrophe Radio button - multiple values

Success!

Help @Sid3335 spread the word by sharing this article on Twitter...

Tweet This

i18n best practice

8 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

i18n best practice

8 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

8 Comments(s) _↴

Also in #PHP _↴