What localization tools are there at the MySQL level?

Alexander Strelov2012-05-31 11:42:08

PHP

Alexander Strelov, 2012-05-31 11:42:08

Perhaps I am not familiar with such a bike, but there is a database in MySQL, there is an application in PHP, there is a task to make transparent work with data taking into account the current user language, i.e. organize support for multilingual site, and the number of languages should be expanded without plowing the database. At the PHP level, everything is clear, these are gettext () and localization files, but what about the base? Perhaps there are already some ready-made solutions that I could not google? I would appreciate an index.

Answer the question

In order to leave comments, you need to log in

8 answer(s)

Shedal, 2012-05-31
@Shedal

Alternatively, you can create a translations (text, culture, translation) table.
Define a function translate(text, culture) that will return either the translation if there is one, or the original string if there is no translation.
You can use it with any table:

SELECT Id, translate(Value), translate(Value2) ...

Of course, different solutions are suitable for different situations. For example, it is not clear from your description whether you need to translate data from one or many tables, whether lines of text can be translated differently depending on the context, etc.

Vampiro, 2012-05-31
@Vampiro

And you can also make separate tables, and then the performance will not depend on the number of languages in the system.

Jurik, 2012-05-31
@Jurik

I would add a language field to the tables and then collect data based on the language. Those. people with Russian language only see posts with the 'ru' tag. If the language is not in the database, then take some default language. You can also make a multilingual label, thanks to which the data will be visible to everyone.

rPman, 2012-05-31
@rPman

If you do not shovel the database, simply, for show ... then instead of text in the field, store a serialized array {ru:'Blah, blah, blah',en:'Blah, Blah, Blah',..}...
But in the general case, this is not will solve problems, which actually appear much more when moving from a single language site to a multilingual one. They are all specific, like what to do with data, the meaning of which is 'translation' ... whether a new entity is needed for them with a link to the original, or a 1k1 connection is enough, etc.

Xenkok, 2012-05-31
@Xenkok

deleted

Stanislav, 2012-05-31
@crackedmind

Hmm, of course I'm wildly sorry, but what's the point in localization on the dbms side? Isn't that what "display" is supposed to do?

strib, 2012-05-31
@strib

dictionary_id|string_id|lang|value also works well.

Valentin, 2019-09-28
@ValentinWebStd

Good night! As for me, this option is possible:
1) make a copy of the tables (including data) of the first table, adding a prefix that will be indicated in the mov.. For example, products_ua.
2) in sql queries, deselect this table, add a prefix to the table name.. ('SELECT * FROM products_'. $_SESSION[`lang`]. ' WHERE product_id=1'
) it is necessary to translate and they can be present in the tables and so everything is correctly processed it is necessary to attach triggers so they will be viewed when updating and inserting fields into the original table ... I can discuss the further description of the mechanism with you in skype: webstd.studio