Wednesday, 29 March 2017

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(
':from' => $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";
$q = $pdo->prepare($sql);
$q->execute(array(':message' => $vals[':message']));
$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 (:from, :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 'from, to, name, subject, message) VALUES ('abc@gmail.com', 'lala@me.com' at line 1




No idea how to fix this. any 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.



PS. as pointed out in the comments, to is another SQL keyword so it needs to be quoted, too. Conveniently, the folks at drupal.org maintain a list of reserved words in SQL.


No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...