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.