Multi linqual site scripting

[stevebrett]

Im a bit over my head designing a site for a client that needs to be in 4 languages. English, French, Italian, Chinese

Much of the content in the site will be stored in a database and will consist of searches, links, articles and so on. This part is fine. Where I get lost is with the clients request that if any particular bit of content(lets say a search) hasnt been translated in the selected language, then the English version gets shown .

I thought I could do this with the table structure below with some kind of deeply nested series php select statement

like select all the searches , loop through and select each search for say the french version and then if that's not available select the english version,

Then select next item and repeat the process.

So far I haven't been able to craft a php script that works. So my questions are these

Do I have a table structure that will allow me to achieve what Im after.

Is this nested series of php selects the right approach

Is there a single mysql select statement that would perform the whole shebang in on step rather then struggle with the complex nesting.

Sorry for the long and complex post and thanks in advance for any thoughts anyone might have.

Steve

-- Table structure for table `languages`
--

CREATE TABLE `languages` (
`localization` varchar(25) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`localization`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `searches`
--

CREATE TABLE `searches` (
`name` varchar(100) collate latin1_general_ci NOT NULL,
`search_url` varchar(300) collate latin1_general_ci default NULL,
PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `search_translations`
--

CREATE TABLE `search_translations` (
`name` varchar(100) collate latin1_general_ci NOT NULL,
`localization` varchar(50) collate latin1_general_ci NOT NULL,
`translation` varchar(100) collate latin1_general_ci default NULL,
PRIMARY KEY (`name`,`localization`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

[stevebrett]

Well as is often the case, the solution to the problem came to me after I posted, so im all set.

Thanks

[eelixduppy]

Hello and Welcome to WebmasterWorld! :)

Would you mind sharing your solution/thought process so that others may find it useful, as well?