There is a table in which a bunch of lines with translation into different languages. You need to make a selection of the type

SELECT * FROM table WHERE language_id = @language_id

but with a rake: not all languages ​​have a translation of the desired string. In this case, you need to get either the first entry, taking into account the importance of the language (for example, ru, en, by, ua, kz , others) or at least just the first entry with a different language_id .
How to implement it?

  • Thanks, hint about ORDER BY FIELD (language_id, 'ru', 'en', 'by', 'ua', 'kz') helped For OpenCart, this SELECT * FROM query (SELECT category .parent_id as parent_category_id, category_description works. category_id, language . code , category_description . name FROM category_description INNER JOIN category ON category .category_id = category_description .category_id JOIN language ON language .language_id = category_description .language_id ORDER BY category_description .category_id, FIELD ( language . code , 'en', " ',' az ') DESC) myViewTable GROUP BY category_i - QuAzI

2 answers 2

a bunch of lines with translation into different languages.

So in the table there is a key by which the "translation" goes, I would do something like this:

 $stack = array('ru', 'en', 'by', 'ua', 'kz'); $q = $db->query('select * from table where translation_key= @key and language_id in ("ru", "en", "by", "ua", "kz")', array(' @key ' => $yourKey)); $translations = array(); foreach ($q as $tr) { $translations[$tr['language_id']] = $r['translate_value']; } // ищем первое наличие перевода. исходя из пордка языков установленных в $stack foreach ($stack as $lang) { if (!empty($translations[$lang])) return $translations[$lang]; } throw new Exception('no translate for '.$yourKey);

You can also do this using mysql.

for example, something like this:

 SELECT * FROM table WHERE translation_key= @key and language_id in ("ru", "en", "by", "ua", "kz" ORDER BY FIELD(language_id, 'ru', 'en', 'by', 'ua', 'kz');

    Like that

     SELECT sentence_id, FIRST_ROW(text) OVER ( PARTITION BY sentence_id ORDER BY (case p.id = @language_id then 1 else 1+ gp.priority end) ) from translation t, lang_priority p where t.language_id = p.id

    You can read more here.