help retrieving text for selected language

I have a site in English and French and am trying to set it up to retrive the text from a table so I don't have 2 pages with the same code.

eg toady I have pages like

I now want to have one page called dsp_home.cfm and retrive the text from a table tblMessages.

I have set up the query below at the top of the the idex.cfm

My question is how do I call the text from the query.

eg if the language is set to English how would I get the text from this sample table:
ID      English      French
1      Hello      Bonjour
2      Home      Accueil


my query to get text for appropriate language:
<cfif request.language is "english"> 
<cfquery name="lmsg" datasource="#application.DS#" username="#application.dbuser#" password="#application.dbpass#">
SELECT     MessageID, English as mtext
FROM         tblMessages
<cfelseif request.language is "french">
<cfquery name="lmsg" datasource="#application.DS#" username="#application.dbuser#" password="#application.dbpass#">
SELECT     MessageID, French as mtext
FROM         tblMessages

Open in new window

Who is Participating?
azadisaryevConnect With a Mentor Commented:
you can use array notation to output data from a specific row in a query:


so, in your case, that would be:


IF the value 'HOME' is returned in the 2nd row of your query - remember that rownumber does not necessarily equal ID of the record!

ShawnAuthor Commented:
or maybe there's a better approach to this. I'm open to suggestions.
if the value of request.language var matches the name of the language's db table column -depending on your db and os, table names may be case-sensitive - you could do something like this:

SELECT     MessageID, #request.language# as mtext
FROM         tblMessages

be very careful with this approach, though! it can leave you quiet open to SQLi, if you are not extremely careful about how request.language var gets set...

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

ShawnAuthor Commented:
with my query I don't actually need the varialbe #request.language# though it is shorter.

What I am not sure how to do is retrieve say the ID 2. If the language was set to English how would I get "Home" to show in a variable like:      #lmsg.2#

I have tried: <cfoutput> Message is this: #lmsg.mtext# </cfoutput>
but this only gives me the first record ("Hello").

I'm pretty sure I've seen somewhere where if you put the ID or a unique name in th evariable you can output another field in the same record. eg for ID 2 >>> #lmsg.2# (or something similar) should output "Home".

hope this is a little clearer. :)

ShawnAuthor Commented:
getting closer. I really can't rely on the row though.

I think there might be something we can use here
ShawnAuthor Commented:
thanks Azadi,
that put me in the right direction. I ended up using:

right under the query: <!---  load into a structure --->
<cfloop query="lmsg">
  <cfset variables.FieldLabels[lmsg.MessageID] = lmsg.mtext>

cfoutput> #variables.FieldLabels['2']#</cfoutput>
good job!

ShawnAuthor Commented:
thank you. works nicely.

feel free to jump in on this one. not sure how to approach it.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.