PHP and MySQL are the best way to handle various contents of a database language

Question

The question is: what is the best way to handle the contents of a database language? Suppose you have a shopping cart whose products can have multiple languages, which would be the best approach for building a db schema?

Hurrah!

Answer 1

I would save the unique translation key in the table, and then either have another table, or possibly translation files, so that you can refer to the translation using the key.

table product :

 id: 15 name: product.foo_widget attr: ... ... etc 

table translation :

 lang: en key: product.foo_widget trans: Foo Widget 

Answer 2

Specify the language of the current user as a property of your user object (or any template that you use to track users). When you query DB for strings, add a conditional expression to display the current user's language, if available.

In this example, I put language_id in $_SESSION ; I usually will not track users this way, but I think the concept is illustrated. In your database, each product will have several entries for each language, where 0 is the default value for the site (presumably English). The request will capture a specific linguistic campaign or revert to the default if there is no language-accessible element.

 // the id of the item you're after $item_id = 1; $sql = 'SELECT id, name FROM product_names WHERE item_id = '.$item_id.' AND (language_id = '.$_SESSION['user']['language'].' OR language_id = 0) ORDER BY language_id DESC LIMIT 1'; 

I use the same concept for common lines around the site - I have a database table called "content_strings" with field identifiers, text and id_ language.