I have a MySQL database with a table which has 4 columns (id, tvDate, tvCourse, tvRoom)
I have created a php page with connects to the database and returns the rows of the database table in an HTML table. I have added an edit link on each row to be able to edit the entries. The link calls up a php file with the row id (eg: edit.php?id=1) and shows the current content which you can then change.
Everything up to this point works fine but my problem is that when you change the content and click the update button I get the following error:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE id=1' at line 638
(same url edit.php?id=1) and the record does not get updated.
This is my code:
<?php include('includes/database.php'); ?>
<?php
//Assign get variable
$id = $_GET['id'];
//Create select query
$query ="SELECT * FROM tvdbase
WHERE id = $id";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);
if($result = $mysqli->query($query)){
//Fetch object array
while($row = $result->fetch_assoc()) {
$tvDate = $row['tvDate'];
$tvCourse = $row['tvCourse'];
$tvRoom = $row['tvRoom'];
}
$result->close();
}
?>
<?php
if($_POST){
//Assign get variable
$id = $_GET['id'];
//Assign Variables
$tvDate = mysql_real_escape_string($_POST['tvDate']);
$tvCourse = mysql_real_escape_string($_POST['tvCourse']);
$tvRoom = mysql_real_escape_string($_POST['tvRoom']);
//Create update
$query = "UPDATE tvdbase
SET
tvDate='$tvDate',
tvCourse='$tvCourse',
tvRoom='$tvRoom',
WHERE id=$id
";
$mysqli->query($query) or die($mysqli->error.__LINE__);
$msg="Updated";
header('Location:index.php?msg='.urlencode($msg).'');
exit;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Edit Page</title>
</head>
<body>
<form role="form" method="post" action="new.php?id=<?php echo $id; ?>">
<label>Date</label>
<input name="tvDate" type="text" value="<?php echo $tvDate; ?>" placeholder="Enter Date">
<label>Course</label>
<input name="tvCourse" type="text" value="<?php echo $tvCourse; ?>" placeholder="Enter Course">
<label>Room</label>
<input name="tvRoom" type="text" value="<?php echo $tvRoom; ?>" placeholder="Enter Room">
<input type="submit" value="Update Room" />
</form>
</body>
</html>
I suspect my problem is in the POST method somewhere. I'm still fairly new to PHP and mySQL so I appologise in advance for any bad coding :)
