Language database

oMIKEo · 04-22-2009, 09:39 PM

Hi guys,

I've got a table of fields for translations but am looking for an effective way to display them all in a form and update them.

The table looks like:

LANGUAGES
id
language_name
translation1
translation2
translation3
etc

How would i output the all these with a title and an input textbox without having to do this:

PHP Code:


			
$thome = $row["thome"];
$tevents = $row["tevents"];
$tvenues = $row["tvenues"];

echo $thome.'<input type="text" name="thome" id="thome" />'.
$tevents.'<input type="text" name="tevents" id="tevents" />'.
$tvenues.'<input type="text" name="tvenues" id="tvenues" />';

That is going to be a huge list, plus that doesn't even include an input value which means even more work and there would be a huge list to save the inputed/edited text. Any ideas how to approach this would be great.

Kalle · 04-23-2009, 01:08 AM

I would suggest abit different database structure.

Make a language table that contains all the languages available:

sql Code:

CREATE TABLE `languages`
(
    `id` INT NOT NULL AUTO_INCREMENT, 
    `title` VARCHAR(48) NOT NULL, 
    `default` TINYINT(1) DEFAULT '0', 
    PRIMARY ID(`id`)
);

And then a phrase table:

sql Code:

CREATE TABLE `phrase`
(
    `id` INT NOT NULL AUTO_INCREMENT, 
    `title` VARCHAR(128) NOT NULL, 
    `phrase` TEXT NOT NULL, 
    `phrasegroup` VARCHAR(24) NOT NULL, 
    `language` INT NOT NULL, 
    PRIMARY KEY(`id`)
);

So the design is like, you have a language table with the languages and a phrase tables with all the translations. You fill in a language like:

sql Code:

INSERT INTO `languages` (`title`, `default`) VALUES ('English', 1);

And you insert phrases like this:

sql Code:

INSERT INTO `phrase` (`title`, `phrase`, `phrasegroup`, `language`) VALUES ('greeting', 'Hello %s', 'default', 1);

The order works as:

title: The title of the phrase
phrase: The phrase itself, you may want to use sprintf-alike conversions here
phrasegroup: This is mainly for performance, you set a phrasegroup and the script may want to get a phrasegroup for "these type of functions do this", so you can seperate them
language: The language id from the language table

So in your bootstraper script you would do:

php Code:

/* If this script is included from a separate file, you may wish to define $phrasegroups with an array of phrasegroups you want to precache */
if(!isset($phrasegroups))
{
    $phrasegroups = Array('default');
}
else
{
    array_push($phrasegroups, 'default');
}

/* Assumes you have the default language id, either for this user or from your options table assigned as $language_id */

/* Assumes a database connection is made, correct the calls so they fit your API */
$p = $db->query('SELECT `title`, `phrase` FROM `phrases` WHERE `phrasegroups` IN (\'' . implode('\', \'', $phrasegroups) . '\') AND `language` = ' . (integer) $language_id);

if(!$p->getNumRows())
{
    /* throw a fatal error here, no phrases were "downloaded" */
    exit;
}

/* Make a simple class, that contains the phrases in form of $language->phrasegroup['phrase'] = 'value'; */

$language = new stdClass;
$language->phrases = Array();

foreach($p as $r)
{
    if(!isset($language->{$p})
    {
        $language->{$p} = Array();
    }

    $language->{$p}[$r['title']] = $r['phrase'];
    $language->phrases[$r['phrase']] =& $language->{$p}[$r['title']];
}

unset($p, $r);

And then we need a utility function for formatting phrases in a sprintf-alike format:

php Code:

function phrase($title)
{
    global $language;

    if(!isset($language->phrases[$title]))
    {
        return('');
    }
    elseif(func_num_args() == 1)
    {
        /* No formatting */
        return($language->phrases[$title]);
    }

    $args = func_get_args();
    $args[0] = $language->phrases[$title];

    return(call_user_func_array('sprintf', $args));
}

Now as you fill in phrases you should be able to simply do:

php Code:

echo phrase('greeting', 'Kalle');

That would print:

Code:

Hello Kalle

(That is, if you added that 'greeting' phrase for english above), or if your language was danish and the phrase looked like:

sql Code:

INSERT INTO `languages` (`title`, `default`) VALUES ('Dansk', 0);

sql Code:

INSERT INTO `phrase` (`title`, `phrase`, `phrasegroup`, `language`) VALUES ('greeting', 'Hej %s', 'default', 2);

it would print:

Code:

Hej Kalle

Hope its not too complex, I know I rushed abit here since its late ;)

oMIKEo · 04-23-2009, 09:12 AM

Hi Kalle, thank you for such a detailed answer. It's a little complex but i understand the approach you are going for and that should make it easier to manage! Thanks!

Kalle · 04-23-2009, 09:33 AM

No problem, feel free to questioning more if needed ;)