MySQL Syntax Error

Question

$sql = "UPDATE galleries SET name='$name', desc='$desc', mainthumb='$mt' 
        WHERE id='$id'";

this throws an error for some godforsaken reason. I must be way too tired because I don't see it.

I've confirmed that all the values are being posted. What's worse, it's an almost exact copy any query that works fine.

Update:

This has been solved. It was the fact that desc didn't have backticks. I'm also going to use PDO instead as suggested.

Answer 1

echo $sql and see what it actually becomes. It looks like an easy target for SQL injection, unless you took care of that.

Answer 2

Is desc not a keyword that you can not use as a column name?

Answer 3

You have a column called desc, which is a reserved word. You will need to quote it with backticks.

`desc`='$desc'

Answer 4

yes, make sure you first sanitize the data, using mysql_real_escape_string for instance.

Then echo your mysql error (mysql_error() ) it will give you more hints as to where is the error;

<?php
$link = mysql_connect("localhost", "mysql_user", "mysql_password");

mysql_select_db("nonexistentdb", $link);
echo mysql_errno($link) . ": " . mysql_error($link). "\n";

mysql_select_db("kossu", $link);
mysql_query("SELECT * FROM nonexistenttable", $link);
echo mysql_errno($link) . ": " . mysql_error($link) . "\n";
?>

Answer 5

Did you sanitize all the parameters before mixing them with the sql statement?
desc is a reserved word in MySQL, you have to explicitly mark it as an identifier:

An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it. [...]
The identifier quote character is the backtick (“`”):

$mysql = mysql_connect(...

$sql = "
    UPDATE
     galleries
    SET
     name='" . mysql_real_escape_string($_POST['name'], $mysql) . "',
     `desc`='" . mysql_real_escape_string($_POST['desc'], $mysql) . "',
     mainthumb='"  . mysql_real_escape_string($_POST['mt'], $mysql) . "' 
  WHERE
    id='"  . mysql_real_escape_string($_POST['id'], $mysql) . "'
 ";

or even better: use prepared statements

Answer 6

$sql = "UPDATE `galleries` SET 
           name='".$name."', 
           desc='".$desc."', 
           mainthumb='".$mt."' 
        WHERE id='".$id."'";

This could be one alternative way to handle it. Although I would gone PDO as VolkerK suggested it. I would also Echo to see what it would output as well. Also as Ben suggested, Desc may be a reserve word.