multilanguage database struct

[eluser]Isern Palaus[/eluser]
Hello,

I have a table called 'idioma' (language in spanish):

Code:

CREATE TABLE IF NOT EXISTS `idioma` (
  `ididioma` int(11) NOT NULL AUTO_INCREMENT,
  `nombre` varchar(45) COLLATE utf8_spanish2_ci DEFAULT NULL,
  `codigo` varchar(5) COLLATE utf8_spanish2_ci DEFAULT NULL,
  `locale` varchar(255) COLLATE utf8_spanish2_ci DEFAULT NULL,
  `imagen` varchar(125) COLLATE utf8_spanish2_ci DEFAULT NULL,
  `orden` int(11) DEFAULT NULL,
  `activo` tinyint(4) DEFAULT NULL,
  `fecha` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`ididioma`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci

nombre -> name
codigo -> code
locale -> locale
imagen -> image
orden -> order
activo -> active
fecha -> date

I add entries like: Name = English, Code = EN, Locale = en_US, Image = en.jpg, Order = 1, Active = 1, Date = 2010-02-17.

Then, to detect the language I personally use URI Language founded on the wiki for the detect the languages like .com/en, .com/es, .com/fr... and my BaseController (that I extend on every controller) looks like:

Code:

<?php

class BaseController extends Controller {

    public $idioma = NULL;

    public function BaseController()
    {
        parent::Controller();
        
        $language = $this->config->item('language_abbr');
        $this->session->set_userdata('language', $language);
        
        $query = $this->_getLanguageId($language);
            
        if($query->num_rows() > 0)
        {
            $row = $query->row();
            
            $languageId = $row->ididioma;
            
            $this->session->set_userdata('language_id',$languageId);
        }
        
        $this->idioma = getLanguageId();
    }

    private function _getLanguageId($codigo)
    {
        return $this->db->get_where('idioma', array('codigo' => $codigo));
    }

}

And in the MY_Language.php helper:

Code:

function getLanguage()
{
    $CI =& get_instance();
    
    return $CI->session->userdata('language');
}

function getLanguageId()
{
    $CI =& get_instance();
    
    return $CI->session->userdata('language_id');
}

So I can access in every controller with $this->idioma.

Then all my tables that contents multilingual content has this structure:

news
----
idnews
image
user_id
date

news_description
----------------
idnews_description
text
news_idnews
idioma_ididioma

Then for every new you will add a text for every language.

A query will look like:

Code:

public function get_new($idnews = 1, $ididioma = 1)
{
    $this->db->from('news')
             ->join('news_description','news.idnews = news_description.news_idnews','left')
             ->where('news_description.idioma_ididioma', $ididioma)
             ->where('news.idnews', $idproducto)
             ->limit(1);
    
    return $this->db->get();
}

And you will call it with get_news($new_id, $this->idioma).

I wish it helps and sorry for my school English.

Regards,
Isern