0

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!

0 Answers0