CODEWITHSUNDEEP
phpmysqlinsertpdo
Schwarz.Oz
Schwarz.Oz

Reputation: 199

PDO Insert not working

I'm trying to take a record from one table in my database and place it in another table (as a backup of the data - needs to be kept for 7 years, including deleted data). The select works, the delete works, but the insert doesn't. What's wrong? What can I do to fix it?

Code:

////////// 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");
    }

SOLVED: The problem is in $query... I've used ordinary apostrophes (') rather than the correct backtick (`) - and I'd forgotten to include the photo field in the bound values.

$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)";

Correct form:

$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>";

Upvotes: 0

Views: 3781

Answers (4)

Nick
Nick

Reputation: 6346

At first glance, your line:

$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)";

Needs to change to use backticks instead of single quotes:

$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
)";

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157989

It keeps me in constant wonder why everyone so eager to write several screens of code for the every simple insert.
What's the point in fetching object if you can get an array which is ready to be inserted into execute?

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

without double assigning and binding!

not to mention that mysql can let you to do that in a single query

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

Upvotes: 5

SeanWM
SeanWM

Reputation: 17009

You're missing the part where you insert studentPhoto between allergy and date. You're trying to insert 10 fields but you're only providing information for 9.

Also, lose the quotes here. You don't need them:

$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)";

Remember to add studentPhoto

Upvotes: 1

jeroen
jeroen

Reputation: 91792

You should really add proper error handling, but the problem is that you want to insert 9 values into 10 fields and you are using quotes for your field and table names:

$query = "INSERT INTO sundayschool.exstudentinfo
           (studentID ,studentFirst ,studentLast ,famID ,studentDOB ,studentGrade ,studentClass ,studentAllergy ,studentPhoto ,removeDate)
           // 10 fields
         VALUES
           (:id, :first, :last, :fam, :dob, :grade, :class, :allergy, :date)";
           // 9 values, photo is missing

If you need to quote for example mysql reserved words, you need to use the backtick for your table or field names:

$query = "INSERT INTO `sundayschool`.`exstudentinfo`
            // etc.

Upvotes: 2

Related Questions