Multilingual site

[Manixat]

Hello again freaks,

 

After doing some research I came to the conclusion that a multilingual site is best done with a database and placeholders. What I can't seem to figure is how exactly is that supposed to work, is every placeholder supposed to have an unique ID and therefore a whole separate record, or shall there be one record per page and all placeholders serialized in a single field? Any help is appreciated!

[DaveyK]

I would be very interested in hearing what is "best practice" for this.

 

However, I think you would introduce string names, like 'user_name', which you COULD store as:

| id | string_name | lang_id | string
-----------------------------------------
| 1  |  user_name  |    1    | User name // english (lang_id 1)
| 2  |  user_name  |    2    | Gebruikersnaam  // dutch (lang_id 2)

 

Its just a suggestion, I've never actually built it. If anyone knows a better method, I would be glad to hear it.

 

EDIT: Markup

Edited March 27, 2013 by DaveyK

[DaveyK]

I just realized that would be a little stupid, I think this would be better:

 

strings

| string_id | string_name
-----------------------------------------
|     1     |  "user_name"
|     2     |  "email_address"

languages

| lang_id | language_name | language_code
-----------------------------------------
|    1    |   "English"   |    "en_US"
|    2    |   "Dutch"     |    "nl_NL"

 

translations

| trans_id | string_id | lang_id | string
-----------------------------------------
|    1     |     1     |    1    | "User name" // English (lang_id 1)
|    2     |     1     |    2    | "Gebruikersnaam"  // Dutch (lang_id 2)
|    3     |     2     |    1    | "Email address" // English (lang_id 1)

 

What do you guys think?

 

EDIT(S): I should check my posts better >.< errors everywhere!

Edited March 27, 2013 by DaveyK

[Manixat]

i.e. your idea is to have an unique record for each placeholder in every language, which was my initial thought, but if the site is planned to become a heavy one ( as in my case ) I suppose there will be an enormous number of requests to the database ( and maybe not an "enormous" number but I'm gonna have a bad time retrieving all those records ), which led me to the idea of every page having a record containing a serialized string of all the translations needed for that page. But this method has it's down sides as well. If I use it then if some pages have partly identical content I'm going to have to store it twice, which is not a big deal in today's storage capabilities, or not a deal at all, but just doesn't give you that warm feeling that you've done your job right. What do you think?

 

And knowing my crappy explaining I best give an example

 

Table structure: 

 

language_id || page || string

 

where language ID needs no explanation,

page is the name of the page that requests translation

and string is the serialized ( or object-like if you will ) string containing all the placeholders in the current language

Edited March 27, 2013 by Manixat

[kicken]

Have a separate row per phrase, but rather than select each translation individually, select all the translations for the target language at once and store them in an array. If you have a huge number of phrases you could further break things down by page or area (ie, separate front-end and back-end (admin) areas).

 

Also you might check into something like gettext, then you don't have to use a bunch of ID numbers or constants as placeholders, you can just use the normal text.

[Psycho]

I agree with kicken. I wouldn't make this more complicated than it needs to be. Your proposed database design in post #3 would be a perfectly normalized design. But, that is not always needed or preferred. I would have to assume that for any languages supported you will always have a value for each "placeholder" for each language. Plus, adding a new language is not something that is done in piecemeal. It should be done all at once. I would have a single table that has a field/column for the text ID or placeholder tag and then additional fields/columns for each each language

 

id       | text_en   | text_de        | text_sp
username   User name   Gebruikersnaam   Usero Nameo

 

Then, when you need the text for a page, only SELECT the column with the text you want based on the user's language. As kicken suggested you could also filter the query based on the specific texts that you need. Something like:

 

$lang = 'en'; //This is determined programatically

$query = "SELECT text_{$lang}
          FROM translations
          WHERE ID IN ($commaSeparatedListOfPlaceHolderIDs)";

[DaveyK]

I see your points, and I agree. As I said, I never actually built it so I just went with a guess. But if you serialize your data inside the table you will lose the functionality...

 

and "Gebruikersnaam" is Dutch (NL), not German (DE).

[Manixat]

 

| text_sp
  Usero Nameo

 

lol..

 

Thank you for the ideas guys, I suppose the last one with the one row per phrase with all languages might turn out to be the best choice, and will most certainly be the one I'll use.

 

Thank you once again!

[Psycho]

 . . . and "Gebruikersnaam" is Dutch (NL), not German (DE).

 

and "Usero Nameo" is not Spanish.

[jazzman1]

"Usero Nameo" is not Spanish.

It sounds like a romanian language

[DaveyK]

Okay I know this thread is no longer actively discussed, but some thought hit me. What if you keep everything perfectly normalized like I suggested, but create a SQL view and fetch from that view? I have never used SQLs views before but it just seems usefull to keep everything normalized if you want to translate things on the go (or even let users vote on translations).

[InoBB]

Another way to do this is to make a php file per language. You could then have a select box to let your users choose languages, or even just little flag links whatever your flavor. And save their choice to a cookie once they have selected or clicked so they don't have to keep doing it over and over.

 

Example lang.en.php: 

<?php

$lang = "";

if (empty($lang) || !is_array($lang)) {
	$lang = array();
}

$lang = array_merge($lang, array(

    'USER_NAME'        => 'User Name',
    'PASSWORD'         => 'Password',
));

?>

and so on just go down the list adding different phrases from your website until you've got them all covered.

In your pages to be translated just change the phrase to a variable of $lang

<?php

include('lang.en.php');

....
//some PHP codes
   echo $lang['USER_NAME'];
   echo $lang['PASSWORD'];
....

?>

You can do this for every language you intend to be used, works extremely well, even for Arabic then you can just call CSS to turn the text right to left when the arabic language is called etc. PHP is very powerful on it's own, use the database for more complex information.

 

BTW don't forget to setcookie(); once your user has picked their language.

[DaveyK]

But what about my idea about storing it normalized, use a SQL view for fetching and perhaps cache it to a file to be refreshed every once in a while... ?

[InoBB]

There are several different ways this could be achieved using SQL, and using VIEWS would be one. It definitely would work.

http://www.tutorialspoint.com/sql/sql-using-views.htm