开发者

How to insert related entities to multiple tables with auto-increment primary keys from one form

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开发者_如何学Go 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?


If you are using PHP's mysql extension, mysql_insert_id() is what you want; if using PDO, then $dbh->lastInsertId().

You will need to insert the records in the order of their relationships, starting with the table that has no foreign keys. You will insert the parent records first, then its children, then their children, and so on. Each time, record the last inserted ID in a variable so you can refer to it when inserting its child records.

So, you will:

  1. Insert the QUESTION record.
  2. Record the last inserted ID.
  3. Insert the ANSWER records, using ID recorded in step 2 as question_id.
  4. And so on...


One way is to use the @@IDENTITY, which is set immediately after adding a record.

In PHP, you can use mysql_insert_id();

Alternatively, if you have an 'alternate key' for the table (a unique key other than the primary key), you could use that to get the id when inserting the new records. For instance, if your question text is unique, you could get the id by using text in the condition:

INSERT INTO answer (question_id, text, correct)
SELECT q.id, 'answer text', 0
FROM question WHERE text = 'question text'
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜