Professional web sites do not rely on Google Translator or other automated tools. Although we can have an idea of the translated subject, not always the translation is 100% accurate and could cause problems for the site owner. Nothing wrong with the client submitting the page to the translation tool, since it is his option and decision to do that, but if you wish to construct a professional multi-lingual web site, I recommend that you spend the time and resources translating all the content by using a human translator.

I have a simple and fast method of building a multi-lingual web site. First you should start by creating a table with all the languages you will use on the site. Think of that as a spreadsheet where each line will be a field or text to be displayed on your pages, and each column will be the selected language. Translate all your content and feed that table. Then, on your Application.cfm or Application.cfc file, load the entire table to the application scope. In the company I work, we load it to the server scope, since we have several multi-lingual web sites, and that saves memory in our case, since we are not loading the same table for each site.
Once the application (or server, in my case) started, you will have the entire dictionary available for all pages. For each page you then call a function to load only the labels or content you need for that page into a structure (I call it: lang), and then you output it on your page as for example: #lang.save# which would display the word “save” in English or “sauvegarder” in French.
You may download my sample application here, which includes the component, spreadsheet, table script, application.cfm and sample code.

At the start of the application, I always read the entire table into the application scope to a query named: application.dictionary. I also set some key structures to work on the site.
Here is the code for your Application.cfm or Application.cfc:
{code type=coldfusion}<cfscript>
application.mainDSN = “myDatabase” ;
application.site = structNew();
application.site.defaultLanguage = “EN”;
application.site.languages = “EN,FR,DE,ES,PT”; // list of languages allowed for this site
application.oLang = createObject(“component”, “dictionary”); //create the object
thisPage = structNew(); //create structure for page verbs and other stuff
</cfscript>
<!— if the dictionary is not already loaded into application scope, then load it —>
<cfif not structKeyExists(application, “dictionary”)>
<cflock scope=”Application” type=”exclusive” timeout=”10″>
<cfset application.dictionary = oLang.init() />
</cflock>
</cfif><{/code}

This piece of code below you should put at the beginning of each page, which sets the content you want to translate for such page:
{code type=coldfusion}<cfprocessingdirective pageencoding=”utf-8″ suppresswhitespace=”true”>
<cfparam name=”url.lang” default=”#application.site.defaultLanguage#” />
<cfparam name=”client.lang” default=”#application.site.defaultLanguage#” />
<!— Here goes the field list to retrieve from the dictionary table —>
<cfset thisPage.wordList = “name,address,city,state,zipCode,selectLang,save,requestMoreInfo” />
<cfset lang = application.oLang.setLanguage(url.Lang, thisPage.wordList) />{/code}
Here is the component I wrote to set the selected language:
{code type=coldfusion}<cfcomponent>
<cffunction name=”init” access=”remote” output=”false” returntype=”Query”>
<cfquery name=”get” datasource=”#application.mainDSN#”>
select fieldName, #application.site.languages#
</cfloop>
from dictionary
</cfquery>
<cfreturn get />
</cffunction>
<cffunction name=”setLanguage” access=”remote” output=”false” returntype=”Struct”>
<cfargument name=”selectedLanguage” type=”string” required=”false” default=”#application.selectedLanguage#” />
<cfargument name=”wordList” type=”string” required=”false” default=”” />
<cfset arguments.wordlist = listQualify(arguments.wordList,”‘”,”,”) />
<cfif not listFindNoCase(application.site.languages, arguments.selectedLanguage)>
<cfset arguments.selectedLanguage = application.site.defaultLanguage />
</cfif>
<cfif arguments.selectedLanguage neq client.lang>
<cfset client.lang = arguments.selectedLanguage />
</cfif>
<cfquery name=”get” dbtype=”query”>
select #arguments.selectedLanguage#, fieldName from application.dictionary
where 1=1
<cfif len(trim(arguments.wordList))>
and fieldName in (#preserveSingleQuotes(arguments.wordList)#)
</cfif>
</cfquery>
<cfset lang = structNew()>
<cfloop query=”get”>
<cfif not structKeyExists(lang, get.fieldName)>
<cfset structInsert(lang, get.fieldName, evaluate(“get.#arguments.selectedLanguage#”)) />
</cfif>
</cfloop>
<cfreturn lang />
</cffunction>
</cfcomponent>{/code}

Here is script for the table:{code type=coldfusion}USE [myDatabase]
GO
/****** Object: Table [dbo].[dictionary] Script Date: 08/04/2008 00:32:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dictionary](
[fieldName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[en] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[fr] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[de] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[pt] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[es] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF{/code}

10 thoughts on “Populating a Multi-Lingual Site from a Database

  1. Dude there are some errors when i run this, i just exactly replicated as yours but one exception, my database is currently empty and when i run the index.cfm page:

    i jst prompt the error:

    Element REQUESTMOREINFO is undefined in LANG.

    The error occurred in C:\Inetpub\wwwroot\core\index.cfm: line 19

    17 :
    18 :
    19 : #Capitalize(lang.requestMoreInfo)#
    20 :
    21 :

  2. Gavy,
    You need to populate the database with the words in the languages you want to use. The error “Element REQUESTMOREINFO is undefined in LANG” is because you requested a field “requestMoreInfo” which is not defined in your database.

  3. Do i need to put to this list in fieldName [varchar50] of datbase.

    this line: name,address,city,state,zipCode,selectLang,save,requestMoreInfo

    and then the changed values in below fields where fr, es stc are defined.

    and what if my data is dynamic, will that convert it automatically.

    Regards

  4. @Gavy
    Please download the sample application I have in this post. I just updated it and it includes a spreadsheet with sample data for you to populate the dictionary table. I ran this test application successfully here.
    Let me know if you have more questions.

  5. Can the multilanguage work like this way?
    1) Set up all langueage in a Application Schope
    :
    application.Lang.#countryCode#.#fieldname#= “#value#”
    ……..

    The result will be :
    application.Lang.Eng.Name = “Name” …
    application.Lang.French.Name = “SomethngElse” …

    Then we just call the variable by the language selected in the page, via cookie or session variable, or a drop down selected box in the login page will do?

    Im not sure how your program work if i change the value in the language table.

    What i worry is if the table value change, we need to refresh the variable value.

    Maybe we can have a maintenance site, like provide a button, click it, then refresh all the Application value again?

    keong

    1. @Keong,
      Usually, in our applications expect an url parameter called “serverinit”. If that parameter is passed like serverinit=true, then we reinitialize the dictionary reading, I mean, we re-read the entire dictionary to the server scope structure and also to the application scope structure.
      Whenever we add a new word to the dictionary or change any field (which is very rare to happen), we have to reinitialize the language structure.
      In our way, you have to write less, for example, does not matter the language selected, you only write “lang.name” and if the selected language is English, it will display “name”, or if it is French, it will display “nom”, if it is Portuguese it will display “nome”.
      At the beginning of the template you can choose either to read the entire dictionary to the structure “lang” or only the fields that you will use.
      The way you described, “application.lang.Eng.name” will work too, but remember, you will be accessing the application scope for every word you have to display. Have you thought about locking the scope with CFLOCK ? We do that only once at the beginning of the template when loading to the “lang” structure. Then being lang a structure in the “variables” scope, we do not need to lock it.

      Thanks for your comments.
      Ricardo.

  6. Hi,

    Thank for your explanation.

    For those value in the table, do your store as Hex rather than TEXT?

    eg Text = Save
    HEX for Japan = &2345; &23453; &3245
    HEX for Korean = &45334; &34r34

    I think is easier for the browser to store Hex raither than text due to browser conversion issue, but HEX will take more space than just TEXT.

    Any comment?

    Keong

    1. @Keong
      That’s your choice, how to store the values, you may use HEX or text, it depends on how much space you want to save. Remember that ColdFusion default charset is UTF-8, so if you would store text, make sure the datasource registered allows for unicode and use nvarchar or ntext for your columns.
      Ricardo.

  7. Hi Ricardo,
    Thank for your update.
    I have an issue here. in the dictionary table, the value of the column is “My First System #app.version#”, the #app.version# value is 1.1.

    When i try to display in the browser, it show “My First System #app.version#” instead of “My First System 1.1”.

    How i can showed My First System 1.1?

    Regard
    Keong

  8. @Keong,
    Sorry for the long delay in replying, I never received your comment to my email, and only today I saw it online.
    On the template (page) you are displaying “My First System”, did you encapsulated it in tags ?
    Regards,
    Ricardo

Leave a Reply