Welcome Guest, Not a member yet? Register   Sign In
localize databases with working searching and ordering?

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:

$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 :


$color_name_lang = 'color_name_'.$lang;


And wherever you want to display them:


<?php foreach($colors as $color) { ?>

    <li><?php echo $color->$color_name_lang; ?></li>

<?php } ?>


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.

(This post was last modified: 06-11-2024, 07:25 AM by Kimotu.)

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() {
                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'
        return $this;

PHP Code:
$data['colors'] = $this->model
            ->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?
    -> 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%';

Ok, rewrote the query myself using CTE (which is unfortunatelly not supported by QueryBuilder)

'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 . '%"'
How does it work? It creates dynamically an additional column with translated color names for the ~10 predefined/hardcoded colors, user defined colors are kept untranslated (assuming, the user uses its native language anyway), tables are joined and the dynamic column is searched for the translated colorname. Smile

Theme © iAndrew 2016 - Forum software by © MyBB