PHP better practice to build a multi language site

Question

i have a website that i built in two languages including two different "dictionaries" based on browser language

in each langXX.php i have

define('SOME_STRING', 'Some string');

etc... but i hate now this solution because i'm never sure if i included a new definition in both files...so i'm trying to move including all the definition in mysql and build a function like

translate('SOME_STRING', 'en'); // ouput 'Some string'

i'm moving to mysql because id like to manage the translation directly via a CMS i will build, i dont want to have the webmaster going trough ftp and textmate...

so my question is, if this latter solution i sbetter or is too stressful for mysql to get a query for each text element of the page..

PS. as far as i remember even osCommerce used this practice right?

Answer 1

Why not use file caching of the data stored in the database? You remove a potentially costly, heavily repeated set of database calls, but can retain the advantages of CMS overview of language values.

• Store translations in the database
• Have a process for generating a flat file from the database values
• Re-fire this process every time a translation update occurs
• Use the flat file in any front-end translation retrieval operations.

Answer 2

A simple method used to translate text elements of your website could be broken down into this simple three-table structure:

Language
--------
Id
Name    # English, French, etc
Code    # en-gb, fr, etc


# Text table stores content in the default language.

Text
----
Id
Content


# Translations relate to a language and an existing Text

Translation
-----------
FkText
FkLanguage
Content

This way, a simple query can obtain the requested content in any language, automatically falling back to default language if there is no translation.