所以我想我已经接近解决这个问题了,但我的查询不会将"待处理"表中的项目添加到"项目"表中。 你们能帮我解决这个问题吗?另外,如果我希望它在添加后删除它,我应该在插入到选择查询下方添加代码吗?谢谢
操作.php:
$sql = "INSERT INTO items (photo,title,description, name) SELECT (photo,title,description, name) FROM pending";
$stmt = $conn->prepare($sql);
$stmt->execute();
将项目从"挂起"获取到项目后删除查询的示例:
$idToDelete = filter_var($_POST["recordToDelete"],FILTER_SANITIZE_NUMBER_INT);
//try deleting record using the record ID we received from POST
$sql = "DELETE FROM pending WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':id', $idToDelete, PDO::PARAM_INT);
$stmt->execute();
我认为你这样做会让自己容易犯错误。
考虑如果在发出INSERT SELECT
之后但在开始删除之前将新行添加到挂起队列中,会发生什么情况。
我认为您需要在单个循环中以更可控的方式执行此操作,以确保您只删除从pending
复制到items
的内容。
$sql = "SELECT photo,title,description, name FROM pending";
$select_pending = $conn->prepare($sql);
$select_pending->execute();
$sql = "INSERT INTO items (photo,title,description, name)
VALUES (:photo,:title,:description, :name)";
$insert_items = $conn->prepare($sql);
$sql = "DELETE FROM pending WHERE id = :id";
$delete_pending = $conn->prepare($sql);
// only if you are using INNODB databases.
//$conn->beginTransaction();
while( $row = $select_pending->fetch_object() ) {
$insert_items->bindParam(':photo', $row->photo, PDO::PARAM_STR);
$insert_items->bindParam(':title', $row->title, PDO::PARAM_STR);
$insert_items->bindParam(':description', $row->description, PDO::PARAM_STR);
$insert_items->bindParam(':name', $row->name, PDO::PARAM_STR);
$insert_items->execute();
$delete_pending->bind_param(':id', $row->id, PDO::PARAM_INT);
$delete_pending->execute();
}
// only if you are using INNODB databases.
//$conn->commit();
$sql = "INSERT INTO items (photo,title,description, name)
SELECT photo,title,description, name FROM pending";
删除 SELECT
语句中的()
。