I have the following tables for a php mysql based quiz application:
QUESTION
id
text
...
ANSWER
id
question_id
text
correct (true/false)
...
TAG
id
text
QUESTION_TAG
question_id
tag_id
I'm trying to create one form from which I can input a new question and it's answer choices, mark one choice as correct, give any number of tags to the question (just like the tag feature on stackoverflow), for existing tags just creating the linking entity, and for new tags creating the tag entity and the linking entity, then hit one submit button to insert all of it to the various tables.
I'm running into trouble because many of the tables are using an auto-increment primary key and I don't know how I can programmatically refer to those entities when I don't yet know what the id will be. Does that make sense? Maybe I am overlooking something obvious here.
For example, if I only wanted to insert a new question, I would just insert the QUESTION.text, and the QUESTION.id would then be auto assigned. If I want to insert the answers for that question together with it, how do I assign ANSWER.question_id before QUESTION.id is known?
I thought of different things like counting how many entities exist in QUESTION, and then giving the next ANSWER.question_id the highest value + 1, or for the ANSWER.question_id running a select from QUESTION.text for the text that was just entered, but those ideas don't seem very solid. What's the best way to do this?