Two implementations of website internationalization I have attempted to create an internationalization system for my PHP framework that I'm working on.

For the purpose I decided I will be using MySQL to store languages and translations. Respectively I have the tables


languages
id  | name
----+--------
1   | English
2   | Spanish

translations
id | 1             | 2           | n ...
---+---------------+-------------+------
1  | Hello, $user  | Hola, $user |   ...
2  | Friend        | Amigo       |   ...



I was told that this is not a normalized database structure. I think that this structure is easier to use, because if I were to add a column lang_id where I'd store the id of the language the string represents, then when I'm selecting I'd have to do

SELECT string FROM translations WHERE id = ? AND lang_id = {$lang_id}


Instead of what I currently use

SELECT {$lang_id} FROM translations WHERE id = ?


It looks simpler to me, also avoids the use of complex primary keys and uses less memory too. If I'm not correct please don't hesitate to criticize!

I have created two implementations of the logic class, one using eval in its base, other one using preg_replace_callback.

First implementation: Eval

class Translator {
    private $stmt = null;
    private static $instance;

    public static function getInstance(){
        if(!self::$instance){
            self::$instance = new self;
        }
        return self::$instance;
    }

    private function __construct() {
        $this-&gt;stmt = \X\Database::connection()-&gt;prepare('SELECT '.$_SESSION['lang_id'].' FROM translations WHERE id = ?')-&gt;getStmt();
    }

    public function get($id, $variables){
        $this-&gt;stmt-&gt;execute(array($id));
        foreach($variables as $k =&gt; $v){
            $$k = $v;
        }
        unset($id); unset($variables); unset($k); unset($v);

        return eval('return "'.addslashes($this-&gt;stmt-&gt;fetch(\PDO::FETCH_NUM)[0]).'";');
    }
}


Example usage in a view, assuming $t = \X\Translator::getInstance();

With variables

&lt;h1&gt;&lt;?= $t-&gt;get(1, array('user'=&gt;'John Doe')) ?&gt;&lt;/h1&gt;


Without variables

&lt;h1&gt;&lt;?= $t-&gt;get(2) ?&gt;&lt;/h1&gt;


Second implementation: Regular Expressions

class Translator {
    public static function init(){
        ob_start();

        register_shutdown_function('\X\Translator::translateDocument');
    }

    public static function translateDocument(){
        $t = \X\Database::connection()-&gt;prepare('SELECT '.$_SESSION['lang_id'].' FROM translations WHERE id = ?')-&gt;getStmt();


        echo preg_replace_callback('/\{\{\s*(\d+)\s*(?:,(.+?))?\s*}}/', function($matches) use($t){
            $t-&gt;execute(array($matches[1]));

            if(count($matches) &gt; 2){
                $keys = array();
                $vals = array();
                preg_replace_callback('/([^\s]+):\s*([^\;]+);/', function($matchesInner) use(&amp;$keys, &amp;$vals){
                    $keys[] = $matchesInner[1];
                    $vals[] = $matchesInner[2];
                }, $matches[2]);
                return str_replace($keys,$vals,$t-&gt;fetch(\PDO::FETCH_NUM)[0]);
            }else{
                return $t-&gt;fetch(\PDO::FETCH_NUM)[0];
            }
        }, ob_get_clean());
    }
}


Example usage, assuming that \X\Translator::init() has been called

With variables

&lt;h1&gt;{{ 1, $user: John Doe; }}&lt;/h1&gt;


Without variables

&lt;h1&gt;{{ 2 }}&lt;/h1&gt;


Finally

I ran a benchmark consisting of 5 runs of 1000 translations each, showing the statistics


#Average execution time
    eval:  0.65 sec
    regex: 1.15 sec

#Memory used
    eval:  240 kb
    regex: 253 kb
Creating the translations table with an indefinite number of weirdly named columns is a bad idea. Subsequently adding a language will be awkward: it involves ALTERing the table, then a series of UPDATEs.

I suggest a table with normalization…

CREATE TABLE translations (
    id INTEGER NOT NULL,
    lang_id INTEGER NOT NULL,
    string VARCHAR(???),
    PRIMARY KEY (id, lang_id),
    FOREIGN KEY (lang_id) REFERENCES languages (id)
);


Then, for your programming convenience, create a view for each language that you support.

CREATE OR REPLACE VIEW en AS
    SELECT string
    FROM translations AS t
        JOIN languages AS l
            ON t.lang_id = l.id
    WHERE l.name = 'English';


Alternatively, define a stored procedure to satisfy your laziness.

That way, your schema stays normalized.  Adding a view is less risky than altering a table. Even your queries read better:

SELECT string FROM en WHERE id = ?;


However, for performance, I think you would be better off querying and caching all of the strings (probably a few hundred — no big deal) in PHP than issuing a separate query per string.