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

问题描述

在我的 Yii 应用程序中，我有一个表示 siteconfig 表并有四列的模型:

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

• 整数 config_id,
• string key,
• string value,
• string update_time.

我使用 Gii 创建了一个模型(以确保我不会犯任何错误).我没有在这里发布完整的代码，因为这是我 100% 未修改的，Gii 生成的标准模型代码.由于我的问题与搜索有关，所以我只发布生成代码的重要部分(search() 方法):

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 行的 'key='sitename' LIMIT 1' 附近使用正确的语法.执行的 SQL 语句是:SELECT value FROM siteconfig tWHERE key=:key LIMIT 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

我哪里做错了?

推荐答案

您使用了 key 作为列名，这是一个 MySQL 中的保留字.Yii 在查询中使用 表别名，但没有采取任何特殊的小心将保留字用作列名.所以，你必须自己解决这个问题.

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);

这应该可以解决您的问题.

This should solve your problem.

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