PHP / MySQL web-app使用枚举数据库字段进行国际化

Question

I have joined a project recently and now I'm working on its Internationalization improvement. Technologies used are PHP/MySQL/Zend Framework/Dojo. I18n is implemented using gettext almost as described here link to SO question in the second answer.

But I encountered one problem. Some part of the information specific to certain DB tables is stored within those tables in the enum type columns. For example there is a field usr_online_status in the table "user" which could be one of either 'online' or 'offline'. There are many such tables with enum fields which contain info like ('yes' ,'no') ,('download', 'upload') and so on. Of course this info is displayed in English regardless of the current Language chosen by user.

I would like to solve this inconvenience. But don't know what is the best way to do this in terms of performance and ease of implementation. I see two possible options:
1) Make language specific dictionary tables for each table which uses such enums.
2) Download all the info from enums. Translate it. Make a script which could on demand alter every table and replace those enums with the required translations.

But there may be simpler or better solutions for this problem.

What would you do ?

Thanks for your answers.

UPD1 Important remark. Info from the enums is not only displayed at the GUI but is used in search. For example - there is a grid on a webpage which contains info about users. You can type 'line' in a search field and the result will be only those users with the word '%line%' in their info, for example 'online' status.

Answer 1

You definitly want dictionary tables: Only with these can 2 different users of the app work in different languages at the same time.

I recommend to put some of these dictionary tables into PHP though, as this has proven to be quite an unintrusive and performant way of doing it - e.g.

$translation=array('yes'=>'Ja','no'=>'Nein', ..)

//...

$row=mysql_fetch_row($qry);
//$row[1] has yes/no
$row[1]=$translation[$row[1]];

//...

$translation could be require_once()'ed depending on the current user's language preferences, the URL or whatever

Basically you trade some RAM for speed and easyness.

UPDATE:

With Gior312 adding the info about search, here is my solution for it: Have the reverse translation in a DB table (you even might use it to create $translation per a script):

CREATE TABLE translations (
  id INT PRIMARY KEY AUTO_INCREMENT,
  languageid INT NOT NULL,
  enumword VARCHAR(m) NOT NULL,
  langword VARCHAR(n) NOT NULL,
  -- n and m to your needs
  INDEX(languageid)
  -- other indices to your needs
)

Now when the search up until now was

$line=... //Maybe coming from $_POST['line'] via mysql_real_escape_string()
$sql="SELECT * FROM sometable WHERE somefield LIKE '%$line%'";

What you now do is

$line=... //Maybe coming from $_POST['line'] via mysql_real_escape_string()
$sql="SELECT enumword FROM translations WHERE languageid=$currentlanguageid AND langword LIKE '%$line%'";
//fetch resulting enumwords into array $enumwords
$enumlist=explode("','",$enumwords);
//This assumes, that the field enumwords contains nothing, that needs to be escaped
$sql="SELECT * FROM sometable WHERE somefield IN ('$enumlist')";

The rationale behind treating forward and back translation differently is:

• There will be many more lines in the code where you display, than where you search, so the unintrusiveness of the forward translation is more important
• The forward trnslation has to be done PER ROW (with a join), the reverse only PER QUERY, so the performance of the forward translation is more important than the performance of the reverse translation