paolo@bimodesign.com | +34 608 61 64 10

DB Relational

        

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