Insert Query

Databases
1

I have two tables related to categories. One is called category_list which is mainly for administration purpose:


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:


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:


  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?

2

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

3

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)

4

i still don’t see why you need two different tables, but perhaps the difficulty lies entirely with me :slight_smile:

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?

5

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?

6

no, you can use the multi-row INSERT syntax as follows…

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

7

Perfect Rudy, thanks a lot :tup: