cakephp: how to make NOW() work in search condition ?

Question

I'm trying to get all records with date greater then now. I tried this but it doeasnt work:

$all_dates = $this->TourDate->find('all', array('conditions' => array('TourDate.date >=' => 'NOW()'), 'order' => array('TourDate.date ASC')));

If I replace NOW() with the current date it works. Why's that?

Answer 1

I do not use CakePHP but I am pretty sure that your 'NOW()' is parsing to string and finally you got something like

TourDate.date >= 'NOW()'

Maybe you should just try

array('TourDate.date >= NOW()')

as a value only instead of spliting it to key => value style ?

Answer 2

hsz is correct. Cake is interpreting the NOW() condition as the string 'NOW()'. Providing a full SQL snippet in your conditions will work, as hsz suggests.

You might be tempted to use date('Y-m-d H:i:s') in place of NOW() (as I suggested in the first version of this answer). Don't do it! As hsz points out below, this will prevent the query from being cached.

Answer 3

I believe CakePHP strips out any SQL like functions to prevent a SQL injection attacks. Much as it annoys me, I usually use the date('Y-m-d H:i:s') trick too.

Answer 4

'conditions' => array('TourDate.date >= NOW()')

Otherwise, cakephp will quote the NOW() function, and mysql will think of it as a string.