使用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作为您插入到$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屋!