Multilingual database - using a variable instead of a column name in Mysql

[sunilwije]

I have following code snippet to implement a multilingual website.
Idea is to, depending on user's language i need to translate word "Man" to "Hombre"

My Question: Will it be valid to use $user_language instead of hard coded column name "Spanish" here.

code:

//$user_language is passed from his profile after log in to the site

SELECT $user_language 					
FROM TranslationTable					
WHERE  English=Man

TranslationTabl e:

English Spanish
Man Hombre

Thanks
SW

[Luuk]

You did not define your database correct.

More correct would be:
Language Word
English Man
Spanish Hombre

But than you will need an extra identifier to link these together like:
Language Id Word
English 123 Man
Spanish 123 Hombre
English 124 Horse
Spanish 124 Caballo

A query to get the translation for 'Man' would be:

SELECT S.Word
FROM table E
JOIN table S ON E.Id=S.Id and S.Language='Spanish'
WHERE E.Language='English' and E.Word='Man'

[Rabbit]

It looks like you're mixing PHP and MySQL.... if you're using PHP to pass a query to your database, then I need to move this to the PHP forum. If you are using a different scripting language, I need to know what that is so I can move it.