SQLSTATE[42000]:语法错误或访问违例:1064 SQL语法有错误- PHP - PDO [duplicate]

This question already has an answer here:

这个问题已经有了答案:

Syntax error due to using a reserved word as a table or column name in MySQL 1 answer
语法错误，因为在MySQL 1中使用一个保留词作为表或列名

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

我查看了所有其他有相同问题的*(和谷歌)帖子，但似乎没有一个能解决我的问题。

I am using pdo and php.

我正在使用pdo和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

第一个print_r给出

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

但它输出了误差

QLSTATE[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

QLSTATE[42000]:语法错误或访问违规:1064 SQL语法有错误;检查与MySQL服务器版本对应的手册，找到正确的语法，在第1行使用“from”、“to”、“to”、“subject”、“message”值(“abc@gmail.com”、“lala@me.com”)

No idea how to fix this... ideas?

不知道怎么解决这个问题……想法吗?

4 个解决方案

#1

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`.

from是SQL中的关键字。除非引用，否则不能将其用作列名。在MySQL中，像列名之类的东西都是用反勾号引用的，例如。“从”。

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.

正如评论中指出的，to是另一个SQL关键字，因此也需要引用它。drupal.org上的人们用SQL保存了一个单词列表，这很方便。

#2

I've got this exact error, but in my case I was binding values for the LIMIT clause without specifying the type. I'm just dropping this here in case somebody gets this error for the same reason. Without specifying the type "... LIMIT :limit OFFSET :offset;" resulted in "... LIMIT '10' OFFSET '1';" instead of "... LIMIT 10 OFFSET 1;". What helps to correct that is the following:

我得到了这个准确的错误，但是在我的例子中，我是为LIMIT子句绑定值，而没有指定类型。我把这个放在这里以防有人因为同样的原因得到这个错误。没有指定类型“…”极限:极限偏移:偏移;限制'10'偏移'1';'代替'…限制10抵消1;”。有助于纠正这一点的是:

$stmt->bindParam(':limit', intval($limit, 10), \PDO::PARAM_INT);
$stmt->bindParam(':offset', intval($offset, 10), \PDO::PARAM_INT);

#3

Same pdo error in sql query while trying to insert into database value from multidimential array:

在sql查询中，当尝试从多向数组插入到数据库值时，同样的pdo错误:

$sql = "UPDATE test SET field=arr[$s][a] WHERE id = $id";
$sth = $db->prepare($sql);    
$sth->execute();

Extracting array arr[$s][a] from sql query, using instead variable containing it fixes the problem.

从sql查询中提取数组arr[$s][a]，使用包含数组的变量来修复问题。

#4

ALTER TABLE `{$installer->getTable('sales/quote_payment')}`
ADD `custom_field_one` VARCHAR( 255 ) NOT NULL,
    ADD `custom_field_two` VARCHAR( 255 ) NOT NULL;

Add backtick i.e. " ` " properly. Write your getTable name and column name between backtick.

添加撇号。“正常”。在背后写你的名字和列名。

#1