（Yii）SQLSTATE [42000]：语法错误或访问冲突：1064

I'm getting a "Syntax error or access violation: 1064" error.

我收到“语法错误或访问冲突:1064”错误。

I'm spending a few hours to fix this problem, but I can't solve this.

我花了几个小时来解决这个问题,但我无法解决这个问题。

This is the error message I got.

这是我收到的错误消息。

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 'comment_flag = '1' created = '0000-00-00 00:00:00' WHERE id = '21'' at line 11

CDbCommand无法执行SQL语句:SQLSTATE [42000]:语法错误或访问冲突:1064您的SQL语法中有错误;检查与您的MySQL服务器版本对应的手册,以便在'comment_flag ='1'created ='0000-00-0000,00:00''WHERE id ='21''第11行附近使用正确的语法

This error occurs when I get my input validated and execute this static method.

当我验证输入并执行此静态方法时,会发生此错误。

Post::updatePost($model->attributes);

This is what I have in $model->attributes.

这就是我在$ model-> attributes中的含义。

array(13) {
    ["id"]=>
    string(2) "21"
    ["title"]=>
    string(13) "my first post"
    ["parent_category"]=>
    string(1) "1"
    ["category"]=>
    string(2) "22"
    ["body"]=>
    string(11) "hello there"
    ["more"]=>
    NULL
    ["description"]=>
    string(19) "this is description"
    ["created"]=>
    string(19) "0000-00-00 00:00:00"
    ["modified"]=>
    NULL
    ["publish_status"]=>
    string(9) "published"
    ["comment_flag"]=>
    string(1) "1"
    ["filename"]=>
    string(8) "accessup"
    ["password"]=>
    string(1) "3"

}

This is my code for this static method.

这是我的静态方法的代码。

public static function updatePost($data){

    $connection=Yii::app()->db;

    $sql = "UPDATE {{post}}
            SET title = :title,
                description = :description,
                filename = :filename,
                body = :body,
                more = :more,
                parent_category = :parent_category,
                category = :category,
                password = :password,
                publish_status = :publish_status
                comment_flag = :comment_flag
                created = :created
                WHERE id = :id";


    $command=$connection->createCommand($sql);

    $command->bindParam(":title", $data['title'], PDO::PARAM_STR);
    $command->bindParam(":description", $data['description'], PDO::PARAM_STR);
    $command->bindParam(":filename", $data['filename'], PDO::PARAM_STR);
    $command->bindParam(":body", $data['body'], PDO::PARAM_STR);
    $command->bindParam(":more", $data['more'], PDO::PARAM_STR);
    $command->bindParam(":parent_category", intval($data['parent_category']), PDO::PARAM_INT);
    $command->bindParam(":category", $data['category'], PDO::PARAM_INT);
    $command->bindParam(":password", $data['password'], PDO::PARAM_INT);
    $command->bindParam(":publish_status", $data['publish_status'], PDO::PARAM_STR);
    $command->bindParam(":created", $data['created'], PDO::PARAM_STR);
    $command->bindParam(":comment_flag", $data['comment_flag'], PDO::PARAM_INT);
    $command->bindParam(":id", $data['id'], PDO::PARAM_INT);
    $result = $command->execute();
    return $result;

}

Could anyone please help me!?

有人可以帮帮我!?

Thanks in advance!!!

提前致谢!!!

1 个解决方案

#1

You are missing comma between fields in your query in the following lines:

您在查询中的字段之间缺少逗号,如下所示:

publish_status = :publish_status
comment_flag = :comment_flag

Fixed query:

$sql = "UPDATE {{post}}
        SET title = :title,
            description = :description,
            filename = :filename,
            body = :body,
            more = :more,
            parent_category = :parent_category,
            category = :category,
            password = :password,
            publish_status = :publish_status,
            comment_flag = :comment_flag,
            created = :created
          WHERE id = :id";