Translation SQL table and translation system

This is a short, non-interesting article about a translation system. Nothing new or original here.
I put here because my next article will use it and it’ll be a very long one.

I a few month, I’m gonna talk a lot about translation. I’ve made many things about it, especially for automatic translations.
Here, it’s about having a website in several languages. Nothing difficult.

Most people use the getext functions. There are translation files, binary processed, with cache. A very efficient and quite simple system to have translations. People who use that are from the past.
Now, we use a simple SQL table, with a field per language. It’s slower, takes more resources, like, hu, a hundred times more ? But it’s soooooo much simpler that we prefer that.

The translation interface is so uninteresting I won’t put it here. I’ll just show you my table, as it will be used in next articles, just like my sqlQueries class.
I use it on all my websites (mostly Supafan and DB Multiverse)

CREATE TABLE `get_textes` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `section` varchar(20) NOT NULL,
  `txt_en` text NOT NULL,
  `txt_fr` text NOT NULL,
  `txt_it` text NOT NULL,
  `txt_es` text NOT NULL,
  `txt_jp` text NOT NULL,
  `txt_ar` text NOT NULL,
  `txt_nl` text NOT NULL,
  `txt_pl` text NOT NULL,
  `txt_pt` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `section` (`section`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

You can give the name you want to the table, as if I cared…
The « section » field will be used to group some sentences together. It’s easier when I want to give to someone only the translations for the times functions, or the comment system, or the translations of the text of DBM’s pages…
The « txt_en » field is required, because it’s the language to use, when the translation is missing.
The engine is MyISAM because it’s mostly a table you read and not one you edit (yes yes I actually thought about that).
So yes, there us a field per language. It’s the simplest by far. Donc forget utf8, it’s your best friends when you add « weird » languages like Asians and Arabic.

Also, in you PHP scripts, always do this query, just after the database connection :

SET NAMES 'utf8'

Otherwise, you may have weird results.

The translation function :

function __($id) {
    global $lang;
    static $tablename = 'get_textes';
    $id = (int) $id;
    $R = mysql_query("SELECT `txt_$lang`, txt_en FROM $tablename WHERE id=$id LIMIT 1");
    $txt = mysql_result($R, 0, 0);
    if (!$txt) return mysql_result($R, 0, 1);
    return $txt;
}

You do the work you must do so the global variable $lang (or $GLOBALS['lang'] if you want to use that instead) can only have a value that’s a database field.

So no, there is no cache on anything like this. I didn’t want and didn’t have time to think about this kind of things.
After all, a database is made to be accessed. There is an index.

That’s all folks!