I keep getting sql syntax error on the block that is supposed to update the table in the DB. Ive tried several different ways to write the syntax and also tried without using prepared statement. I have a table that displays movietitles with the options to edit & delete. When selecting Edit it grabs the id >checks in DB >displays the right data in the form. So far so good. But when i click on update, the query doesn't want to run due to syntax error. I suspect other things might be the reason it wont run.
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>
<?php
require_once ('dbinfo.php');
/*This block gets Id from the URL in the movietitle that is being edited
and displays the data in the form */
$id = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_SPECIAL_CHARS);
$query3 = "SELECT * FROM movie WHERE id=$id";
$result = $con->query($query3);
if (!$result) die($con->error);
$row = mysqli_fetch_array($result);
$title = $row['title'];
$director = $row['director'];
$year = $row['year'];
$category = $row['categoryid'];
//This block updates the moviedata if the submitbutton is pressed
if (isset($_POST['update'])) {
$query = "UPDATE movie SET 'title'=?, 'director'=?, 'year'=?, 'categoryid'=? WHERE 'id'=$id";
if (!$stmt = $con->prepare($query)) {
echo "Prepare failed: (" . $con->errno . ") " . $con->error;
}
if (!$stmt->bind_param("ssii", $_POST['title'], $_POST['director'], $_POST['year'], $_POST['categoryid'])) {
echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->execute()) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
//Returns to the mainform after update is done
header('Location: form.php');
}
?>
<form method="post" action="edit.php">
<fieldset> <legend>Edit movie</legend>
<input type="radio" name="category" value="1" <?php if ($category=="1") echo "checked"; ?>> Sci-Fi<br>
<input type="radio" name="category" value="2" <?php if ($category=="2") echo "checked"; ?>> Horror<br>
<input type="radio" name="category" value="3" <?php if ($category=="3") echo "checked"; ?>> Thriller<br>
<input type="radio" name="category" value="4" <?php if ($category=="4") echo "checked"; ?>> Comedy<br>
<input type="radio" name="category" value="5" <?php if ($category=="5") echo "checked"; ?>> Fantasy<br><br>
<input type="text" id="title" name="title" maxlength="50" placeholder="Title" value="<?php echo $title; ?>" required> <br>
<input type="text" id="director" name="director" maxlength="50" placeholder="Director" value="<?php echo $director; ?>" required><br>
<input type="text" id="year" name="year" maxlength="4" placeholder="Year" value="<?php echo $year; ?>" required><br><br>
<input type="submit" value="Update" name="update" id="update">
</fieldset>
</form>
</body>
</html> 