Update query in Yii

Question

I have one requirement in Yii where I have to update one table based on some condition. And I have to update the column with new_val = previous_value + new_val. But the code is not working as expected.

The code I tried is

$update = Yii::app()->db->createCommand()
->update('tbl_post', array('star'=>('star' + 1),'total'=>('total' + $ratingAjax)),
'id=:id',array(':id'=>$post_id));

In normal query the query will be

UPDATE tbl_post set star= star + 1,total = total + '$ratingAjax' where id = 1

Anybody knows where is mistake?

Solution 1

Try the following:

$update = Yii::app()->db->createCommand()
    ->update('tbl_post', 
        array(
            'star'=>new CDbExpression('star + 1'),
            'total'=>new CDbExpression('total + :ratingAjax', array(':ratingAjax'=>$ratingAjax))
        ),
        'id=:id',
        array(':id'=>$post_id)
    );

Using CDbExpression will allow you to send an expression for what to update the column value to be.

See: http://www.yiiframework.com/doc/api/1.1/CDbCommand#update-detail

and: http://www.yiiframework.com/doc/api/1.1/CDbExpression#__construct-detail

Solution 2

Your working with strings, try this :

$update = Yii::app()->db->createCommand()
->update('tbl_post', array('star'=>'star + 1','total'=> 'total + '.$ratingAjax),
'id=:id',array(':id'=>$post_id));

Solution 3

This should do the job:

   Post::model()->updateCounters(
        array('star'=>1),
        array('total'=>$ratingAjax),
        array('condition' => "id = :id"),
        array(':id' => $post_id),
    );

It will increment, not set the values

Comments

[Yogesh Suthar]

star and total is integer field not string.

[Philippe Boissonneault]

The SQL Query you are building is a string not an integer, this should generate the string UPDATE tbl_post set star= star + 1,total = total + [$ratingAjax variable value] where id = 1

[Philippe Boissonneault]

'star' + 1 this is not concatenation, this is string + integer, it will return 1 so your sql will be ... set star = 1 ...