scratching my head over this one, I have a form displaying all rows from a table, and the whole table is to be updated on form submit. The context is that this is the site navigation.
Error: 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 'order = '1' WHERE ID = '1' LIMIT 1' at line 1' in (path) Stack trace: #0 (path): PDOStatement->execute(Array) #1 {main} thrown in (path) on line 65
Form:
<form action="<?=$PHP_SELF?>" method="POST">
<?php
$navstmt = $dbh->prepare("SELECT *
FROM `navlinks`
ORDER BY `order` ASC
LIMIT 0 , 30 ");
$navstmt->execute();
$result = $navstmt->fetchAll();
echo "<table width='500px'><tr><td><b>ID</b></td><td><b>Display Title</b></td><td> <b>Page Name (URL)</b></td><td><b>Order</b></td><td><b>Delete</b></td></tr>";
foreach($result as $row)
{
echo '<tr><td><input type="text" name="ID[]" size="4" value="'.$row['ID'] . '"></td><td><input type="text" name="title[]" value="'.$row['title'] . '"></td><td><input type="text" name="url[]" value="'.$row['url'] . '"></td><td><input type="text" name="order[]" size="4" value="'.$row['order'] . '"><td><a href="#"><img src="img/cancel.gif" border="0"></a></td></tr>';
}
echo '</table>';
?>
<p><input type="submit" name="button" value="Save Changes"></p>
Save to database:
<?php
if($_POST['title']){
$size = count($_POST['title']);
$size = count($_POST['url']);
$size = count($_POST['order']);
$i = 0;
while ($i < $size) {
$title = $_POST['title'][$i];
$url = $_POST['url'][$i];
$id = $_POST['ID'][$i];
$order = $_POST['order'][$i];
$sql = "UPDATE navlinks SET title = :title, url = :url, order = :order WHERE ID = :id LIMIT 1";
$q = $dbh->prepare($sql);
$q->execute(array(':title' => $title, ':url' => $url, ':order' => $order, ':id' => $id));
echo '<div class="alert alert-success"><b>All done!</b> The navigation has been updated.</div>';
++$i;
}
}
?>
It is probably something really obvious I am sure, but any help would be greatly appreciated!