Page 1 of 1

Approach to multi-language support website

Posted: Tue Aug 25, 2009 11:58 am
by rhecker
I am in the process of creating a website that will need to support three languages--English, Vietnamese, and Chinese. In this post I don't have a specific question. I simply want to describe the approach I am taking and find out if anyone thinks there is a better method.

I am creating columns in my mySQL tables that contain the webpage text, with names like en_text, vn_text, ch_text. A variable called $language will be set to either en_, vn_, or ch_. Each page will contain links to allow visitors to select the appropriate language, like so:

<a href="pagename.php?language=en_">ENGLISH</a> <a href="pagename.php?language=vn_">VIETNAMESE</a> <a href="pagename.php?language=ch_">CHINESE</a></p>

A SESSION variable called language will also be set, so the visitors language choice will be persistent.

The above seems fine to me, but the method I am using to tell the database which column to pull text from seems clumsy. Here it is:
<?php $list=mysql_query("SELECT * FROM instructors");
while ($individual= mysql_fetch_array($list)){
extract ($individual);
$description1= $language . "inst_description";
$description2= $individual[$description1];
$name1= $language . "inst_name";
$name2= $individual[$name1];
. . .
?php echo $name2 ?></strong><br/><?php echo $description2 ?>


Any thoughts?

Re: Approach to multi-language support website

Posted: Tue Aug 25, 2009 12:07 pm
by Darhazer
This is not the right database design. What if you have to add another language.
You should have 2 tables for each entity.
One that contains the general data about the entity, and another for the ML fields, containing one row per language
Someting like:
Instructors
----------------
InstructorID
UserID // or some other that would be the same for many records

InstructorsML
--------------------
InstructorID
LanguageID
InstructorName
InstructorDescription

Re: Approach to multi-language support website

Posted: Tue Aug 25, 2009 2:02 pm
by rhecker
Thanks. I see your point. We may indeed add more languages.

Re: Approach to multi-language support website

Posted: Tue Aug 25, 2009 2:50 pm
by Darhazer
rhecker wrote:Thanks. I see your point. We may indeed add more languages.
It's not just my point, this is called first normal form of a database. Read about database normalization, it's really necessary to know the rules when you are designing a DB.