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


10 Comments »

  1. Comment by Ben Thatcher — January 12, 2010 @ 6:00 pm

    nice tutorial! I’m taking databases this semester, and I think we’re going to learn SQL and MySQL…

  2. Comment by Igor — March 16, 2010 @ 7:10 am

    It seems that the code is of high quality, although I can’t judge because I’ve just started learning PHP and MySQL, and I’d say you’re using object oriented programming which I don’t have a clue about, at least for now. Also the code seems to allow for additional features later if needed.

    Anyway, thank you very much for this tutorial—it’s been stored in my bookmarks to reference it when I’ve learned the above mentioned language/database management system to the point where I’m able to understand the code completely.

  3. Comment by Kenny — March 16, 2010 @ 9:35 am

    >Ben
    Glad you liked it!

    >Igor
    Thanks for the comment. If you have ANY questions at all feel free to post them!

  4. Comment by DevGutt — April 12, 2010 @ 8:08 am

    What´s better performance?, gettext or a db solution like? i appreciate any help.

  5. Comment by Kenny — April 15, 2010 @ 12:25 pm

    In terms of performance. For a small website, there probably won’t be much performance issues. However,On a page where there are many text fields being displayed + lots of users, you would not want to make a million different SQL calls. Instead you would want to modify the code to query ALL the text fields on the page in a SINGLE QUERY, load them into an associative array, then display them.
    I think this would be more efficient.
    Thanks for your input, I will make a more “efficient” version of this later. what do you think?

  6. Comment by Jorrit — April 29, 2010 @ 1:48 am

    Thanks Ken! I try to use your script for a school assignment. I created the MySQL database. When I try to run the script, there is something wrong with the echoText. In each line i get this mistake. What could be the problem? Thanks in advance

  7. Comment by Kenny — April 29, 2010 @ 4:52 pm

    can you print out the error?
    in case it’s a DB error just change
    mysql_query($sql);
    to
    mysql_query($sql) or die(mysql_error());

    You said created the Database, are you sure that you inserted the appropriate text_id’s?

  8. Comment by gordon — August 21, 2010 @ 4:09 am

    Hey, Jorrit. I got the same problem and found a simple explanation to it. For those who receive output of the index.php in the browser like this
    echoText(’LANG_EN’, $lang); ?>|echoText(’LANG_JP’, $lang); ?>|echoText(’LANG_ZH’, $lang); ?>
    and so on….

    the problem is with the short_open_tags used in the code
    <? - short open tag
    <?php - full open tag

    so simply replace all the short tags with full (NB: this conserns only open, not closing tags)
    OR
    enable short_open_tag in the php.ini file of your server.

    P.S. Sorry if my English is bad somewhere, cause I’m not a native english speaker

  9. Comment by gordon — August 21, 2010 @ 11:16 am

    Hey, could someone give me an idea on how to pass this ?lang=jp value when the user switches the page? Would apreciate that very much

  10. Comment by Kenny — August 21, 2010 @ 7:44 pm

    @gordon
    Yes, thanks, I updated the code so that it uses < ?php ?> instead of < ? ?>

    I also got rid of “echoText()” so now you just echo out the results of “getText()”, which makes more sense :P
    Also, if you’re interested i would recommend making a function that accepts an array of textID’s, and makes a SINGLE SQL call that returns an Array of text to prevent multiple SQL calls. (I will update later)

    As to your passing the variable around from page to page. Either store it in a SESSION variable or load it from stored User information.

RSS feed for comments on this post. TrackBack URI

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="">

Copyright © 2009 www.Ken-Soft.com