Multi table or one filed more is the same for all ressources : they must be modified in all cases.
If we used multi tables, table name can be retrieved with
$userId = $modx->getLoginUserID();
$table = $modx->getFullTableName("user_messages").'_'.$_SESSION['lang'];
$messages = $modx->db->select("subject, message, sender", $table, "recipient = $userId", "postdate DESC", "10");
$modx->getFullTableName("xxxx");
In the case of we use added field, we must add in the request a "where statement" as :
$userId = $modx->getLoginUserID();
$table = $modx->getFullTableName("user_messages");
$messages = $modx->db->select("subject, message, sender", $table, "recipient = $userId and lang=$_SESSION['lang']", "postdate DESC", "10");
$modx->getFullTableName("xxxx");
There are no many difference to implement it.
In your need, if you use two tables, you can use a sql request with "UNION" to get all records for content with different language.
It is necessary to make a request for only 2 language tables of content because
I need to switch from the desired language to the default language each time the user arrives in a page that is not translated
$table_localized = $modx->getFullTableName("user_messages").'_'.$_SESSION['lang'];
$table_default = $modx->db->select("manager_language", $modx->getFullTableName("setting_name"), "setting_name='system_settings'");
$sql = "SELECT content FROM $table_default WHERE id=15 UNION SELECT content FROM $table_localized WHERE id=15";
$result = $modx->db->query($sql);
// 1st method
if (mysql_num_rows($result)==1) {
$content = mysql_result ( $res, 0);
}
else {
$content = mysql_result ( $res, 1);
}
// 2nd method
// if num rows == 2 then TRUE (1) (get the row number 1 to get the translated content)
// else FALSE (0) (get the row number 0 to get the default content)
$content = mysql_result ( $res, mysql_num_rows($result)==2);
If you use one table for all :
$table = $modx->getFullTableName("user_messages");
$sql = "SELECT content FROM $table WHERE id=15 OR (parent=15 AND lang='fr')";
$result = $modx->db->query($sql);
// 1st method
if (mysql_num_rows($result)==1) {
$content = mysql_result ( $res, 0);
}
else {
$content = mysql_result ( $res, 1);
}
// 2nd method
// if num rows == 2 then TRUE (1) (get the row number 1 to get the translated content)
// else FALSE (0) (get the row number 0 to get the default content)
$content = mysql_result ( $res, mysql_num_rows($result)==2);
There are no many difference to implement it.
So, I always prefered multi tables for this reasons :
Use of parent id in row translated to refer default row is not very clean : it permit to resolve the problem but parent id is not a field to make a link between different language.
Smaller the tables are, more MySQL queries are fast. With multi table, MySQL parse one time a medium table for the first SELECT with a WHERE on one medium index and one time a small table for the SECOND with a WHERE on one small index. With one table, MySQL parse one time a big table with a WHERE on two big index with condition. At the release of version 4 of mysql, MySQL said that is faster to parse 2 medium tables with one index than to parse one table with 2 indexes. Maybe that changed....
Your solution use simplier query than multi tables solution. It permit to answer to precise solution.
Now if you want to use comments on other docments on your website, you need to hack UserComments to create a query as "select * from site_content where parent=15 and lang is null". Maybe other snippet require hacks.
If one day, you want modify your site partially translated to site all translated, number of rows in site_content will grow up very fast and request to display content will make many much time.
The first reason for me to prefered multi tables is possiblities of evolutions for the website in all directions. With it, you can create easly partially translated site, localized subsites or split the principal site (on one server) to several independent site (each site with its server).
Off topic : several solutions exist to optimize sql => optimize database with tables (separate small fields and other, use of the smaller field possible, use adapted field type when is possible) and query (order of element in "where", use of small indexes or adapted indexes, use preferably numeric id). There are other optimisation possible but it’s too late for searching
I’m not sur to be comprehensive. Ask me precision if need.
Edit (2006-03-17 10 min later) : For optmize Modx to multi language content, it is necessary to modify database structure. And to optimize Modx it is also necessary
MODX Staff