cakephp: how to make NOW() work in search conditio

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

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 2

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

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

Answer 3

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 4

If you are looking for a way to do it with PHP-function istead of MySQL-functions you don't have to use the verbous format $conditions = array('begin >' => date('Y-m-d H:i:s')), MySQL understands the shorter 'c' preset of PHPs date() for ISO 8601 dates with $conditions = array('begin >' => date('c')).

I like this for doing queries like 'begin >' => date('c', strtotime("+4 weeks")), which is very readable code and great to debug when you look at the SQL-Queries.

Answer 5

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.