dping1968 2011-08-19 08:03
浏览 51
已采纳

如何使用一个表单中的自动增量主键将相关实体插入到多个表中

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?

  • 写回答

2条回答 默认 最新

  • douzhaochan6468 2011-08-19 08:11
    关注

    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...
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常