SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Insert Query

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

    Insert Query

    I have two tables related to categories. One is called category_list which is mainly for administration purpose:
    Code MySQL:
    CREATE TABLE IF NOT EXISTS `category_list` (
      `category_id` tinyint(2) NOT NULL auto_increment,
      `category` varchar(32) default NULL,
      PRIMARY KEY  (`category_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    and a table categories, holding the different categories in 6 different languages:
    Code MySQL:
    CREATE TABLE IF NOT EXISTS `categories` (
      `cat_id` tinyint(2) NOT NULL auto_increment,
      `category_id` tinyint(2) NOT NULL,
      `language_abbr` char(3) NOT NULL,
      `category_name` varchar(32) NOT NULL,
      PRIMARY KEY  (`cat_id`),
      FOREIGN KEY (`category_id`) REFERENCES `category_list` (`category_id`) ON DELETE CASCADE,
      FOREIGN KEY (`language_abbr`) REFERENCES `languages` (`language_abbr`) ON DELETE CASCADE,
      KEY `category_name` (`category_name`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    So doing an insert goes in two steps. The first insert in to the table category_list and after that into the table categories, but I can't figure out how to get the six different values for the six text fields(languages) in the database since they have a different name in the form, where in the database there is just one field (category_name). Everything goes well with the category_id and language_abbr by using the id from the first insert and using the table languages to get the active languages:
    Code MySQL:
      INSERT INTO
            categories
            (
              category_id
            , language_abbr
     
            )
      SELECT
              #catID#
            , language_abbr
     
      FROM
              languages
      WHERE
            language_isActive IN ( 1 )
    Unfortunately a can't give those textfields the same name like with a check box group. Any ideas?
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    could you please explain why you have two tables? i would've thought only the second table was necessary

    also, what was your question again? the query looks okay to me, other than the fact that you're not populating the category_name column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy. I use the two different tables since the categories are part of the menu and because i'm working with different languages. This way when from the menu I redirect to the activities page the activity_id is always the same, no matter from which language I'm coming. The problem I have is populating the category_name. Since there is just one name in the database, but I have obviously six different form names (cat_eng, cat_gre, cat_rus, cat_dut, cat_fre, cat_swe)
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i still don't see why you need two different tables, but perhaps the difficulty lies entirely with me

    so you have trouble populating six different language names for the same category

    why? i would've thought that if you have a form with six different names on it, you would do six inserts in your application

    is this really a database question?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i would've thought that if you have a form with six different names on it, you would do six inserts in your application

    is this really a database question?
    Hi Rudy, I thought it could be done in just one go. The way I have it now, with the category_id and language_abbr there are indeed 6 rows inserted at the same time!

    Do you mean I have to do 6 six separate inserts?
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by donboe View Post
    Do you mean I have to do 6 six separate inserts?
    no, you can use the multi-row INSERT syntax as follows...
    Code:
    INSERT 
      INTO categories
         ( category_id
         , language_abbr
         , category_name )
    VALUES
         ( #catID# , 'eng' , 'english name' )
        ,( #catID# , 'gre' , 'greek  name' ) 
        ,( #catID# , 'rus' , 'russian name' )
        ,( #catID# , 'dut' , 'dutch name' )
        ,( #catID# , 'fre' , 'french name' )
        ,( #catID# , 'swe' , 'swedish name' )
    presumably the various language names would come from your text boxes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perfect Rudy, thanks a lot
    “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
  •