Page 1 of 1

thinking about possible approaches

Posted: Mon Aug 01, 2005 2:36 pm
by newmember
Hi

I have a simple database of images which later displayed as gallery. (I assume that db supports unicode)
Records hold information such as image description, keywords, and etc...

The site, where this gallery is integrated, comes in few language versions: english, russian, hebrew, arabic, german.

I want to be able to display image information according to site language version.
So for example if site is switched to russian, then all information about images displayed in russian, and russian keywords are used when user does search.

What i'm thinking is of ways to achieve this...
For example, I could keep one table that keeps all langauge-indepandant information + several separate tables each for different language.

Are there any other approaches..?

Posted: Mon Aug 01, 2005 9:34 pm
by dreamline
Yes there are, you could use PHP for that too with using several language files and defining keywords that are used in the site. For example:
1 file contains all defines in English and another one all German
languages. It would go something like this:
File 1:
DEFINE("LANG","English")
DEFINE("SAV","Save")

File2
DEFINE("LANG","German")
DEFINE("SAV","Speichern")

Anyway that's what i did with one of my sites, however for a good overview i put all the languages in a database with 3 fields, 1 defining the first word (like LANG) then another field to hold the English Translation and the 3rd field to hold the German language.

And by doing a little programming I create those define files. I rather load them instead of doing a sql query on the database everypage, just for the language. :)

So on the main page you check wich language is selected and load the appropriate file..

Hope this helps.. :)

Posted: Tue Aug 02, 2005 4:54 am
by newmember
ok thanks...
but how do i dela with other data?

for example, if my image record has field named 'description', then i want to keep the description in different languages and show it appropriate to language version of the page...

With your approach i could simply have 3 fields, each for different language...right?

Posted: Tue Aug 02, 2005 6:55 am
by feyd
go for a normalized approach: a simple table structure that includes a language table link. When selecting records, restrict the query to find the language most wanted, plus fall back ones. The language table could have an order of preference field which will allow you to sort any request by that joined in information.

Posted: Tue Aug 02, 2005 2:58 pm
by newmember
feyd, would you please give some simple example/s that illustrates what you say?
i don't quite understand your idea :?

Posted: Tue Aug 02, 2005 6:07 pm
by feyd
quick and dirty example:

image data table
recordID
keywords
description
user_recordID (author, for image or just this language version)
language_recordID
incept
copyright
whatever..

language table
recordID
name

language preference table
recordID
user_recordID
language_recordID
order


Say for instance Hebrew has a recordID of 10, English 11 and German 12. Also assume I understand only English and German, preferring German first and that my user ID is 200. So there are 2 records in the preference table of:

Code: Select all

20, 200, 11, 0
20, 200, 12, 1
Performing a selection of images in all possible languages I want to read would be like (untested):

Code: Select all

SELECT
  *
FROM
  images
INNER JOIN
  langPref
  ON(
    langPref.language_recordID = images.language_recordID
  )
WHERE
  langPref.user_recordID = 200
ORDER BY
  images.images_incept DESC,
  langPref.langPref_order DESC
you'll have to play around with it to suit your site needs, but this should give you an idea...

Posted: Wed Aug 03, 2005 9:52 am
by newmember
thanks feyd
i like your idea.. :D