CODEWITHSUNDEEP
phpmysqlsqlpdoprepared-statement
ZachFlem
ZachFlem

Reputation: 53

php prepared statements || Syntax error or access violation: 1064

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:\Websites\riggingregister\pdo.php:90 Stack trace: #0 D:\Websites\riggingregister\pdo.php(90): PDOStatement->execute(Array) #1 {main} thrown in D:\Websites\riggingregister\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 />";

Upvotes: 0

Views: 120

Answers (2)

david strachan
david strachan

Reputation: 7228

You have the wrong format for UPDATE Your query is still open to injection by passing $id directly. Add $id to end of $data array.

TRY

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

Upvotes: 1

AdRock
AdRock

Reputation: 3096

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

Upvotes: 0

Related Questions