GS Design Just another developer blog

As i wrote in a previous post there are more ways to design a database for a multilanguage website, but the one i decided to go with is Coupled Translation table approach, where basicly for each table you have another one where you store a row for each language wich has the primary table id, language id and the other columns that need to be translated.

The Requirements and Implementation

Having this idea in mind i wrote a new class that extends Zend_Db_Table to provide an easy access to the translated tables. Basically the class a couple of things:

1. Getting the right translated info
It overwrites the default select method so you can basicly use Zend_Db_Table as normal to fetch rows, but you will actualy get a join of 2 tables as a result (primary table and translate table joined on the default language).

2. Getting form data
Of course you will need a method to add this data into your database … and you cant do it by hand.
There is a method called formInfo wich takes a parameter an ID that will return a model with language columns.
To be more explicit i will give an example … if you have as translated columns the columns name and description and as languages en and es you will get this new columns:
name_en
name_es
description_en
description_es

3. Saving form data
After populating the form with formInfo you will need to save your model. So i overwrote the methods insert and update to allow separation of columns from the main table and the language table (the ones i explained above)

4. Deleting Data
I also overwrote the delete method to allow the deletion of the language table rows as well.

The actual Class

Below you have the actual class file … you will have to excuse me because it isnt commented.
The class is still in testing in a project i am making at the moment, and hopefully when its fully tested i will comment it.
Or maybe someone will show me i reinvented the wheel as on a previous post. )
[php]

abstract class GSD_Db_TableLang extends Zend_Db_Table
{

public function formInfo($id){
$select = new Zend_Db_Table_Select($this);
$row = $this->fetchRow($select->where(‘id = ?’, $id));
$rowArray = $row->toArray();

if ($row) {
$rowArray = $this->getLangArray($rowArray);
}

return $this->buildLangRow($rowArray);
}

function getLangArray($rowArray){
$langRows = $this->_db->fetchAll(
$this->_db->select()
->from($this->getLangTable(), $this->lang_fields)
->join(‘languages’,
$this->getLangTable() . ‘.language_id = languages.id’, ‘code’)
->where($this->_sName . ‘_id = ?’, $rowArray['id']));

foreach ($langRows as $langRow) {
foreach ($this->lang_fields as $field) {
$colName = $field . ‘_’ . $langRow['code'];
$rowArray[$colName] = $langRow[$field];
}
}
return $rowArray;
}

function buildLangRow($array) {
$rowClass = $this->getRowClass();
return new $rowClass(array(‘table’ => $this, ‘data’ => $array, ‘stored’ => true));
}

public function getInfo($id){
$row = $this->fetchRow($this->select()->where(‘id = ?’, $id));
return $row;
}

public function select()
{
$select = new Zend_Db_Table_Select($this);
$select->setIntegrityCheck(false)
->from($this->_name)
->join($this->getLangTable(),
“{$this->_name}.id = {$this->getLangTable()}.{$this->_sName}_id”,
$this->lang_fields)
->where($this->getLangTable() . ‘.language_id = ?’, Zend_Registry::get(‘language’)->id);
return $select;
}

public function getLangTable()
{
return $this->_name . ‘_lang’;
}

public function countRecords($where = false)
{
$select = new Zend_Db_Table_Select($this);
$select->setIntegrityCheck(false)
->from($this->_name,’COUNT(*) AS num’);

if($where != false) {
$select->where($where);
}

$row = $this->fetchRow($select);
return $row->num;
}

public function delete($where)
{
$tableSpec = ($this->_schema ? $this->_schema . ‘.’ : ”) . $this->_name;

$rows = $this->_db->fetchAll($this->_db->select()
->from($this->_name, array(‘id’))
->where(implode(‘ && ‘, $where)));
$this->_db->delete($tableSpec, $where);

$tableSpec = ($this->_schema ? $this->_schema . ‘.’ : ”) . $this->getLangTable();
foreach ($rows as $row) {
$newWhere[] = $this->_db->quoteInto(“{$this->getLangTable()}.{$this->_sName}_id = ?”, $row['id'], ‘INT(11)’);
}
$this->_db->delete($tableSpec, $newWhere);

return;
}

/**
* Inserts a new row.
*
* @param array $data Column-value pairs.
* @return mixed The primary key of the row inserted.
*/
public function insert(array $insertData)
{
list($data, $langData) = $this->separateLangRows($insertData);

if (is_array($data) && count($data)>0) {
$tableSpec = ($this->_schema ? $this->_schema . ‘.’ : ”) . $this->_name;
$this->_db->insert($tableSpec, $data);
$insertData['id'] = $this->_db->lastInsertId();
}

if (is_array($langData) && count($langData)>0) {
$tableSpec = ($this->_schema ? $this->_schema . ‘.’ : ”) . $this->getLangTable();

foreach ($langData as $language_id=>$data) {
$data[$this->_sName . '_id'] = $insertData['id'];
$data['language_id'] = $language_id;
$this->_db->insert($tableSpec, $data);
}
}

return $this->buildLangRow($insertData);
}

/**
* Updates existing rows.
*
* @param array $data Column-value pairs.
* @param array|string $where An SQL WHERE clause, or an array of SQL WHERE clauses.
* @return int The number of rows updated.
*/
public function update(array $data, $where)
{
list($data, $langData) = $this->separateLangRows($data);

if (is_array($data) && count($data)>0) {
$tableSpec = ($this->_schema ? $this->_schema . ‘.’ : ”) . $this->_name;
$result = $this->_db->update($tableSpec, $data, $where);
}

if (is_array($langData) && count($langData)>0) {
$tableSpec = ($this->_schema ? $this->_schema . ‘.’ : ”) . $this->getLangTable();
$db = $this->getAdapter();

$where[0] = str_replace($this->_name, $this->_sName, $where[0]);
$where[0] = str_replace(‘`.`’, ‘_’, $where[0]);

foreach ($langData as $language_id=>$data) {
$where[1] = $db->quoteInto(“{$tableSpec}.language_id = ?”, $language_id, ‘int(11)’);
$this->_db->update($tableSpec, $data, $where);
}
}

return;
}

private function separateLangRows( array $data) {
$languages = Languages::instance()->getList();
$langData = array();

foreach ($this->lang_fields as $field) {
foreach ($languages as $language) {
$colName = $field . ‘_’ . $language->code;
if (isset($data[$colName])) {
$langData[$language->id][$field] = $data[$colName];
unset($data[$colName]);
}
}
}

return array($data, $langData);
}
}
[/php]

How to use it

To use this class you first setup your model to exted my class.
[php]
class Map_Landmarks_Table extends GSD_Db_TableLang
{
protected $_name = ‘map_landmarks’;
protected $_sName = ‘map_landmark’;

protected $lang_fields = array(‘name’, ‘description’);
[/php]

U should notice that in order for this to work you need to setup 2 extra variable:
1. $_sName this is used to generate the key from the language table. in this case it
would be map_landmark_id.
2. $lang_fields this tells the class witch are the columns in the translated table.

Requirements

First of all you should have a database structure similar to the one i describe here at 4. Coupled Translation table approach

Also you will need to have stored in Zend_Registry the language model.
if you are interested in a method of selecting the language via URL you might be interested in this implementation
Be carefull the registry needs to contain the language mode since i am using the code below to do the join.
[php]
Zend_Registry::get(‘language’)->id
[/php]

Then you will also need to have a Languages Model:
[php]

class Languages extends Zend_Db_Table
{
protected $_name = ‘languages’;
protected $_list = ‘languages’;

public function init()
{
$this->_list = $this->fetchAll();
}

public function getList()
{
return $this->_list;
}

public function findByCode($code)
{
$lang = $this->fetchRow($this->select()->where(‘code = ?’, $code));
return $lang;
}

public function listArray($key = ‘id’)
{
$languages = $this->getList();
$return = array();
foreach ($languages as $language) {
$return[$language->$key] = $language->name;
}
return $return;
}

static public function instance()
{
static $instance;
if (!($instance instanceof Languages)) {
$instance = new Languages();
$instance->init();
}
return $instance;
}
}
[/php]

Hope u are able to find some logic into this extension of Zend_Db_Table ( it took me a while to think it so complicated ) ) and await your thought on it.
Cheers.