(Yii) SQLSTATE[42000]: Syntax error or access violation: 1064

Question

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

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

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.

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!!!

vee vee · Accepted Answer · 2013-08-12T04:41:02

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

(Yii) SQLSTATE[42000]: Syntax error or access violation: 1064

1 Answers