For a multilingual website for a restaurant I need some kind of DISTINCT output if you can call it that and for the dishes I use the following two tables:
CREATE TABLE IF NOT EXISTS `menu_kaart` (
`menu_kaart_id` tinyint(2) NOT NULL AUTO_INCREMENT,
`menu_kaart_type` varchar(32) NOT NULL,
PRIMARY KEY (`menu_kaart_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `menu_kaart_items` (
`menu_kaart_item_id` tinyint(2) NOT NULL AUTO_INCREMENT,
`menu_kaart_id` tinyint(4) NOT NULL,
`language_abbr` char(2) NOT NULL,
`menu_item` varchar(32) DEFAULT NULL,
`menu_item_description` text,
`menu_item_price` varchar(40) DEFAULT NULL,
PRIMARY KEY (`menu_kaart_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
There are 4 languages so the rows in menu_kaart_items(the second table) are like this:
(1, 1, ‘nl’, ‘Gestoomde Jacobsschelp’, 'Gestoomde sint jacobsschelp met knoflook ', ‘5.00’),
(2, 1, ‘en’, ‘Steamed Scallop’, 'Steamed scallop with garlic ', ‘5.00’),
(3, 1, ‘de’, ‘Gedämpfte Jakobsmuschel’, ‘Gedämpfte Jakobsmuscheln mit Knoblauch’, ‘5.00’),
(4, 1, ‘fr’, ‘Pétoncles cuits à la vapeur’, ‘Pétoncles cuits à la vapeur avec de l’ail’, ‘5.00’),
In the CMS in need to output the menu_item from just one language but the menu_item_descriptions from all 4! e.a.
Gestoomde Jacobsschelp
- Gestoomde sint jacobsschelp met knoflook
- Steamed scallop with garlic
- Gedämpfte Jakobsmuscheln mit Knoblauch
- Pétoncles cuits à la vapeur avec de l’ai
Can this be done in a query or should this be done in my server side scripting?
Thank you in advance!