CODEWITHSUNDEEP
phpmysql
LightningBG
LightningBG

Reputation: 131

MySQL select content in multiple languages

I have one table for generating unique id for translation, named translations_ids translation_id (BIGINT)

one table for translations named translations_entries with this structure: translation_id (BIGINT) - correspondents to unique translation_id in translations_ids table

    lang_abbr (varchar 3)
    table_name (varchar 42)
    translated_text (TEXT)

table with languages:

    lang_abbr (varchar 3)
    language (varchar 36)
    title (varchar 36)

Here is table with content (for example news) with this structure:

id
title (BIGINT) - correspondents to translation_id in translation_entries table
content (BIGINT) - correspondents to translation_id in translation_entries table
author (varchar 36)
date (varchar36)

I tried this:

SELECT *
FROM content LEFT JOIN translations_entries ON (content.title = translation_entries.translation_id AND
content.content = translation_entries.translation_id)
WHERE translations_entries.lang_abbr = 'en';

but in this way if content no english translation, it wouldn't be displayed in default language

Other way is:

  1. SELECT * FROM content
  2. Foreach row SELECT * FROM translations_entries WHERE translation_id = this from content AND lang_abbr = 'en'
  3. Check if row exists else new query with default language
  4. When data is fetched merge arrays from two tables and display news but this way may slow performance

My question is: What is the best way to select content from tables in given language and if language translation for this row not exists then select content in the default language?

Upvotes: 2

Views: 991

Answers (1)

NotGaeL
NotGaeL

Reputation: 8484

Sorry about my first answer it's not a solution. Try this instead:

SELECT *
FROM content LEFT JOIN (SELECT * FROM translations_entries 
WHERE translations_entries.lang_abbr = 'en') AS te 
ON (content.title = te.translation_id AND
content.content = te.translation_id);

Upvotes: 1

Related Questions