multilanguage database struct

[eluser]davidino86[/eluser]
Hi,

I'm developing a multilanguage cms and i like to know the best way to structure my DB for the best frontend control with the multilanguage library of codeigniter. so when i have in the URL en or it the query take the right record.

i hope in your help,
regards

[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

[eluser]davidino86[/eluser]
thanks so much

[eluser]siubie[/eluser]
hai isern i try your solution but when i use print $this->idoma there is notthing appear ?
where do i wrong

i use URI Language Identifier from wiki this is my file

Code:

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class MY_Language extends CI_Language
{
    function MY_Language() {

        global $RTR;

        $index_page    = $RTR->config->item('index_page');
        $lang_uri_abbr = $RTR->config->item('lang_uri_abbr');

        /* get the lang_abbr from uri segments */
        $lang_abbr = current($RTR->uri->segments);

        /* check for invalid abbreviation */
        if( ! isset($lang_uri_abbr[$lang_abbr])) {

            $deft_abbr = $RTR->config->item('language_abbr');

            /* check for abbreviation to be ignored */
            if ($deft_abbr != $RTR->config->item('lang_ignore')) {

                /* check and set the default uri identifier */
                $index_page .= empty($index_page) ? $deft_abbr : "/$deft_abbr";

                /* redirect after inserting language id */
                header('Location: '.$RTR->config->item('base_url').$index_page.$RTR->uri->uri_string);
            }

            /* get the language name */
            $user_lang = $lang_uri_abbr[$deft_abbr];

        } else {

            /* get the language name */
            $user_lang = $lang_uri_abbr[$lang_abbr];

            /* reset config language to match the user language */
            $RTR->config->set_item('language', $user_lang);
            $RTR->config->set_item('language_abbr', $lang_abbr);

            /* check for abbreviation to be ignored */
            if ($lang_abbr != $RTR->config->item('lang_ignore')) {

                /* check and set the user uri identifier */
                   $index_page .= empty($index_page) ? $lang_abbr : "/$lang_abbr";

                /* reset uri segments and uri string */
                $RTR->uri->_reindex_segments(array_shift($RTR->uri->segments));
                $RTR->uri->uri_string = str_replace("/$lang_abbr/", '/', $RTR->uri->uri_string);
            }
        }

        /* reset the index_page value */
        $RTR->config->set_item('index_page', $index_page);
        log_message('debug', "MY_Language Class Initialized");
    }
}

/* translate helper */
  function t($line) {
      global $CI;
      return ($t = $CI->lang->line($line)) ? $t : $line;
  }

function getLanguage()
{
    $CI =& get_instance();

    return $CI->session->userdata('language');
}

function getLanguageId()
{
    $CI =& get_instance();

    return $CI->session->userdata('language_id');
}

and my baseController.php

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->idbahasa;

            $this->session->set_userdata('language_id',$languageId);
        }

        $this->idioma = getLanguageId();
        log_message('debug', "BaseController Class Initialized");
    }

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

}

[eluser]Isern Palaus[/eluser]
Hi siubie,

Seems to be all fine... Do you extend "BaseController" correctly, no?

Whis it helps!
Isern

[eluser]siubie[/eluser]
this is my controller is this the right way to extend the class?

Code:

<?php
include "baseController.php";
class Web extends BaseController {

    function Web()
    {
        parent::Controller();
    }

    function index()
    {
      echo $this->idioma; // this one return nothing
//      echo $this->session->userdata('language_id'); // this line return 1 wich is in my database english language have id 1
      $this->template->set_template('webTemplate');
      $this->template->write_view('header', 'web/header/default');
      $this->template->write_view('menu', 'web/menu/default');
      $this->template->write_view('slide', 'web/slide/default');
      $this->template->write_view('content', 'web/content/default');
      $this->template->write_view('footer', 'web/footer/default');
      $this->template->render();
    }



}

/* End of file welcome.php */
/* Location: ./system/application/controllers/welcome.php */

[eluser]Isern Palaus[/eluser]
This is the way to do it:

Code:

<?php

class Web extends BaseController {

    function Web()
    {
        parent::BaseController();
    }

    function index()
    {
      echo $this->idioma; // this one return nothing
//      echo $this->session->userdata('language_id'); // this line return 1 wich is in my database english language have id 1
      $this->template->set_template('webTemplate');
      $this->template->write_view('header', 'web/header/default');
      $this->template->write_view('menu', 'web/menu/default');
      $this->template->write_view('slide', 'web/slide/default');
      $this->template->write_view('content', 'web/content/default');
      $this->template->write_view('footer', 'web/footer/default');
      $this->template->render();
    }



}

/* End of file welcome.php */
/* Location: ./system/application/controllers/welcome.php */

Put the 'BaseController.php' in your /libraries folder inside the application folder.

Note that I changed parent::Controller(); to parent::BaseController();

Have you got a record inside your database that matches with the language abbreviation on the URI?

[eluser]siubie[/eluser]
I have change the code, i move BaseController.php to /libraries in the application folder

but it comes with error :

Fatal error: Class 'BaseController' not found

and in my database i have the record that matches the language abbreviation.

[eluser]Isern Palaus[/eluser]
mmmm... so strange.

Can you e-mail me the application folder zipped to [email protected] ? And I will look for the problem.

[eluser]siubie[/eluser]
ok i will send it asap
a million thanks for you isern