Localization / Multi-language Applications with PHP & MySQL

I’m working on a web app which must support multiple languages (lucky for me, the client requested English & Spanish but I want to be ready to easily add another in the future).

First, I did some reading on localization (l10n) vs. internationalization (i18n) and while my requirement isn’t quite “localization,” it’s close enough so that’s what I’m calling it.

My own requirements:

  • Easily expandable beyond 2 languages.
  • Able to read the intent in my PHP code.

I already had my languages table, so I added a couple more:

  • words
  • words_translated

“words” contained a (unique) slug, where the translation was stored in words_translated (I later switched from calling my translations “phrases” instead of “words). My PHP code would look like:

<?= l10n('logo_alt_text', $language_id) ?>

This is OK. I could look up the slug ‘logo_alt_text’ in the $language_id specified and return the “word.”

If the translation didn’t exist, I could fall back to looking it up in language ‘1’ (English) and return that.

function l10n($slug, $language_id) {
    $this->db->select("wt.word");
    $this->db->from("words_translated wt");
    $this->db->join("words w", "w.word_id=wt.word_id", "left");
    $this->db->where("w.slug", $slug);
    $this->db->where("wt.language_id", (int)$language_id);
    $this->db->limit(1);
    $query = $this->db->get();
    if ($row = $query->unbuffered_row('array')) {
        return $row['word'];
    }

    // We didn't get a match, so return the English version
    return l10n($slug, 1);
}

The problem with this is if I didn’t even specify the English version of the slug, it will fail. If you want to make sure a word is defined, this is fine but I’d rather have the application continue working so I scrapped those tables and came up with the following concept:

Now, my PHP looks like:

<?= l10n('Logo Alt Text', $language_id) ?>

I pass along the pure English phrase, instead of a slug. The PHP function is as follows:

public function get_phrase($phrase, $language_id) {
    if ($language_id == 1) { return $phrase; }

    // First, we need to get the phrase's l10n_id
    $this->db->select("*");
    $this->db->from("l10n");
    $this->db->where("language_id", 1);
    $this->db->where("phrase", $phrase);
    $query = $this->db->get();

    if ($row = $query->unbuffered_row('array')) {
        // Now, get the translated phrase
        $this->db->select("*");
        $this->db->from("l10n");
        $this->db->where("language_id", (int)$language_id);
        $this->db->where("l10n_parent_id", (int)$row['l10n_id']);
        $query = $this->db->get();

        if ($translated_row = $query->unbuffered_row('array')) {
            return $translated_row['phrase'];
        }
    }

    // I don't know.
    return $phrase;
}

I know I can rewrite those 2 SQL queries into a single query; this was a proof of concept which will get refactored before the app goes to production.

Finally, I didn’t want to have my PHP code like this:

$this->Language_model->get_phrase('Logo Alt Text', $language_id);

So I made MY_text_helper.php and put in it:

function l10n($phrase, $language_id) {
    $CI =& get_instance();

    $CI->load->model('Language_model');
    return $CI->Language_model->get_phrase($phrase, $language_id);
}

And now I can simply write:

<?= l10n('Logo Alt Text', $language_id) ?>