yii model search with daterange for time stamp

Question

can any post the how to filter a grid view timestamp(Y-m-d h:m:s) column using date picker. my model is below

   public function search()
{
    $criteria=new CDbCriteria();

                $criteria->condition="time_up BETWEEN UNIX_TIMESTAMP('$this->time_up_from') AND UNIX_TIMESTAMP('$this->time_up_to')";
    $criteria->compare('proc_id',$this->proc_id);
    $criteria->compare('book_id',$this->book_id);
    $criteria->compare('Project_name', $this->Project_name);
    $criteria->compare('isbn_no', $this->isbn_no);
    $criteria->compare('book_title',$this->book_title);
    $criteria->compare('totalpage',$this->totalpage,true);
    $criteria->compare('totaltime',$this->totaltime,true);
    return new CActiveDataProvider($this, array(
        'criteria'=>$criteria,
        'pagination'=>array(
        'pageSize'=>100
    ),
    ));
    }

for the normal particular condition its working by below condition

        $criteria->condition = "  time_up LIKE  '$this->time_up%'";

for date range its not working i tried also wiki/142/ in yii website but no use. kindly help in this.or give some other methods to darange search for timestamp.

My inputs from advanced search form

        <div class=" wide form">

   <?php $form=$this->beginWidget('CActiveForm', array(
'action'=>Yii::app()->createUrl($this->route),
'method'=>'get',
         )); ?>
    <div class="row">
    <?php echo "Time UP from"; ?>
 <?php $this->widget('zii.widgets.jui.CJuiDatePicker',
 array(
  'model'=>$model, 
'name'=>'Process[time_up_from]',
       // Model attribute filed which hold user input
      'options'=>array(
    'showAnim'=>'fold',
    'dateFormat'=>'yy-mm-dd',),
    'htmlOptions'=>array(
    'style'=>'height:20px;width:100px',
    'size'=>15,
    //'value'=>date('Y-m-d'),
    /*'onchange'=>"$.fn.yiiGridView.update('books-grid', {data: $(this).serialize()});" */),));?>
   </div>
    <?php echo "Time Up to"; ?>
   <?php $this->widget('zii.widgets.jui.CJuiDatePicker',
    array(
  'model'=>$model, 
    'name'=>'Process[time_up_to]',
       // Model attribute filed which hold user input
      'options'=>array(
    'showAnim'=>'fold',
    'dateFormat'=>'yy-mm-dd',),
    'htmlOptions'=>array(
    'style'=>'height:20px;width:100px',
    'size'=>15,
    //'value'=>date('Y-m-d'),
    /*'onchange'=>"$.fn.yiiGridView.update('books-grid', {data:      $(this).serialize()});"*/  ),));?>
           </div>
         <?php   echo CHtml::submitButton('Search'); ?>

ANSWER FOR THE PROBLEM

hi i found the answer its just a if condition before criteria condition

`if(strlen($this->time_up_from) && strlen($this->time_up_to))
    {
 $criteria->condition="time_up BETWEEN UNIX_TIMESTAMP('$this->time_up_from') AND UNIX_TIMESTAMP('$this->time_up_to')";
   }

now its working fine. @bool.dev thank you very much for your suggestions.thanks alot.

Answer 1

i have a field with number and i use .. to specify a range in the input field. In the model, i just do:

    // check for .. as a range selector
    if ( stripos( $this->AGE_IN_DAYS, '..') )
    {
        $range = explode( '..', $this->AGE_IN_DAYS );
        $criteria->compare('AGE_IN_DAYS','>='.$range[0]);
        $criteria->compare('AGE_IN_DAYS','<='.$range[1]);
    }
    else {
        $criteria->compare('AGE_IN_DAYS',$this->AGE_IN_DAYS);
    }

simple and in my optinion pretty logical for the user. He would put in 0..100 to select range 0 to 100. No additional field is required.

Answer 2

Try this:

public function search(){

   $criteria=new CDbCriteria();

   if(!empty($this->time_up_from) && !empty($this->time_up_to)){
      $criteria->condition="time_up BETWEEN UNIX_TIMESTAMP('$this->time_up_from') AND UNIX_TIMESTAMP('$this->time_up_to')";
   }
   $criteria->compare('proc_id',$this->proc_id);
   $criteria->compare('book_id',$this->book_id);
   $criteria->compare('Project_name', $this->Project_name);
   $criteria->compare('isbn_no', $this->isbn_no);
   $criteria->compare('book_title',$this->book_title);
   $criteria->compare('totalpage',$this->totalpage,true);
   $criteria->compare('totaltime',$this->totaltime,true);
   return new CActiveDataProvider($this, array(
      'criteria'=>$criteria,
      'pagination'=>array(
         'pageSize'=>100
      ),
   ));
}

Guessing that time_up_to and time_up_from are virtual attributes that you have declared in your model, take care that you have declared them properly, and also added the safe validator for them, like this:

// in your model
public $time_up_from;
public $time_up_to;

// in the rules of the model
return array(
  // other rules
  // below is the safe rule
  array('proc_id, book_id, Project_name, isbn_no, book_title, totalpage, totaltime, time_up, time_up_from, time_up_to', 'safe', 'on'=>'search'),
);

Also in your search form modify the date pickers, as i already mentioned in the comments:

 // remove the 'name'=>'Process[time_up_from]' and use the following line
 'attribute'=>'time_up_from'
 // and remove the 'name'=>'Process[time_up_to]' and use the following line
 'attribute'=>'time_up_to'

Edit :

As Dcoder pointed out in the comments below, we should always bind params, to prevent sql injection, and possibly get improved performance, hence the modified condition could be:

if(!empty($this->time_up_from) && !empty($this->time_up_to)){
  $criteria->condition="time_up BETWEEN UNIX_TIMESTAMP(:time_up_from) AND UNIX_TIMESTAMP(:time_up_to)";
  $criteria->params[':time_up_from']=$this->time_up_from;
  $criteria->parmas[':time_up_to']=$this->time_up_to;
}

From the guide