php - PDO 插入不工作

Question

我正在尝试从我的数据库中的一个表中取出一条记录并将其放入另一个表中(作为数据的备份 - 需要保留 7 年，包括已删除的数据)。选择有效，删除有效，但插入无效。怎么了？我该怎么做才能修复它？

代码:

////////// DELETE STUDENT RECORD /////////
///// step 1 - copy all data to exStudentInfo with removal date //////

$studentID = $_REQUEST['sID'];
// pull student data from the database //
$query = "SELECT * FROM studentinfo WHERE studentID=" . $studentID;
$statement = $db->prepare($query);
$statement ->execute();
while($row = $statement->fetchObject())
    {
        $studentFirst = $row->studentFirst;
        $studentLast = $row->studentLast;
        $studentDOB = $row->studentDOB;
        $studentGrade = $row->studentGrade;
        $studentClass = $row->studentClass;
        $studentAllergy = $row->studentAllergy;
        $studentFam = $row->studentFam;
        $removeDate = date("Y-m-d");
    }

已解决:问题出在 $query 中...我使用了普通的撇号 (') 而不是正确的反引号 (`) - 我忘记在绑定(bind)值中包含照片字段。

$query = "INSERT INTO 'sundayschool'.'exstudentinfo' ('studentID' ,'studentFirst' ,'studentLast' ,'famID' ,'studentDOB' ,'studentGrade' ,'studentClass' ,'studentAllergy' ,'studentPhoto' ,'removeDate') VALUES (:id, :first, :last, :fam, :dob, :grade, :class, :allergy, :date)";

正确的形式:

$query = "INSERT INTO `sundayschool`.`exstudentinfo` (`studentID` ,`studentFirst` ,`studentLast` ,`famID` ,`studentDOB` ,`studentGrade` ,`studentClass` ,`studentAllergy` ,`studentPhoto` ,`removeDate`) VALUES (:id, :first, :last, :fam, :dob, :grade, :class, :allergy,:photo,:date)";
$statement = $db->prepare($query);
$statement->bindValue(':id',$studentID, PDO::PARAM_INT);
$statement->bindValue(':first',$studentFirst, PDO::PARAM_STR);
$statement->bindValue(':last',$studentLast, PDO::PARAM_STR);
$statement->bindValue(':fam',$studentFam, PDO::PARAM_INT);
$statement->bindValue(':dob',$studentDOB, PDO::PARAM_STR);
$statement->bindValue(':grade',$studentGrade, PDO::PARAM_STR);
$statement->bindValue(':class',$studentClass, PDO::PARAM_STR);
$statement->bindValue(':allergy',$studentAllergy, PDO::PARAM_STR);
$statement->bindValue(':date',$removeDate, PDO::PARAM_STR);
$statement->execute();

///// step 2 - Delete student from studentinfo table //////
$stmt = $db->prepare("DELETE FROM studentinfo WHERE studentID=:id");
$stmt->bindValue(':id', $studentID);
$stmt->execute();
echo $studentFirst . " " . $studentLast . " has been removed from the system (and backed up)";
echo "<p><a href='admin.php'>&lt;&lt; Return to administration page</a></p>";

Answer 1

这让我一直想知道为什么每个人都如此渴望为每个简单的插入编写几屏代码。
如果您可以获得一个准备好插入到执行中的数组，那么获取对象有什么意义？

$row = $statement->fetch(PDO::FETCH_ASSOC));
$row['removeDate'] = date("Y-m-d");
$query = "INSERT INTO exstudentinfo (studentID ,studentFirst,studentLast,famID,studentDOB,studentGrade,studentClass,studentAllergy,studentPhoto,removeDate) VALUES (:id, :first, :last, :fam, :dob, :grade, :class, :allergy, :date)";
$statement = $db->prepare($query);
$statement->execute($row);

没有双重分配和绑定(bind)!

更不用说 mysql 可以让你做到这一点 in a single query

INSERT INTO exstudentinfo SELECT *, CURDATE() as removeDate FROM studentinfo