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

我最近加入了一个项目,现在我正致力于国际化改进。 使用的技术是PHP / MySQL / Zend Framework / Dojo。 I18n使用gettext实现,几乎与此处所述链接到SO 问题在第二个答案中。</ p>

但我遇到了一个问题。 特定于某些数据库表的信息的某些部分存储在枚举类型列中的那些表中。 例如,表“user”中有一个字段usr_online_status,它可以是“在线”或“离线”之一。 有很多这样的表有enum字段,其中包含信息('yes','no'),('download','upload')等等。 当然,无论用户选择何种语言,此信息都以英语显示。</ p>

我想解决这个不便之处。 但是不知道在性能和易于实现方面做到这一点的最佳方法是什么。

我看到两个可能的选项:

1)为使用这些枚举的每个表创建特定于语言的字典表。

2)从枚举中下载所有信息。 翻译它。 创建一个可以按需更改每个表的脚本,并用所需的翻译替换这些枚举。</ p>

但是对于这个问题可能有更简单或更好的解决方案。</ p>

你会做什么?</ p>

感谢您的回答。</ p>

UPD1 </ strong>
重要评论。 来自枚举的信息不仅显示在GUI上,还用于搜索。 例如 - 网页上有一个网格,其中包含有关用户的信息。 您可以在搜索字段中键入“行”,结果将只是那些在其信息中包含“%line%”字样的用户,例如“在线”状态。</ p>
</ div>

展开原文

原文

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.

1个回答



你肯定想要字典表:只有这些才能同时使用不同语言的2个不同用户。</ p >

我建议将这些字典表中的一些放入PHP中,因为事实证明这是非常不引人注意且高效的方式 - 例如</ p>

 <  code> $ translation = array('yes'=&gt;'Ja','no'=&gt;'Nein',..)

// ...

$ row = mysql_fetch_row($ qry );
// $ row [1]有是/否
$ row [1] = $ translation [$ row [1]];

// ...
</ code> </ pre>

$ translation可以是 require_once()</ code>',具体取决于当前用户的语言首选项,URL或其他</ p>

基本上你 交换一些RAM以获得速度和简便性。</ p>

更新:</ strong> </ p>

随着Gior312添加有关搜索的信息,这是我的 解决方案:在数据库表中进行反向转换(您甚至可以使用它来为每个脚本创建$ translation):</ p>

  CREATE TABLE translations(
id) INT PRIMARY KEY AUTO_INCREMENT,
languageid INT NOT NULL,
enumword VARCHAR(m)NOT NULL,
langword VARCHAR(n)NOT NULL,
-n和m to your needs
INDEX(languageid)
  • 满足您需求的其他索引

    </ code> </ pre>

    现在搜索到现在为止</ p>

      $  line = ... //也许来自$ _POST ['line']来自mysql_real_escape_string()
    $ sql =“SELECT * FROM sometable WHERE somefield LIKE'%$ line%'”;
    </ code> </ pre>

    你现在做的是</ p>

      $ line = ... //也许来自$ _POST ['line']通过mysql_real_escape_string()

    $ sql =“SELECT enumword FROM translations WHERE languageid = $ currentlanguageid AND langword LIKE'%$ line%'”;
    //将得到的枚举词提取到数组$ enumwords
    $ enumlist = explode(“','”, $ enumwords);
    //这假定,字段枚举不包含任何内容,需要转义
    $ sql =“SELECT * FROM sometable WHERE somefield IN('$ enumlist')”;
    </ code> </ pre>

    处理前向和后向翻译的理由不同 y是:</ p>


    • 您显示的代码中将有比您搜索的行更多的行,因此正向翻译的非侵入性更为重要</ li >
    • 正向转发必须在PER ROW(带连接)完成,反向只进行PER QUERY,因此正向转换的性能比反向转换的性能更重要</ li>
      </ ul>
      </ div>

展开原文

原文

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

du31992
du31992 感谢前向和反向翻译的解释。 这似乎是我一直在寻找的。
接近 9 年之前 回复
doufuxing8691
doufuxing8691 更新了我处理搜索问题的答案
接近 9 年之前 回复
douwengzao5790
douwengzao5790 感谢您的回答。 字典表对我来说似乎也是一个好主意。 至于2个用户改变语言你是对的。 我没有想到它,因为我们的网络应用程序不是类似系统的论坛,而是控制某些硬件设备的应用程序,因此只有有限数量的用户可以更改语言(例如管理员)和之间的冲突 他们不太可能。 关于在PHP中存储枚举信息。 如果我们这样做,那么我将无法使用'SELECT ... where ... ='%line'在该字段中进行搜索。 请参阅我的问题中的UPD1部分
接近 9 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐