+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: localize databases with working searching and ordering? (/showthread.php?tid=91043)
Pages:
1
2
localize databases with working searching and ordering? - Kimotu - 06-10-2024
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
- blau (blue)
- grün (green)
- schwarz (black)
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');RE: localize databases with working searching and ordering? - ozornick - 06-10-2024
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/helpers/form_helper.html#form_dropdown
RE: localize databases with working searching and ordering? - warcooft - 06-10-2024
You have to specify what colors can be queried into the table, then the results in view must be translated according to user preferences.
RE: localize databases with working searching and ordering? - Kimotu - 06-10-2024
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.
RE: localize databases with working searching and ordering? - ozornick - 06-10-2024
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.
RE: localize databases with working searching and ordering? - JustJohnQ - 06-10-2024
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.
RE: localize databases with working searching and ordering? - ozornick - 06-10-2024
Yeah, and have a bunch of garbage in the table to sort
And when will there be five more localized fields?
RE: localize databases with working searching and ordering? - JustJohnQ - 06-10-2024
We're talking about a colors table, right? What garbage do you mean?
RE: localize databases with working searching and ordering? - InsiteFX - 06-10-2024
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
RE: localize databases with working searching and ordering? - Kimotu - 06-11-2024
(06-10-2024, 01:02 PM)JustJohnQ Wrote: 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.