使用PDO插入时来自另一个表的ID [英] ID from another table on insertion using PDO

问题描述

我正在创建一个基于测验的系统，该系统的一部分包括2个表:

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

Answer_bank表:

Answer_bank table:

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

和一个Question_bank表:

and a Question_bank table:

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

目的是允许某人创建一个问题和一个答案，答案将以ab_qb_id等于qb_id的形式存储在答案库表中.我不想在同一张表中使用它，因为我会让它变得更复杂.

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.

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

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();

但是我遇到的问题是answer_bank表中的ab_qb_id总是插入0，并且与qb_id的ID不同.这是这样做的不正确方法吗?答案表包含qb_id的最佳方法是什么?...这样答案就可以与特定问题相关.谢谢

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

推荐答案

我个人将获得刚刚插入到questions_bank表中的行的ID，然后使用该ID作为您插入到列.因为您正在使用PDO，所以可以使用以下命令:$db->lastInsertId().例如:

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();

使用此方法将确保questions_bank.qb_id和answers_bank.ab_qb_id相同.为确保任何中断(电涌，磁盘故障等)都没有机会影响这一点，可以将它们包装在事务中.然后，您可以确保这些值将始终匹配.

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.

编辑，我忘了在交易中添加try{}catch{}语句:

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

例如:

try{
    $db->beginTransaction();
    //Your current queries
    $db->commit();
}catch(Exception $e){
    $db->rollback();
    die($e->getMessage());
}

这篇关于使用PDO插入时来自另一个表的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！