For a multilingual website I am thinking of setting up the following table structure for a product-line:
table products:
CREATE TABLE IF NOT EXISTS `products` (
`product_id` tinyint(2) NOT NULL AUTO_INCREMENT,
`product_name` varchar(32) DEFAULT NULL,
`product_class` varchar(20) DEFAULT NULL,
PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
table languages:
CREATE TABLE IF NOT EXISTS `languages` (
`language_abbr` char(2) NOT NULL,
`language_full` varchar(12) DEFAULT NULL,
`isActive` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`language_abbr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `languages` (`language_abbr`, `language_full`, `isActive`) VALUES
('de', 'german', 0),
('el', 'greek', 1),
('en', 'english', 1),
('es', 'spanish', 0),
('fr', 'french', 0),
('it', 'italian', 0),
('nl', 'dutch', 0),
('no', 'norwegian', 0),
('ru', 'russian', 1),
('se', 'swedish', 0);
table products_content:
CREATE TABLE IF NOT EXISTS `products_content` (
`content_id` smallint(2) NOT NULL AUTO_INCREMENT,
`products_id` tinyint(2) NOT NULL,
`language_abbr` char(2) NOT NULL,
`product_name` varchar(32) DEFAULT NULL,
`product_content` text,
PRIMARY KEY (`content_id`),
FOREIGN KEY (`products_id`) REFERENCES `products` (`product_id`) ON DELETE CASCADE,
FOREIGN KEY (`language_abbr`) REFERENCES `languages` (`language_abbr`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The first table(products) is holding the main details for a product. The second table(languages) is holding the languages for the website, where languages with the value 1 are active. The third table(products_details) is holding more detailed information.
What I would like to accomplish is the following: When a new product is added that information is going obviously in the table products, but after that multiple rows should be added into the table products_content. I say multiple because the number of rows is depending on the languages that are active. With the table structure above it should be something like:
Insert new product:
INSERT INTO `products` (`product_name`, `product_class`) VALUES
('product_name', 'product_class');
After that I check what the last inserted id is from the product added and after that the following insert should happen based on the active languages:
Insert into products_content:
INSERT INTO `products_content` (`product_id`, `language_abbr`) VALUES
(last_inserted id, 'el'),
(last_inserted id, 'en'),
(last_inserted id, 'ru'),
I know how to determine the last inserted id but how do loop over the active languages and get an insert as the last one?
I hope this is clear. Thank you in advance