You have an error in your SQL syntax;

Question

I am trying to update a film table and I am getting this error:

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 '(name,release,runtime,rating, description,price) VALUES ('World War ' at line 1

Here is my code:

$sql = mysql_query("UPDATE  film  SET (`name`,`release`,`runtime`,`rating`,   `description`,`price`) VALUES ('$name','$release','$runtime','$rating','$des','$price' WHERE film_id ='$fid')") or die (mysql_error());

$fid = $row['film_id'];
$name = mysql_real_escape_string($_POST['name']);
$release = ($_POST['release']);
$runtime = ($_POST['runtime']);
$release = $row['release'];
$rating = $row['rating'];
$runtime = $row['runtime'];
$des = $row['description'];
$price = $row['price'];

score 2 · Answer 1 · edited May 25 '19 at 13:53

2

You're confusing INSERT and UPDATE syntax:

UPDATE `film`  
SET `name` = '$name',
    `release` = '$release',
    `runtime` = '$runtime',
    `rating` = '$rating',   
    `description` = '$des',
    `price` = '$price' 
WHERE `film_id` = '$fid'

Some additional notes:

FYI, you shouldn't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.
I don't see where you are sanitize your data so you might be open to SQL injections.
Using mysql_real_escape_string() here: $name = mysql_real_escape_string($_POST['name']); serves no purpose unless you are running another query we don't see after this.
The parenthesis on this line are unnecessary: $release = ($_POST['release']);

edited May 25 '19 at 13:53

Zoe

27,060
21
118
148

answered Apr 24 '14 at 00:59

John Conde

217,595
99
455
496

1

OP's code could go either way; the reason I didn't touch this one, non monsieur ;-) – Funk Forty Niner Apr 24 '14 at 01:03
I hesitated at first but then I felt pretty good about them wanted to do an update. It looks like they took an insert and tried to make an update by changing the keywords without being aware that the syntax is much more different then that. – John Conde Apr 24 '14 at 01:05
I've seen the same thing happen last week actually. – Funk Forty Niner Apr 24 '14 at 01:08

score 0 · Answer 2 · answered Apr 24 '14 at 01:04

0

Your syntax with the update is not correct, try this one

$sql = mysql_query("UPDATE film SET name='$name',release='$release,runtime='$runtime',rating='$rating',description='$description' where film_id = '$id'");

answered Apr 24 '14 at 01:04

GarfieldBesa

45
1
1
10

You have an error in your SQL syntax;

2 Answers2