How to filter CGridView with From Date and To Date datepicker Follow @yiiframework

This artical is related to allow user to filter CGridView with date range using datepicker on submit button. Here I use Yii Cookie to preserve date in datepicker textbox after submitting the form.

Step 1 : Lets insert below code in your view part where required I create one form for submit the dates ( from_date & to_date )

View ¶

<?php $form=$this->beginWidget('CActiveForm', array(
    'id'=>'page-form',
    'enableAjaxValidation'=>true,
)); ?>
 
<b>From :</b>
<?php
$this->widget('zii.widgets.jui.CJuiDatePicker', array(
    'name'=>'from_date',  // name of post parameter
    'value'=>Yii::app()->request->cookies['from_date']->value,  // value comes from cookie after submittion
     'options'=>array(
        'showAnim'=>'fold',
        'dateFormat'=>'yy-mm-dd',
    ),
    'htmlOptions'=>array(
        'style'=>'height:20px;'
    ),
));
?>
<b>To :</b>
<?php
$this->widget('zii.widgets.jui.CJuiDatePicker', array(
    'name'=>'to_date',
    'value'=>Yii::app()->request->cookies['to_date']->value,
     'options'=>array(
        'showAnim'=>'fold',
        'dateFormat'=>'yy-mm-dd',
 
    ),
    'htmlOptions'=>array(
        'style'=>'height:20px;'
    ),
));
?>
<?php echo CHtml::submitButton('Go'); ?> // submit button
<?php $this->endWidget(); ?>
 
// BELOW THIS YOUR CGridView code...

Insert below code in controller

Controller ¶

unset(Yii::app()->request->cookies['from_date']);  // first unset cookie for dates
unset(Yii::app()->request->cookies['to_date']);
 
$model=new XyzModel('search');  // your model
$model->unsetAttributes();  // clear any default values
 
  if(!empty($_POST))
  {
    Yii::app()->request->cookies['from_date'] = new CHttpCookie('from_date', $_POST['from_date']);  // define cookie for from_date
    Yii::app()->request->cookies['to_date'] = new CHttpCookie('to_date', $_POST['to_date']);
    $model->from_date = $_POST['from_date'];
    $model->to_date = $_POST['to_date'];
}

Now code for model part

Add two public variable in your model class

Model ¶

public $from_date;
public $to_date;

now mark them as safe attribute

... ...  from_date, to_date', 'safe', 'on'=>'search'),

now just put below code in your search function in model class,

if(!empty($this->from_date) && empty($this->to_date))
        {
            $criteria->condition = "date >= '$this->from_date'";  // date is database date column field
        }elseif(!empty($this->to_date) && empty($this->from_date))
        {
            $criteria->condition = "date <= '$this->to_date'";
        }elseif(!empty($this->to_date) && !empty($this->from_date))
        {
            $criteria->condition = "date  >= '$this->from_date' and date <= '$this->to_date'";
        }

Demo Image ¶

and this is it as you can filter from date range ... .. .

Total 10 comments

#12751

nozomi at 2013/04/09 10:36pm

making better use of the cookies

I don't think cookies are needed in this specific example. If you unset the cookies each time the controller is called then you might as well just pass the post data directly into the model and widget value.

Cookies would be very useful if you'd like to keep the filter view when users update or delete information from the gridview as the post data isn't preserved when the page is reloaded in those instances. For that though we'd need to fine tune when the cookies get changed and retrieved.

//controller
if(isset($_POST['from_date'])) {
    Yii::app()->request->cookies['from_date'] = new CHttpCookie('from_date', $_POST['from_date']);
    $model->from_date=$_POST['from_date'];
} else {
    if(isset(Yii::app()->request->cookies['from_date'])) $model->from_date=Yii::app()->request->cookies['from_date'];
}
 
//view
$this->widget('zii.widgets.jui.CJuiDatePicker', array(
    'name'=>'from_date',
    'value'=>isset(Yii::app()->request->cookies['from_date']) ? Yii::app()->request->cookies['from_date'] : '',

#11956

ram87 at 2013/02/15 05:23am

How to filter CGridView with From Date and To Date datepicker

It was Very nice Tutorial..

#11700

SomethingWicked at 2013/01/28 06:56am

@kiran

Did you find a workaround to use both the set of search (head of the table and date filtering) at the same time? :)

#10476

dyooolicious at 2012/10/30 09:57pm

Great article !

this is exactly what I need :) thanks

#10469

kiran sharma at 2012/10/30 10:35am

@sirin k

can't say about error until debug it, but might be problem with fetch colum(e.g column not preset or anything else..)

#10468

sirin k at 2012/10/30 09:46am

im getting an error

CDbCommand::fetchColumn() failed: SQLSTATE[23000]: Integrity constraint
violation: 1052 Column 'date_add' in where clause is ambiguous. The SQL
statement executed was: SELECT COUNT(DISTINCT `t`.`id_user_gift`) FROM
`lag_user_order` `t`  LEFT OUTER JOIN `lag_user` `sender` ON
(`t`.`id_user`=`sender`.`id_user`)  WHERE (date_add  >= '2012-10-08' and
date_add <= '2012-10-27').

#9153

kiran sharma at 2012/07/24 08:35am

@SomethingWicked

yes there comes the limitation, You can either filter by this date filters or by CGridView default filter. but will thought about it if possible.

#9151

SomethingWicked at 2012/07/24 08:22am

Merge datepicker form and and search form

I'm having an issue: when filtering by datepicker I can't preserve the autogenerated filtering if any field has value in it (for instance your Customer Name), and also in the opposite scenario.

It seems that I can't use at the same time both the datepicker form and the general filter form in the table.

Am I wrong?

#9144

kiran sharma at 2012/07/24 04:57am

@SomethingWicked

Yes it is... this is my experience that it depend on server, when you use localhost or normal server you can use directly, but when you use sensitive server it shows error.

so better to use isset or !empty.

#9143

SomethingWicked at 2012/07/24 04:50am

values before first send

Thanks for this wiki :).

Just a little addendum: remember (as it is specified in the Yii Cookie you mentioned at the top of your guide) to use the conditional isset to prevent error before the drop of the cookie.

$this->widget('zii.widgets.jui.CJuiDatePicker', array(
    'name'=>'from_date',  // name of post parameter
'value'=>(isset(Yii::app()->request->cookies['from_date'])) ? Yii::app()->request->cookies['from_date']->value : '',
...
));

Please login to leave your comment.