doufuhao0566 2016-02-24 19:57 采纳率: 100%
浏览 75
已采纳

使用PDO插入的另一个表中的ID

I am creating a quiz based system which part of it includes 2 tables:

Answer_bank table:

+-------+---------+----------+
| ab_id | ab_name | ab_qb_id |
+-------+---------+----------+

and a Question_bank table:

+-------+-------------+
| qb_id | qb_question | 
+-------+-------------+

The aim is to allow someone to create a question and an answer, the answer will be stored within the answer bank table with the ab_qb_id equal to the qb_id. I don't want this in the same table as I will be making this more complex.

I try to use the following PDO/SQL to insert into both the tables.

//questions
$qb_id = $_POST['qb_id'];
$qb_question = $_POST['qb_question'];
$sql = "INSERT INTO questions_bank (`qb_id`, `qb_question`)
        VALUES (:qb_id, :qtn)";
$stmt = $db->prepare($sql);
$stmt->bindValue(":qb_id", $qb_id);
$stmt->bindValue(":qtn", $qb_question);
$stmt->execute();

//answers
$ab_name = $_POST['ab_name'];
$sql = "INSERT INTO answers_bank (`ab_name`, `ab_qb_id`) VALUES (:ab_name, :qb_id)";
$stmt = $db->prepare($sql);
$stmt->bindValue(':ab_name', $ab_name);
$stmt->bindValue(':qb_id', $qb_id);
$stmt->execute();

However the problem I have is the ab_qb_id in the answer_bank table always inserts 0 and not the same id as qb_id. Is this the incorrect way to do this? What's the best way for the answer table to include the qb_id ?... So that then the answer is related to a specific question. Thank you

  • 写回答

1条回答 默认 最新

  • doutan2228 2016-02-24 20:09
    关注

    I would personally get the ID of the row that was just inserted in the questions_bank table and then use that ID as the value you insert into the answers_bank.ab_qb_id column. Because you're using PDO, you can use this: $db->lastInsertId(). For example:

    $qb_id = $_POST['qb_id'];
    $qb_question = $_POST['qb_question'];
    $sql = "INSERT INTO questions_bank (`qb_id`, `qb_question`)
        VALUES (:qb_id, :qtn)";
    $stmt = $db->prepare($sql);
    $stmt->bindValue(":qb_id", $qb_id);
    $stmt->bindValue(":qtn", $qb_question);
    $stmt->execute();
    $inserted_id = $db->lastInsertId();
    
    //answers
    $ab_name = $_POST['ab_name'];
    $sql = "INSERT INTO answers_bank (`ab_name`, `ab_qb_id`) VALUES (:ab_name, :qb_id)";
    $stmt = $db->prepare($sql);
    $stmt->bindValue(':ab_name', $ab_name);
    $stmt->bindValue(':qb_id', $inserted_id); //Use the previously inserted ID
    $stmt->execute();
    

    Using this method will ensure that the questions_bank.qb_id and answers_bank.ab_qb_id are the same. To make sure that any interruptions (power surge, disk failure, etc.) don't have a chance to affect this, you can wrap these in a transaction. Then you can be sure that the values will always match.

    EDIT I forgot to add the try{}catch{} statement to the transaction:

    For example:

    try{
        $db->beginTransaction();
        //Your current queries
        $db->commit();
    }catch(Exception $e){
        $db->rollback();
        die($e->getMessage());
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 请分析一下这个电路设计的优点🙏
  • ¥15 求视频摘要youtube和ovp数据集
  • ¥15 怎么改成输入一个要删除的数后现实剩余的数再输入一个删除的数再现实剩余的数用yes表示继续no结束程序
  • ¥15 在启动roslaunch时出现如下问题
  • ¥15 汇编语言实现加减法计算器的功能
  • ¥20 关于多单片机模块化的一些问题
  • ¥30 seata使用出现报错,其他服务找不到seata
  • ¥35 引用csv数据文件(4列1800行),通过高斯-赛德尔法拟合曲线,在选取(每五十点取1点)数据,求该数据点的曲率中心。
  • ¥20 程序只发送0X01,串口助手显示不正确,配置看了没有问题115200-8-1-no,如何解决?
  • ¥15 Google speech command 数据集获取