is it possible to use LAST_INSERT_ID () across queries

[emehrkay]

Example

 

INSERT INTO question (sti_id, question_type, creation_date, last_updated_date, question_text) VALUES (1, 1, NOW(), NOW(), 
');
INSERT INTO test_question (test_id, question_id, question_num) VALUES (4, LAST_INSERT_ID(), 1);
INSERT INTO question_option (question_id, question_option_id, creation_date, last_updated_date, question_option_text, question_option_correct) VALUES   
( 1, 1, NOW(), NOW(), '', 1),
( 1, 2, NOW(), NOW(), '', 0),	
( 1, 3, NOW(), NOW(), '',0),
( 1, 4, NOW(), NOW(), '', 0);

 

the question_id in my question_option insert, needs to be the last_insert_id() of the question insert. Is that at all possible using strictly mysql, no php?

[cmgmyr]

just take the last_insert_id() after the first insert and apply that to $id (or something) then just use $id for the other 2 inserts

[emehrkay]

This is being run in the command line, I cannot use php. thanks though

[cmgmyr]

oops, sorry, didn't catch that

[emehrkay]

this is what i did, let me know if there is a more efficient way to handle it

 

 

INSERT INTO question_option (question_id, question_option_id, creation_date, last_updated_date, question_option_text, question_option_correct) VALUES   
( (SELECT question_id FROM question ORDER BY question_id DESC LIMIT 1), 1, NOW(), NOW(), '', 1),
( (SELECT question_id FROM question ORDER BY question_id DESC LIMIT 1), 2, NOW(), NOW(), '', 0),	
( (SELECT question_id FROM question ORDER BY question_id DESC LIMIT 1), 3, NOW(), NOW(), '',0),
( (SELECT question_id FROM question ORDER BY question_id DESC LIMIT 1), 4, NOW(), NOW(), '', 0);

[fenway]

Find the question_id first, store in into a user variable, and then use it -- otherwise bad thigns will happen.

[bubblegum.anarchy]

use SET to store the insert id between queries.

 

SET @question_id = last_insert_id();

INSERT INTO question_option (question_id, question_option_id, creation_date, last_updated_date, question_option_text, question_option_correct) VALUES   
( @question_id, 1, NOW(), NOW(), '', 1),
( @question_id, 2, NOW(), NOW(), '', 0),	
( @question_id, 3, NOW(), NOW(), '',0),
( @question_id, 4, NOW(), NOW(), '', 0);

[emehrkay]

use SET to store the insert id between queries.

 

SET @question_id = last_insert_id();

INSERT INTO question_option (question_id, question_option_id, creation_date, last_updated_date, question_option_text, question_option_correct) VALUES   
( @question_id, 1, NOW(), NOW(), '', 1),
( @question_id, 2, NOW(), NOW(), '', 0),	
( @question_id, 3, NOW(), NOW(), '',0),
( @question_id, 4, NOW(), NOW(), '', 0);

 

thank you, i just revisited this and used it

[fenway]

FYI, this will also work, assuming there are no inserts in between:

 

NSERT INTO question_option (question_id, question_option_id, creation_date, last_updated_date, question_option_text, question_option_correct) VALUES   
( LAST_INSERT_ID(), 1, NOW(), NOW(), '', 1),
( LAST_INSERT_ID(), 2, NOW(), NOW(), '', 0),	
( LAST_INSERT_ID(), 3, NOW(), NOW(), '',0),
( LAST_INSERT_ID(), 4, NOW(), NOW(), '', 0);