ActiveRecord的搜索返回“语法错误或访问冲突”错误 [英] ActiveRecord search returns 'Syntax error or access violation' error

问题描述

在我的Yii应用，我有一个模型，再presents siteconfig 表，有四列：

In my Yii application, I have a model that represents siteconfig table and have four columns:

• 在整数 config_id ，
• 字符串键，
• 字符串值，
• 字符串 UPDATE_TIME 。

• integer config_id,
• string key,
• string value,
• string update_time.

我在使用GII（以确保我不会犯任何错误）的模型。我这里不公布整个code，因为这是100％未修改由本人GII产生标准模型code。由于我的问题是有关搜索，我只公布产生code重要组成部分（即搜索（）法）：

I created a model using Gii (to ensure that I will not make any mistakes). I don't publish entire code here, cause this is 100% unmodified by me, standard model code generated by Gii. Since my problem is related to search, I only publish important part of generated code (the search() method):

public function search()
{
    // Warning: Please modify the following code to remove attributes that
    // should not be searched.

    $criteria=new CDbCriteria;

    $criteria->compare('config_id',$this->config_id);
    $criteria->compare('key',$this->key,true);
    $criteria->compare('value',$this->value,true);
    $criteria->compare('update_time',$this->update_time,true);

    return new CActiveDataProvider($this, array(
        'criteria'=>$criteria,
    ));
}

我试图使用生成的模型在正常的Yii ActiveRecord的搜索这样的：

I'm trying to use generated model in normal Yii ActiveRecord search like that:

$etona = new SiteConfigurationRecord();
$crit = new CDbCriteria();
$crit->select = "value";
$crit->condition = "key=:key";
$crit->params = array(":key"=>"sitename");
$etona = $etona->find($crit);

不过，而不是得到预期的搜索结果中，一个奇怪的（对我来说）发生错误：

But, instead of getting expected search results, a strange (for me) error occurs:

CDbCommand未能执行SQL语句：SQLSTATE [42000]：   语法错误或访问冲突：1064您的SQL错误   句法;检查对应于你的MySQL服务器版本的手册   为正确的语法，在1号线附近的'键=网站名称限制1使用。   执行的SQL语句是：从 siteconfig SELECT值t   其中key =：密钥限制1

CDbCommand failed to execute the SQL statement: 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 'key='sitename' LIMIT 1' at line 1. The SQL statement executed was: SELECT value FROM siteconfig t WHERE key=:key LIMIT 1

我上哪儿去了？

推荐答案

您使用键的列名，这是一个的保留字在MySQL中。 Yii中使用的查询表别名，但没有采取任何特殊照顾的情况下作为列名RESERVERD字。所以，你要好好照顾这个自己。

You used key for column name, which is a reserved word in MySQL. Yii uses table alias in queries, but does not take any special care in case of reserverd word used as columns names. So, you have to take care of this by yourself.

例如：

$etona = new SiteConfigurationRecord();
$crit = new CDbCriteria();
$crit->select = "value";
$crit->condition = "t.key=:key"; // 't' is default alias
$crit->params = array(":key"=>"sitename");
$etona = $etona->find($crit);

这应该解决您的问题。

这篇关于ActiveRecord的搜索返回“语法错误或访问冲突”错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！