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

图片转代码服务由CSDN问答提供 功能建议

我正在创建一个基于测验的系统,其中一部分包括2个表: < p> Answer_bank表:

  + ------- + --------- + ---------- + \  N |  ab_id |  ab_name |  ab_qb_id | 
 + ------- + --------- + ---------- + 
   
 
 

和一个Question_bank表:

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

目的是让某人创建一个 问题和答案,答案将存储在答案库表中, ab_qb_id 等于 qb_id 。 我不希望这个在同一个表中,因为我将使它更复杂。

我尝试使用以下PDO / SQL插入到两个表中。

  // 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-&gt; prepare($ sql); 
 $ stmt-&gt; bindValue(”:qb_id“,$  qb_id); 
 $ stmt-&gt; bindValue(“:qtn”,$ qb_question); 
 $ stmt-&gt; execute(); 
 
 // answers = n $ ab_name = $ _POST ['ab_name'  ]; 
 $ sql =“INSERT INTO answers_bank(`ab_name`,`ab_qb_id`)VALUES(:ab_name,:qb_id)”; 
 $ stmt = $ db-&gt; prepare($ sql); 
 $ stmt  - &gt; bindValue(':ab_name',$ ab_name); 
 $ stmt-&gt; bindValue(':qb_id',$ qb_id); 
 $ stmt-&gt; execute(); 
  <  / pre> 
 
 

但是我遇到的问题是 answer_bank 表中的 ab_qb_id 总是插入 0 而不一样 id为 qb_id 。 这是不正确的方法吗? 答案表包含 qb_id 的最佳方法是什么?...那么答案就与特定问题有关。 谢谢

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

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());
    }
    
    已采纳该答案
    打赏 评论

相关推荐 更多相似问题