SQL intervals as query parameters

Yii 1.1 General Discussion
1

This is what I would like to do:


Yii::app()->db->createCommand(

  'select * from t 

  where t.created <= :created

  and t.created > :created - interval :range'

)->execute(array(

  ':created' => $form->created,

  ':range' => $form->range,

));

But it doesn’t work because Yii puts quotes around :range. The query ends up being:


  select * from t 

  where t.created <= '2011-09-01'

  and t.created > :created - interval '1 month'

which is not valid SQL.

What’s an orthodox way to solve this?

2

why not do this:




Yii::app()->db->createCommand(

  'select * from t 

  where t.created <= :created

  and t.created > :range'

)->execute(array(

  ':created' => $form->created,

  ':range' => date('Y-m-d', strtotime('-'.$form->range, strtotime($form->created))),

));

of course you could rename ‘range’ to make more sense in this format

3

Did you try:




Yii::app()->db->createCommand(

  'select * from t 

  where t.created <= :created_1

  and t.created > :created_2 - interval :range'

)->execute(array(

  ':created_1' => $form->created,

  ':created_2' => $form->created,

  ':range' => $form->range,

));