0

The 2 table structures like the following(generated by sequel pro):

The question table:

CREATE TABLE `question` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL DEFAULT '',
  `content` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The answer table:

CREATE TABLE `answer` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `question_id` int(11) unsigned NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `question_id` (`question_id`),
  CONSTRAINT `answer_ibfk_1` FOREIGN KEY (`question_id`) REFERENCES `question` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now when I want to insert an answer:

Can I do something like:

INSERT INTO answer(content) JOIN question(title,content) VALUE('Ironman',"favourite characters","Who is your favourite characters in Avanger?");

or Is there a better way to do the similar thing?

mko
  • 21,334
  • 49
  • 130
  • 191
  • I think this is what you're after: http://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization – gllen Oct 07 '12 at 00:36

1 Answers1

0

The best way to do this is by in some way persisting the question id and using it to insert the answers. If there is no other way, you could do something like this:

INSERT INTO answer(content, question_id) 
VALUES('Ironman', (select id 
                     from question 
                    where title ='favourite characters' 
                      and content = 'Who is your favourite characters in Avanger?'));
Mateus Schneiders
  • 4,853
  • 3
  • 20
  • 40
  • Thanks man, The select statement can do this kind thing, which is amazing, BTW could you provide some resource for this kind of advanced sql command? – mko Oct 07 '12 at 04:10
  • please also check my last question that related to this question, but use CodeIgniter activerecord ORM to archive the same thing http://stackoverflow.com/q/12759536/456218 – mko Oct 07 '12 at 04:21