Create a Multilingual Website in PHP using a MySQL database
In a previous post I showed how to make a simple multilingual website using a hash array. This post will demonstrate how to design a multilingual website in PHP backed by a MySQL database. To see a simple demo open the following link HERE
1. Create the DataBase
First create a MySQL Database called language (or whatever you want). Next, copy the below code into a file (language.sql) and import into your database (import as utf8). You can of course also enter the commands manually. Each language is in it’s own database and has a charset of utf8 to support multiple languages. Every ‘text’ entry is referenced by a corresponding ‘textid’. The textid, for example ‘FNAME’ will appear in each database, but will correspond to a different word depending on the langauge.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | CREATE TABLE IF NOT EXISTS `lang_en` ( `textid` varchar(255) collate latin1_general_ci NOT NULL, `text` longtext charset utf8 collate utf8_unicode_ci NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE IF NOT EXISTS `lang_jp` ( `textid` varchar(255) collate latin1_general_ci NOT NULL, `text` longtext charset utf8 collate utf8_unicode_ci NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE IF NOT EXISTS `lang_zh` ( `textid` varchar(255) collate latin1_general_ci NOT NULL, `text` longtext charset utf8 collate utf8_unicode_ci NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `lang_en` (`textid`,`text`) VALUES ('FNAME','First Name'),('LNAME','Last Name'),('SUBMIT','Submit'),('LANG_EN','English'),('LANG_JP','Japanese'),('LANG_ZH','Chinese'); INSERT INTO `lang_jp` (`textid`,`text`) VALUES ('FNAME','名'),('LNAME','姓'),('SUBMIT','確認'),('LANG_EN','英語'),('LANG_JP','日本語'),('LANG_ZH','中国語'); INSERT INTO `lang_zh` (`textid`,`text`) VALUES ('FNAME','名'),('LNAME','姓'),('SUBMIT','确定'),('LANG_EN','英语'),('LANG_JP','日语'),('LANG_ZH','中文简体'); |
2. Create Language Class in PHP
The below class is just one implementation, so feel free to customize this however you want to fit your needs. Note the usage of the default language. I implement this just in case a user stumbles across a page where the text is not translated so that it can resort to a default. Some text looks better than none
Having everything in one function getText() or echoText() makes it easy for me to affect all the text on the whole website at once. It also allows me to later add in a “Suggest better translation ” button, or any other number of features that you wish.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | <?php // AUTHOR: KENNY CASON // WEBSITE: WWW.KEN-SOFT.COM class Language { public $defaultlang = "en"; // Default Language function Language($defaultlang="") { if($defaultlang != "") { $this->defaultlang = $defaultlang; } } // get text from DB public function getText($textid, $lang="") { if($lang == "") { $lang = $this->defaultlang; } $query = "SELECT text FROM lang_$lang WHERE textid='$textid'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $text = $row[0]; if($lang == $this->defaultlang) { // if language is default, return the langauge unchecked. return $text; } else { if($text != "") { // Is text field un-translated? (i.e. not empty string) return $text; } else { // If not, query using default language return $this->getText($textid, $this->defaultlang); } } } // get and echo text from DB public function echoText($textid, $lang="") { echo $this->getText($textid, $lang); } } |
3. Put everything together
Finally we will create our index.php file to demonstrate how to use everything. In the beginning of the code connect to the Database of your choice. For the purpose of this simple demo the language parameter ($lang) can be passed to the page, and is English (en) by default. This could ideally be queried from the users profile, etc. Also notice that each time you want to print text just call the appropriate function in the Language class. The below example demonstrates to ways to print the Japanese text (名) referred to by the textid FNAME
1 2 3 4 5 | $lang = "jp" // Japanese // create a new Langauge Object $obj_lang = new Language(); $obj_lang->echoText('FNAME', $lang); echo $obj_lang->getText('FNAME', $lang); |
Two other VERY IMPORTANT lines are:
1 | <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/> |
Set the page incoding to UTF-8.
1 | mysql_set_charset('utf8', $con); |
AND, set the charset for your mysql queries. Otherwise you’ll get a bunch of “?”’s and likely wonder what it is your doing wrong… or at least I did. ![]()
Here is the whole index.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | <?php include_once("language.php"); // connect to a MySql DB, usually have a seperate file, or DB class to do this. $host = 'dbhost'; $user = 'dbuser'; $password = 'dbpass'; $database = 'dbname'; // I call mine language $con = mysql_pconnect($host, $user, $password); mysql_select_db($database,$con); mysql_set_charset('utf8', $con); // create a new Langauge Object $obj_lang = new Language(); // ideally pull this from a users profile. $lang = $_REQUEST["lang"]; ?> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/> </head> <body> <a href="index.php?lang=en"><? $obj_lang->echoText('LANG_EN', $lang); ?></a>|<a href="index.php?lang=jp"><? $obj_lang->echoText('LANG_JP', $lang); ?></a>|<a href="index.php?lang=zh"><? $obj_lang->echoText('LANG_ZH', $lang); ?></a> <form> <? $obj_lang->echoText('FNAME', $lang); ?>:<input type="text" name="firstname" /><br /> <? $obj_lang->echoText('LNAME', $lang); ?>:<input type="text" name="lastname" /><br /> <select name="lang"> <option value="en"><? $obj_lang->echoText('LANG_EN', $lang); ?></option> <option value="jp"><? $obj_lang->echoText('LANG_JP', $lang); ?></option> <option value="zh"><? $obj_lang->echoText('LANG_ZH', $lang); ?></option> </select><br /> <input type="submit" value=<? $obj_lang->echoText('SUBMIT', $lang); ?> /> </form> </body> </html> |
Again, the link to the demo using the following code can be found HERE
Hope this is useful to someone.








































