SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mySQL insert in two tables with in the second table 3 rows with null values

    For a multilingual website I am thinking of setting up the following table structure for a product-line:

    table products:
    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  2. #2
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I found the sollution:

    Code:
    		  INSERT INTO   products_content
    		              (
    					    products_id
    					  , language_abbr
    					  )
    		  SELECT  #SESSION.product#
    		               , language_abbr
              FROM           languages
              WHERE          isActive = 1
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •