CDbExpression

February 23rd, 2009

Ever want to set an attribute in Active Record with a MySQL function? For instance, often times you need to set an attribute to the well known MySQL function “NOW()”. Most people first attempt this in the following way:

<?php
$model->createdDate = 'NOW()';

The above, however, will most definitely not work. Why not? Because Yii will first escape the value and then enclose it in quotes, like the following:

... `createdDate`= 'NOW()' ...

Since it is enclosed in quotes, it is treated as a string and not a function. If Yii did not clean the data like this, you would have to, and that would be very annoying.

As of version 1.0.2 of Yii however, there is a way to get around this:

<?php
$model->createdDate = new CDbExpression('NOW()');

CDbExpression is a special class for using MySQL expressions in your queries. You use it just like in the above example.

Share and Enjoy:
  • Digg
  • del.icio.us
  • Facebook
  • Google Bookmarks
  • Technorati
  • Reddit
  • RSS
  • Twitter

Categories: Uncategorized

Leave a comment

Comments Feed4 Comments

  1. Wei

    To be more precise, prepared statements are used to bind the values. CDbExpression allows for arbitrary SQL fragments, therefore, extreme care must be taken when the fragment is generated from variable values such as user inputs. Avoid arbitrary SQL if possible.

  2. Konstantin Mirin

    Actually, this thing doesn’t work with all MySQL+PHP configurations. I experienced the problems with such approach and had to use simply date(‘Y-m-d H:i:s’). I didn’t have time to investigate the issue, so it’s just a quick note – be attentive :)
    More pitfalls&tips when changing environment for Yii in my blog (this is not advertisement:))

  3. Garbago Olso

    well, that is a nice example of what frameworks do with your development process: instead of concentrating on the main work, you are trying to find the trick how to do things you already know with the framework. especially with SQL there is so much more than a simple select * from bla – so all your sql knowledge is now worth nothing because the framework needs some special trick to to it. THAT is the moment when you find that the framework kills your worktime.

  4. Jonah

    @Wei
    Thanks for the clarification

    @Konstantin Mirin
    Really? That’s interesting. If you investigate it further I would be interested in knowing the cause.

    @Garbago Olso
    I see your point, but don’t forget that Yii does *not* force you to actually use Active Record. You may instead write direct SQL as you would without a framework. (see http://www.yiiframework.com/doc/guide/database.dao)

    So I would not use this “feature” against it. It is only optional that you use Active Record. I do confess that Yii’s Active Record is not as extensive as CakePHP’s in that it is not as flexible, but all in all, I would never go back to Cake (and besides, because Cake’s active record was so extensive, it was also slow).

    It is a learning curve but once you get around it I believe you will be developing faster than ever.

Leave a comment

Feed

http://php-thoughts.cubedwater.com / CDbExpression