localize databases with working searching and ordering?

Hello,

I try to implement an application that can be localized. Text in php and views is no problem, since I can use 'lang()', but now I have a database table that holds colors and their name.

I stored the colors in english (black, blue, green, ...). No problem while just displaying them. I just use "<?= lang('Colors.' . $color->name) ?> in templates and get a localized output (e.g. german: schwarz, blau, grün).

But now I offer the possibility to search and order by color and that leads to wrong (order) or no (search) results, since e.g. order

In english:

• green
  
• black
  
• blue
  

In german:

• blau (blue)
  
• grün (green)
  
• schwarz (black)
  

Searching is broken at all, since names are complete different. Any ideas how to solve this except with this construction that may get really long (usually I prefer query builder, but there is no CASE support):

Code:

$db->query('
  select case
    when name = "Colors.black" then "' . lang('Color.black' . '"
    when name = "Colors.green" then "' . lang('Color.green' . '"
    ...
  end as color_name from colors order by color_name');

results in:

Code:

$db->query('
  select case
    when name = "Colors.black" then "schwarz"
    when name = "Colors.green" then "grün"
    ...
  end as color_name from colors order by color_name');

Any ideas to make it better? Unfurtunatelly there is no reverse_lang(), so that I could simply translate the localized string, back to its placeholder.

Search in form? What is the problem? Set up the select or radio form as ['green' => lang(...), 'black' => lang(...), 'blue' => lang(...),].
Create a regular search query.

See https://codeigniter.com/user_guide/helpe...m_dropdown

Simple CI 4 project for beginners codeigniter-expenses ( topic )

You have to specify what colors can be queried into the table, then the results in view must be translated according to user preferences.

@xxxx[{::::::::::::::::::::::::::::::::>

It is not so easy. It is a search input, that is not restricted to one database column. You can search a term and it is looked up in serveral columns and all matching ones are shown.

You can try here: https://kh.4lima.de/routes

Colors is a translated string. So sorting and searching is wrong. Search 'schwarz' (german), you won't find anything, but search 'black' and black routes are shown.

I'm not sure it's possible to do this easily. The initial search structure is incorrect. You will be searching for non-existent data.

As an variant, do not translate the keys for colors.

Simple CI 4 project for beginners codeigniter-expenses ( topic )

Hi,
The way I would do this: in a table I have multiple field for different languages, i.e. colorEN, colorDE, colorFR.
The query would be something 

PHP Code:

$query = $this->db->table('colors')
 ->select('color'.$language.' AS color')
    ->orderBy('color'.$language, 'ASC); 


Where $language is the current selected language.
I guess this would also order the colors correctly.

Yeah, and have a bunch of garbage in the table to sort  And when will there be five more localized fields?

Simple CI 4 project for beginners codeigniter-expenses ( topic )

We're talking about a colors table, right? What garbage do you mean?

Translate them.

PHP Code:

<?php
/**
*
* Blog - English / Anglais
*
*/
return [
    'title' => "Welcome to my blog",
    'metaDescription' => "Example of a multilingual web site made with CodeIgniter",
    'metaKeywords' => "CodeIgniter, language, example",
    'languageName_de' => "German (Deutsch)",
    'languageName_en' => "English",
    'languageName_es' => "Spanish (Español)",
    'languageName_fr' => "French (Français)",
    'languageName_ja' => "Japanese (日本語)",
    'green' => "green",
    'black' => "black",
    'blue' => "blue",
    'aboutTitle' => "About",
    'aboutText' => "Hello, here is an example of a multilingual blog.",
];


<?php
/**
*
* Blog - German / Allemand
*
*/
return [
    'title' => "Willkommen in meinem Blog",
    'metaDescription' => "Beispiel für eine mit CodeIgniter erstellte mehrsprachige Website",
    'metaKeywords' => "CodeIgniter, Sprache, Beispiel",
    'languageName_de' => "Deutsch",
    'languageName_en' => "Englisch (English)",
    'languageName_es' => "Spanisch (Español)",
    'languageName_fr' => "Französisch (Français)",
    'languageName_ja' => "Japanisch (日本語)",
    'green' => "Grün",
    'black' => "Schwarz",
    'blue' => "Blau",
    'aboutTitle' => "About",
    'aboutTitle' => "Über uns",
    'aboutText' => "Hallo, hier ist ein Beispiel für einen mehrsprachigen Blog.",
]; 


SEE: The Universal Solution: A Translation subschema

Best Practices for Multi-Language Database Design

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

We're talking about a colors table, right? What garbage do you mean?

You have one additional column per language and if you do no restrict SELECT to one language column, all additonal language columns need to be process (sorted, etc.), even when not needed.
For every typo you have to make an SQL query. I would like to maintain it in language files. Easier for translators. They just need to work work with text files.