php准备好的陈述|| 语法错误或访问冲突：1064

Question

The code below is returning this error (Line numbers are in brackets[])

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' location, type, capacity, length, qty, serial, cert, lastinsp, inspby, datein, ' at line 1' in D:\Websitesiggingregister\pdo.php:90 Stack trace: #0 D:\Websitesiggingregister\pdo.php(90): PDOStatement->execute(Array) #1 {main} thrown in D:\Websitesiggingregister\pdo.php on line 90

I've edited the entry manually in PHPMyAdmin and the suggested 'PHP Code' came out as:

$sql = "UPDATE `riggingregister`.`register` SET `register` = \'100\',
`location` = \'testing\', `type` = \'Chains - 4 leg\', `capacity` = \'10mm\',
`length` = \'testing\', `qty` = \'testing\', `serial` = \'testing\', 
`cert` = \'testing\', `lastinsp` = \'testing\', `inspby` = \'testing\',
`datein` = \'testing\', `dateout` = \'testing\', 
`status` = \'HOLD\', `notes` = \'EDITED!\' WHERE `register`.`id` = 1;";

But I'm not sure how I can translate that into my prepared statement.

I've been reading tutorials, manuals and every possible question that SO offered me and can't see where I'm going wrong. Would love for someone to point me in the right direction.

Cheers

echo "the <b>CONFIRM CHANGES</b> button was pressed<br /><br />";

    $query = $dbh->prepare('UPDATE register SET register, location, type, capacity, length, qty, serial, cert, lastinsp, inspby, datein, dateout, status, notes VALUES ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? WHERE id = $id');
    $data = array($register, $location, $type, $capacity, $length, $qty, $serial, $cert, $lastinsp, $inspby, $datein, $dateout, $status, $notes);
    $query->execute($data);

    echo "Data has been written to the database!<br /><br />";

Answer 1

Maybe something like

echo "the <b>CONFIRM CHANGES</b> button was pressed<br /><br />";

$data = array(
    'register' => $register,
    'location' => $location,
    'type' => $type,
    'capacity' => $capacity,
    'length' => $length,
    'qty' => $qty,
    'serial' => $serial,
    'cert' => $cert,
    'lastinsp' => $lastinsp,
    'inspby' => $inspby,
    'datein' => $datein,
    'dateout' => $dateout,
    'status' => $status,
    'notes' => $notes
);

$fieldDetails = NULL;

foreach($data as $key=> $value) {
    $fieldDetails .= "`$key`=:$key,";
}

$fieldDetails = rtrim($fieldDetails, ',');

$dbh = $this->prepare("UPDATE register SET $fieldDetails WHERE id = :id");

foreach ($data as $key => $value) {
    $dbh->bindValue(":$key", $value);
}

$dbh->bindValue(":id", $id);

if($dbh->execute()) {

     echo "Data has been written to the database!<br /><br />";
}