Search code examples
mysqlmysql-error-1064

Syntax error in MySQL update query

I have a database table with 13 fields, 9 of which are populated by user input from a form. Inserting a new row works fine, but I'm having a problem with updating an existing row. I asked a friend for his input, and he couldn't help out.

I'm required to update all user-adjustable fields since each adjustment can cover one to all fields, thus requiring a query with multiple SET statements. The update query is

$query = sprintf("UPDATE tour 
  SET StopNum=%d, SET ForwardLink=%d, SET BackLink=%d, SET LeftLink=%d, 
  SET RightLink=%d, SET Name= '%s', SET Lat=%f, SET Lon=%f, SET Heading=%d 
  WHERE FileName='%s'", $_POST['curStop'], $_POST['nextStop'], $_POST['prevStop'],
  $_POST['leftStop'], $_POST['rightStop'], $location, $latitude, $longitude, 
  $heading, $image);

Using the above query, I get the following printout in an example update:

UPDATE tour SET StopNum=0, SET ForwardLink=0, SET BackLink=0, SET LeftLink=0, SET RightLink=0, SET Name= 'Start', SET Lat=98.154000, SET Lon=-75.214000, SET Heading=100 WHERE FileName='../panos/photos/1-prefix_blended_fused.jpg'

According to the mysql error message, I need to check the manual for the right syntax to use near 

'SET ForwardLink=0, SET BackLink=0, SET LeftLink=0, SET RightLink=0, SET Name= 'S' at line 1

The field 'Name' is defined as a nullable varchar(250) container, so 5 letters is more than enough room for storage.

Solution

  • The syntax should be:

    UPDATE table SET column=1,column1=2,column2=3 WHERE filename='asdf'