+- 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
RE: localize databases with working searching and ordering? - demyr - 06-11-2024
For an easy solution, your color tables should be:
color_id - color_name_en - color_name_de - color_name_it etc.
in your Controller you can catch your language easily, it is generally the first segment after your base_url. And also you can catch it after you send the form. You can send the language with a hidden input.
From your Controller to the view, send the current language with:
Code:
$data['lang'] = $this->request->uri->getSegment(1);Later, it is time to display. No need for the translation file. Just catch your $lang variable and on top of your site, in a common file - maybe a top menu one - set your names :
Code:
<?php
$color_name_lang = 'color_name_'.$lang;
?>And wherever you want to display them:
Code:
<ul>
<?php foreach($colors as $color) { ?>
<li><?php echo $color->$color_name_lang; ?></li>
<?php } ?>
</ul>This part is important. Not $color->color_name_lang but $color->$color_name_lang
Hope it helps
P.S. You are going to make your search via color_id, not names. Names are just sth visual that you display.
RE: localize databases with working searching and ordering? - Kimotu - 06-11-2024
I tried something like this. All predefined standard colors would get translated, user defined ones in the language what they used during naming.
Then I could use translated_name as search and order column and everything would work like expected. Unfortunatelly, when I add this function, my closure function does not find the translated_name column.
PHP Code:
/**
* Get localized color names
*
* @return object result with additional localized name column
*/
public function withLocalizedNames() {
$this->builder()->select('
CASE
WHEN name = "Colors.black" THEN "' . lang('Colors.black') . '"
WHEN name = "Colors.blue" THEN "' . lang('Colors.blue') . '"
WHEN name = "Colors.green" THEN "' . lang('Colors.green') . '"
ELSE name
END AS translated_name', false);
return $this;
}
PHP Code:
$data['colors'] = $this->model
->select()
->withLocalizedNames()
->when($data['search'] !== '', function ($query) use ($data) {
return $query
->like('color', $data['search'], 'both')
->orLike('color2', $data['search'], 'both')
->orLike('translated_name', $data['search'], 'both');
})
->orderBy($data['sortColumn'], $data['sortDirection']);
Well, the solution above works pretty good for correct order, since I can order by column translated_name. The problem I am facing now is the logical SQL execution order.
WHERE is executed before SELECT, thus the column translated_name does not exist when WHERE is compared.
Any SQL gurus around that can rewrite this query? Runtime add a column with translated values that I can compare in WHERE clause?
Code:
SELECT *, CASE
-> WHEN name = "Colors.black" THEN "schwarz"
-> WHEN name = "Colors.blue" THEN "blau"
-> WHEN name = "Colors.green" THEN "grün"
-> ELSE name END AS translated_name FROM `colors` WHERE `translated_name` LIKE '%schwarz%';RE: localize databases with working searching and ordering? - Kimotu - 06-12-2024
Ok, rewrote the query myself using CTE (which is unfortunatelly not supported by QueryBuilder)
Code:
'with translated as (
select id, CASE
WHEN colors.name = "Colors.black" THEN "' . lang('Colors.black') . '"
WHEN colors.name = "Colors.blue" THEN "' . lang('Colors.blue') . '"
WHEN colors.name = "Colors.green" THEN "' . lang('Colors.green') . '"
...
ELSE colors.name
END AS tname from colors)
SELECT *, translated.tname from routes
JOIN colors ON colors.id = routes.color_id
JOIN translated ON translated.id = colors.id
WHERE tname like "%' . $searchterm . '%"'