MySQL/PHP, manage multi languages
In order to manage a lot of different languages (as russian, arabic, chinese and all european languages) I decided to create an only table that will contain the translation of all the texts of my application. The table schema is:
CREATE TABLE IF NOT EXISTS `texttranslate` ( `id_texttranslate` int(11) NOT NULL AUTO_INCREMENT, `id_language` int(11) NOT NULL, `text_translate` text, PRIMARY KEY (`id_texttranslate`), KEY `fk_texttranslate_language_idx` (`id_language`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;
How you can notice, I set the charset to UTF-8 that will cover all the possible languages.
But of course, it's not enought, cause an application include several parts, as
- DB connection
That I've set in this way:
$pdo = new PDO('mysql:dbname=paolo;host=localhost', 'root', 'paolo', array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
- Tag Header of HTML page
header('Content-Type: text/html; charset=utf-8');
- Tag Header of Form
form action="" method="post" accept-charset="utf-8"
Note: Currently the application doesn't plan a procedure to import a file with all translation text, but in order to generate a new language, I'll using an INSERT INTO texttranslate SELECT... from the backend application.
The only problem that I could have, it will be to manage the PHP strings, for example with the strlen() function, because the UTF-8 is a multibyte coding. So in this case I'll use the mb_strlen() as explain here
In this link you'll find an excellent explication about this problem