MySQL error about updating table

Question

What I want is to update my database table by selecting the row by index number and updating the answer field on that row.

This is my form's select; it puts the index id to the option value. Also there is a textarea with the name "answer".

<select name="indexno" style="width:150px">
<option selected="selected">&nbsp;</option>
<?php 
require('dbconnect.php');

$query = mysql_query("SELECT * FROM mytable WHERE answer = '' ");

while($result = mysql_fetch_array($query))
  {
  echo "<option " . "value='" . $result['index'] . "'>";
  echo $result['index'];
  echo "</option>";
  }

?>
</select>

This is the PHP code:

$indexno = $_POST['indexno'];
$answer = $_POST['answer'];
$date = gmdate("Y-m-d\TH:i:s\Z");
$query = "UPDATE mytable 
             SET answerfield = '$answer',
                 date = '$date' 
           WHERE index = '$indexno'";

$link = mysql_query($query);

However, it is not working; the error message is:

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 'index = '2'' at line 1

Is `index` an integer column? You could try `$indexno` instead of `'$indexno'` — Kieren Johnstone, Jul 05 '11 at 19:57
... so did you try my suggestion? And next question, did my suggestion have any impact? Finally next question, did it fix the problem? — Kieren Johnstone, Jul 05 '11 at 19:59

score 3 · Answer 1 · answered Jul 05 '11 at 19:59

3

Try

WHERE `index` = '$indexno'";

answered Jul 05 '11 at 19:59

Karoly Horvath

94,607
11
117
176

1

Ah, bingo. `index` would be a reserved word and needs escaping. – Kieren Johnstone Jul 05 '11 at 20:00
Maybe `date` is also reserved, I can't remember but it's better not to use field names like this in the first place... – Karoly Horvath Jul 05 '11 at 20:01
@yi_H it worked however there is nothing changed in the database?! – user552828 Jul 05 '11 at 20:09
I think there's a big confusion in your code, `answer`, `answerfield`, `index`... Try to think it carefully what you're actually trying to do – Karoly Horvath Jul 05 '11 at 20:14
This reminds me of the SQL Reserved Word Palindrome: `SELECT Equals FROM Where WHERE From = 'Select'` – Chris Cunningham Jul 05 '11 at 20:16

MySQL error about updating table

1 Answers1

Linked