使用PHP更新MySQL条目

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.

Screenshot

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 :)

Here are the problems with your code.

You're mixing mysql_ functions with the MySQLi_ API and they do not intermix.

This being all instances of mysql_real_escape_string() which need to be replaced with mysqli_real_escape_string($mysqli, $_POST['var']).

  • Use a prepared statement instead, they're much safer.

Then you have a trailing comma in the UPDATE query's WHERE clause:

tvRoom='$tvRoom',

in here:

UPDATE tvdbase
  SET
  tvDate='$tvDate',
  tvCourse='$tvCourse',
  tvRoom='$tvRoom', <<< right there, remove it
  WHERE id=$id

This is why I write my SELECT and UPDATE queries this way...

SELECT x
     , y
     , z
  FROM my_table;

It makes this kind of error almost impossible.