Ken-Soft

Software and the World

  • 日本語
  • 한국어
  • 中文
  • italian
  • Portugese
  • Espanol
  • German

Create a Multilingual Website in PHP using a MySQL database

Posted under Language Software, PHP, Programming, Software Development, Web Development by Kenny on Tuesday 12 January 2010 at 3:32 pm

Bookmark and Share

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
19
20
21
22
23
24
25

CREATE TABLE IF NOT EXISTS `languages` (
  `lang` varchar(255)  NOT NULL,
  `description` varchar(255)   NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
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 `languages` (`lang`,`description`) VALUES ('en','English'),('jp','Japanese'),('zh','Chinese');
 
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 get() 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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169

<?php
/**
 * Small Wrapper class to manage Languages stored in a MySQL DB
 * Extended functionality will be added soon
 * @author Kenny Cason
 * @site www.ken-soft.com
 */
class Language {
 
    var $defaultlang = "en";	// Default Language
 
    var $languages = array("en");
 
    // mysqli instance;
    var $db;
 
    function __construct($db="", $defaultlang="") {
        if($db != "") {
            $this->db = $db;
        } else {
            $this->db = new mysqli();
        }
        if($defaultlang != "") {
            $this->defaultlang = $defaultlang;
        }
        $this->languages = $this->getLanguageIDs();
    }
 
    /**
     * Get text from DB given a specific Text ID and Language
     */
    public function get($textid, $langid="") {
        if($langid == "") {
            $langid = $this->defaultlang;
        }
      	$query = "SELECT text FROM lang_$langid WHERE textid='$textid'";
        $result = mysql_query($query) or die(mysql_error());
        $row = mysql_fetch_array($result);
        $text = $row[0];
        if($langid == $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->get($textid, $this->defaultlang);
            }
        }
        return "";
    }
 
    /**
     * Return an Array of Language IDs of each language in the Database
     */
    public function getLanguageIDs() {
        $languages = array();
        $sql = "SELECT lang FROM languages";
        $result = mysql_query($sql);
        $i = 0;
        while($row = mysql_fetch_array( $result )) {
            $languages[$i] = $row[0];
            $i++;
        }
        return $languages;
    }
 
    /**
     * Return an Array of Descriptions of each language in the Database.
     */
    public function getLanguageDescriptions() {
        $languagesDesc = array();
        $sql = "SELECT description FROM languages";
        $result = mysql_query($sql) or die(mysql_error());
        $i = 0;
        while($row = mysql_fetch_array( $result )) {
            $languagesDesc[$i] = $row[0];
            $i++;
        }
        return $languagesDesc;
    }
 
    /**
     * Delete a Text ID from all Language Tables
     * @param $textid
     */
    public function deleteTextID($textid) {
        if($textid != "") {
            $numLanguages = count($this->languages);
            for($i = 0; $i < $numLanguages; $i++) {
                $sql = "DELETE FROM lang_".$this->languages[$i]." WHERE textid='$textid'";
                $result = mysql_query($sql) or die(mysql_error());
            }
            $status = "Deleted TextID ".$textid;
        }
        return $status;
    }
 
    /**
     * Update Text for a specific Language
     * @param $langid
     * @param $textid
     * @param $text
     */
    public function updateText($langid, $textid, $text) {
        if($langid != "" && $textid != "" && $text != "") {
            $sql = "UPDATE lang_$langid SET text='$text' WHERE textid='$textid' ";
            $result = mysql_query($sql) or die(mysql_error());
            $status = "Updated Entry";
        }
	return $status;
    }
 
    /**
     * Delete a Language from the Database
     * @param $langid
     */
    public function deleteLanguage($langid) {
    	if($langid != "") {
            $sql = "DROP TABLE IF EXISTS lang_".$langid;
            $result = mysql_query($sql) or die(mysql_error());
            $sql = "DELETE FROM languages WHERE lang='$langid'";
            $result = mysql_query($sql) or die(mysql_error());
        }
	return "Deleted Language Table ".$langid;
    }
 
    /**
     * Add TextID field to all language databases
     * @param $textid
     */
    public function addTextID($textid) {
        $numLanguages = count($this->languages);
        for($i = 0; $i < $numLanguages; $i++) {
            $status = $status.$this->languages[$i];
            $sql = "INSERT INTO lang_".$this->languages[$i]." (`textid`, `text`) VALUES ('$textid', '')";
            $result = mysql_query($sql) or die(mysql_error());
        }
        $status = $status."Added Text ID";
        return $status;
    }
 
    /**
     * Add a new Language
     * @paam $langID is typically a 2 letter ID (English is 'en', Japanese is 'jp')
     * The created database is 'lang_$langID' (lang_en, lang_jp)
     * @param $langDesc is the Description for the language.
     */
    public function addLanguage($langid, $langdesc) {
    	if($langid != "" && $langdesc != "") {
            $sql = "INSERT INTO languages (`lang`, `description`) VALUES ('$langid', '$langdesc')";
            $result = mysql_query($sql) or die(mysql_error());
            $sql = "CREATE TABLE IF NOT EXISTS `lang_$langid` (
        	`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;";
            $result = mysql_query($sql) or die(mysql_error());
            $sql = "SELECT textid FROM lang_en";
            $result = mysql_query($sql) or die(mysql_error());
            while($row = mysql_fetch_array($result)) {
                $sql = "INSERT INTO lang_$langid (`textid`, `text`) VALUES ('$row[0]', '')";
        	$result2 = mysql_query($sql) or die(mysql_error());
            }
            $status = "Added Language";
	}
        return $status;
    }
 
}
?>

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

$lang = "jp" // Japanese
// create a new Langauge Object
$obj_lang = new Language($db);
echo $obj_lang->get('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

$db->set_charset('utf8');

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. :P
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
require_once("Language.php");
 
// connect to a MySql DB, usually have a seperate file, or DB class to do this.
$DB_HOST = '<HOSTNAME>';
$DB_USER = '<USERNAME>';
$DB_PASSWORD = '<PASSWORD>';
$DB_DATABASE = 'language';
$DB_PORT = <PORT>
 
$db = new mysqli($DB_HOST, $DB_USER, $DB_PASSWORD, $DB_DATABASE,$DB_PORT);
$db->set_charset('utf8');
 
 
// create a new Langauge Object
$obj_lang = new Language($db);
 
// 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"><?php echo $obj_lang->get('LANG_EN', $lang); ?></a>|<a href="index.php?lang=jp"><?php echo $obj_lang->get('LANG_JP', $lang); ?></a>|<a href="index.php?lang=zh"><?php echo $obj_lang->get('LANG_ZH', $lang); ?></a>
	<form>
		<?php echo $obj_lang->get('FNAME', $lang); ?>:<input type="text" name="firstname" /><br />
		<?php echo $obj_lang->get('LNAME', $lang); ?>:<input type="text" name="lastname" /><br />
		<select name="lang">
			<option value="en"><?php echo $obj_lang->get('LANG_EN', $lang); ?></option>
			<option value="jp"><?php echo $obj_lang->get('LANG_JP', $lang); ?></option>
			<option value="zh"><?php echo $obj_lang->get('LANG_ZH', $lang); ?></option>
		</select><br />
		<input type="submit" value=<?php echo $obj_lang->get('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.
Note: these are recent updates. Let me know if you find any bugs. :) thanks

Share on Facebook

Kana Tutor

Posted under Japan, Japanese, Language Software, Learning, Linguistics by Kenny on Thursday 25 December 2008 at 12:36 am

Bookmark and Share

This is some simple software a wrote a while back for studying Katakana(カタカナ) and Hiragana(ひらがな), Japanese two alphabets (not including Kanji(漢字)). Unfortunately I have only ported it to Windows.

This Software will eventually be replaced with an online PHP page, so check back time to time!

Download Here

Japanese Kana Tutor Katakana hiragana Japanese Kana Tutor Katakana hiragana Japanese Kana Tutor Katakana hiragana
Share on Facebook

Copyright © 2009 www.Ken-Soft.com