Multiple insert queries

Question

I have three tables that essentially cascade down, like:

• topic
• section (references topic id)
• subsection (references topic id and section id)

Whats the best method of writing mysql statements to create the initial topic/section/subsection so I can grab the id's (auto_incremented) of the newly created rows and use them to insert them into the second two?

edit I'm using phpbb3, dunno if that makes a huge difference, but I normally use the $db-sql_query() function

Answer 1

From the parent, down the line.
Then you can use either LAST_INSERT_ID(), or INSERT in the SELECT:

INSERT INTO TOPIC
  (topic_id, topic)
VALUES (DEFAULT, $topic);

INSERT INTO SECTION
  (topic_id, section)
SELECT topic_id, $section
  FROM TOPIC
 WHERE topic = $topic

INSERT INTO SUBSECTION
  (section_id, topic_id, subsection)
SELECT section_id, topic_id
  FROM SECTION 
 WHERE section = $section

This example assumes that TOPIC.topic_id, SECTION.section_id, and SUBSECTION are auto_increment, primary key columns.