willium willium - 6 months ago 36
PHP Question

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO

I've looked through all the other stackoverflow (and google) posts with the same problem, but none seemed to address my problem.

I am using pdo and php.

My code:

$vals = array(
':f'=>$email,
':to'=>$recipient,
':name'=>$name,
':subject'=>$subject,
':message'=>$message
);
print_r($vals);
try {

$pdo = new PDOConfig();

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "SELECT * FROM messages WHERE `message` LIKE :message AND `from` = :f";

$q = $pdo->prepare($sql);
$q->execute(array(':message' => $vals[':message'], ':f' => $vals[':f']));
$resp = $q->fetchAll();

foreach ($resp as $row) {
throw new Exception('Please do not post the same message twice!');
}

$sql = "INSERT INTO messages (`from`, to, name, subject, message) VALUES (:f, :to, :name, :subject, :message)";
$q = $pdo->prepare($sql);
$q->execute($vals);
}

catch(PDOException $e) {
echo $e->getMessage();
}


and the first print_r gives

Array ( [:from] => abc@gmail.com [:to] => lala@me.com [:name] => abc [:subject] => abc [:message] => abc )


which is expected (none are null)

but it outputs the error


SQLSTATE[42000]: Syntax error or access violation: 1064 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 'to, name, subject, message) VALUES ('jonah@j.com', 'lala@me.com', 'jona' at line 1


No idea how to fix this... ideas?

Answer

from is a keyword in SQL. You may not used it as a column name without quoting it. In MySQL, things like column names are quoted using backticks, i.e. `from`.

Personally, I wouldn't bother; I'd just rename the column.

Comments