Categories in different languages (PHP)

Trying to work this out, but I don't know what's the best practice for this kind of things.

I'm working on a website using 3 languages: English, French & Dutch. There are categories on the website and the category names are different for the 3 languages.

For example:

Stars -> English

Sterren -> Dutch

Stars -> French

So I was thinking about adding them to the database. It's also easier for me to add more categories later if needed.

开发者_C百科

Now I'm facing the problem how to do this. My solution is:

**Cat_lang (category languages)**

cat_lang_id

language

**Categories**

categories_id

cat_lang_id

cat_title

Using cat_lang_id I can link both tables to get the language I need.

Is this the best solution for this problem?

Thanks in advance.

---

So that you can expand your website more easily in the future, I dont recommend having a cat_lang table. Stick with a languages table that contains language_id and language_name, and have your categories table point to it. Doing it that way allows you to have other entity types in your database (e.g. articles) that also contain multiple languages.

---

This is a flexible and reasonable solution. You see the same type of design in large scale ERP systems that have to handle dozens of languages and the possibility of more being added at any time.

---

If I were doing a website in multiple languages, I would use Zend_Translate to do the translations. Basically, you create a Zend_Translate object which reads in data files. Then you make calls on that object to translate() giving it the english version and it will give the translation in the correct language. Zend_Translate will scan your source and find all references to requested translations which will make files that can be translated by hand.

You are going to have much more than just the category names to translate, so I would recommend an approach like this where you just read in the translate file.

---

If you don't plan for a massive scale website and that you don't plan to increase to 100 languages, you can do a simpler and 'less nice' solution that is to have only 1 table of categories, where you hard code the language code in the category_name, for instance:

**Categories**

categories_id
cat_title_fr
cat_title_en
cat_title_de

Then in your code you set a $language_code variable at the beginning of each page using an include, you can even analyze the domain name in the $_SERVER variables to asign the correct language an by default choose the one you like (if you leave the variable empty your queries will return no text).

and you generate your queries like this:

mysql_query("SELECT cat_title_".$lang." FROM categories;");

Yeah it is dirty because you hard code the language in your DB structure, but if you have the exact same categories in each language with just a translation of the name, it is simple to implement.

Besides to add a language you just need to add a field in your table with the new translation, for instance spanish would be

cat_title_es