Update SQL query with PHP error

Question

Here is the error I get when I submit the updated form: 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 'WHERE id='19' LIMIT 1' at line 1

Here is the PHP and HTML for the edit (update) page.

    <?php

require_once('../../../private/initialize.php');

if(!isset($_GET['id'])) {
  redirect_to(url_for('/staff/subjects/index.php'));
}
$id = $_GET['id'];

if(is_post_request()) {

  // Handle form values sent by new.php

  $subject = [];
  $subject['id'] = $id;
  $subject['menu_name'] = $_POST['menu_name'] ?? '';
  $subject['description'] = $_POST['description'] ?? '';




  $result = update_subject($subject);
  if($result === true) {
    redirect_to(url_for('/staff/subjects/show.php?id=' . $id));
  } else {
    $errors = $result;
  }

} else {

  $subject = find_subject_by_id($id);

}

$subject_set = find_all_subjects();
$subject_count = mysqli_num_rows($subject_set);
mysqli_free_result($subject_set);

?>

<?php $page_title = 'Edit Subject'; ?>
<?php include(SHARED_PATH . '/staff_header.php'); ?>

  <a class="back-link" href="<?php echo url_for('/staff/subjects/index.php'); ?>">&laquo; Back to List</a>

  <div class="subject edit">
    <h1>Edit Subject</h1>

    <?php echo display_errors($errors); ?>

    <form action="<?php echo url_for('/staff/subjects/edit.php?id=' . h(u($id))); ?>" method="post">
      <dl>
        <dt>Subject name</dt>
        <dd><input type="text" name="menu_name" value="<?php echo h($subject['menu_name']); ?>"</dd>
      </dl>
      <dl>
        <dt>Description</dt>
        <dd>
          <textarea name="description" cols="60" rows="10"><?php echo h($subject['description']); ?></textarea>
        </dd>
      </dl>
      <div id="operations">
        <input type="submit" value="Edit Subject" />
      </div>
    </form>

  </div>


<?php include(SHARED_PATH . '/staff_footer.php'); ?>

This is my PHP update to update the record.

//UPDATE SUBJECTS
function update_subject($subject) {
global $db;

$errors = validate_subject($subject);
if(!empty($errors)) {
  return $errors;
}

$sql = "UPDATE subjects SET ";
$sql .= "menu_name='" . db_escape($db, $subject['menu_name']) . "', ";
$sql .= "description='" . db_escape($db, $subject['description']) . "', ";
$sql .= "WHERE id='" . db_escape($db, $subject['id']) . "' ";
$sql .= "LIMIT 1";

$result = mysqli_query($db, $sql);
// For UPDATE statements, $result is true/false
if($result) {
  return true;
} else {
  // UPDATE failed
  echo mysqli_error($db);
  db_disconnect($db);
  exit;
}}

Take a look on this question https://stackoverflow.com/questions/6289729/mysql-update-query-with-limit, you can use UPDATE LIMIT only if you have `row count`. — Ivan Kalita, Oct 25 '17 at 17:44

score 1 · Answer 1 · answered Oct 25 '17 at 17:49

You have a comma ( , ) right before the WHERE

$sql .= "description='" . db_escape($db, $subject['description']) . "', ";
$sql .= "WHERE id='" . db_escape($db, $subject['id']) . "' ";

change it to:

$sql .= "description='" . db_escape($db, $subject['description']) . "' ";

score 0 · Answer 2 · answered Oct 25 '17 at 17:47

0

Remove the , at the last from this line :

$sql .= "description='" . db_escape($db, $subject['description']) . "', ";

Use this :

$sql .= "description='" . db_escape($db, $subject['description']) . "' ";

answered Oct 25 '17 at 17:47

Sagar Jajoriya

2,377
1
9
17

Thanks for the help! – Tom Sheppard Oct 26 '17 at 08:31

Update SQL query with PHP error

2 Answers2