Yii2- How to search gridview records using kartik date time picker

Moeez Published at Dev

Moeez

I am working in yii2 framework. I have two kartik date time pickers in my index view. I want to use them to search the records.

<section class="content">
<div class="box">
    <div class="box-body">

        <p>
            <?= Html::a('Update Record', ['create'], ['class' => 'btn btn-success']) ?>
        </p>
        <div class="div1" style="float: left; text-align: right; width: 25%;">

                <span style="padding-bottom: 10px; border-bottom: 1px solid black">
           <?php

           echo DateTimePicker::widget([
               'name' => 'startTime',
               'options' => ['placeholder' => 'Select Start Time ...'],
               'convertFormat' => true,
               'pluginOptions' => [
                   'format' => 'yyyy-dd-MM HH:i:ss',
                   //'startDate' => '01-Mar-2014 12:00 AM',
                   'todayHighlight' => true
               ]
           ]);
           ?>
        </span>

        </div>
        <div class="div2" style="float: left; text-align: right; width: 25%;">

                <span style="padding-bottom: 10px; border-bottom: 1px solid black">
           <?php

           echo DateTimePicker::widget([
               'name' => 'endTime',
               'options' => ['placeholder' => 'Select End Time ...'],
               'convertFormat' => true,
               'pluginOptions' => [
                   'format' => 'yyyy-dd-MM HH:i:ss',
                  // 'startDate' => '01-Mar-2014 12:00 AM',
                   'todayHighlight' => true
               ]
           ]);
           ?>
        </span>

        </div>
        <p>
            <?= Html::a('Search Record', ['index','id','name'=>'search'], ['class' => 'btn btn-info']) ?>
        </p>
        <?php Pjax::begin(); ?>
        <?= GridView::widget([
        'dataProvider' => $dataProvider,
        'filterModel' => $searchModel,
        'columns' => [
        ['class' => 'yii\grid\SerialColumn'],


        'meter_msn',
        [
            'label' => 'Meter Type',
            'value' => function ($d) {
                if(is_object($d))
                    return $d->meter_type;
                return ' - ';
            },
            'filter' => Html::activeDropDownList($searchModel, 'meter_type', \app\models\Meters::getMeterTypeValues(), ['prompt' => "Meter Type", 'class' => 'form-control']),

        ],
        'sub_div_code',
        [
            'label' => 'Sub Division Name',
            'value' => function ($d) {
                if(is_object($d->subdiv))
                    return $d->subdiv->name;
                return ' - ';
            },
            'filter' => Html::activeDropDownList($searchModel, 'sub_div_code', \common\models\SurveyHescoSubdivision::toArrayList(), ['prompt' => "Sub-Div", 'class' => 'form-control']),

        ],
        'meter_ping_date_time',

        'start_date_time',
        'end_date_time',

        ['class' => 'yii\grid\ActionColumn'],
    ],
]); ?>
<?php Pjax::end(); ?>
    </div>
</div>

GUI

Index Controller

 public function actionIndex()
{
    $value = isset($_GET['name']);
    if($value =='search')
    {

    }
    else
    {
        $searchModel = new MeterpingSearch();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        return $this->render('index', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
        ]);
    }

}

Now I want to search the records when a user selects the start and end date time and click on search button. i.e. I have to pass the date time values and then search the records. Below is my search model.

public function rules()
{
    return [
        [['id', 'meter_id'], 'integer'],
        [['meter_msn', 'sub_div_code','meter_type', 'sub_div_name'], 'safe'],
    ];
}
public function scenarios()
{
    // bypass scenarios() implementation in the parent class
    return Model::scenarios();
}
public function search($params)
{
    $query = MeterPing::find();

    // add conditions that should always apply here

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);

    $this->load($params);

    if (!$this->validate()) {
        // uncomment the following line if you do not want to return any records when validation fails
        // $query->where('0=1');
        return $dataProvider;
    }

    // grid filtering conditions
    $query->andFilterWhere([
        'id' => $this->id,
        'meter_id' => $this->meter_id,
        'meter_ping_date_time' => $this->meter_ping_date_time,
        'start_date_time' => $this->start_date_time,
        'end_date_time' => $this->end_date_time,
        'meter_type' => $this->meter_type,
    ]);

    $query->andFilterWhere(['like', 'meter_msn', $this->meter_msn])
        ->andFilterWhere(['like', 'sub_div_code', $this->sub_div_code])
        ->andFilterWhere(['like', 'sub_div_name', $this->sub_div_name]);

    return $dataProvider;
}

Update 1

As per answer given I have tried to change my code

<section class="content">
<div class="box">
    <div class="box-body">

        <p>
            <?= Html::a('Update Record', ['create'], ['class' => 'btn btn-success']) ?>
        </p>
        <?php Pjax::begin(); ?>

        <?php $form = ActiveForm::begin([
            'action' => ['index'],
            'method' => 'get',
            'options' => [
                'data-pjax' => 1
            ],
        ]); ?>
        <div class="div1" style="float: left; text-align: right; width: 25%;">

            <span style="padding-bottom: 10px; border-bottom: 1px solid black">
       <?php

       echo DateTimePicker::widget([
           'model' => $searchModel,
           'attribute'=>'start_date_time',
           'options' => ['placeholder' => 'Select Start Time ...'],
           'convertFormat' => true,
           'pluginOptions' => [
               'format' => 'yyyy-dd-MM HH:i:ss',
               // 'startDate' => '01-Mar-2014 12:00 AM',
               'todayHighlight' => true
           ]
       ]);
       ?>
    </span>

        </div>
        <div class="div2" style="float: left; text-align: right; width: 25%;">

            <span style="padding-bottom: 10px; border-bottom: 1px solid black">
       <?php

       echo DateTimePicker::widget([
           'model' => $searchModel,
           'attribute'=>'end_date_time',
           'options' => ['placeholder' => 'Select End Time ...'],
           'convertFormat' => true,
           'pluginOptions' => [
               'format' => 'yyyy-dd-MM HH:i:ss',
               //'startDate' => '01-Mar-2014 12:00 AM',
               'todayHighlight' => true
           ]
       ]);
       ?>
    </span>

        </div>
        <p>
            <?= Html::submitButton('Search', ['class' => 'btn btn-primary']) ?>
        </p>

        <?= GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],

        //'id',
        //'meter_id',
        'meter_msn',
        [
            'label' => 'Meter Type',
            'value' => function ($d) {
                if(is_object($d))
                    return $d->meter_type;
                return ' - ';
            },
            'filter' => Html::activeDropDownList($searchModel, 'meter_type', \app\models\Meters::getMeterTypeValues(), ['prompt' => "Meter Type", 'class' => 'form-control']),

        ],
        'sub_div_code',
        [
            'label' => 'Sub Division Name',
            'value' => function ($d) {
                if(is_object($d->subdiv))
                    return $d->subdiv->name;
                return ' - ';
            },
            'filter' => Html::activeDropDownList($searchModel, 'sub_div_code', \common\models\SurveyHescoSubdivision::toArrayList(), ['prompt' => "Sub-Div", 'class' => 'form-control']),

        ],
        'meter_ping_date_time',

        'start_date_time',
        'end_date_time',

        ['class' => 'yii\grid\ActionColumn'],
    ],
]); ?>
        <?php ActiveForm::end(); ?>
<?php Pjax::end(); ?>
    </div>
</div>
 </section>

Action Index

   $searchModel = new MeterpingSearch();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        return $this->render('index', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
        ]);

After clicking on button it didn't gives me the desired result.

Update 2

My updated search model is below

 /**
 * @inheritdoc
 */
public function rules()
{
    return [
        [['id', 'meter_id'], 'integer'],
        [['meter_msn', 'sub_div_code','meter_type', 'sub_div_name','start_date_time','end_date_time'], 'safe'],
    ];
}

/**
 * @inheritdoc
 */
public function scenarios()
{
    // bypass scenarios() implementation in the parent class
    return Model::scenarios();
}

/**
 * Creates data provider instance with search query applied
 *
 * @param array $params
 *
 * @return ActiveDataProvider
 */
public function search( $params ) {
    $query = MeterPing::find ();

    // add conditions that should always apply here

    $dataProvider = new ActiveDataProvider ( [
        'query' => $query ,
    ] );

    $this->load ( $params );

    if ( !$this->validate () ) {
        // uncomment the following line if you do not want to return any records when validation fails
        // $query->where('0=1');
        return $dataProvider;
    }

    // grid filtering conditions
    $query->andFilterWhere ( [
        'id' => $this->id ,
        'meter_id' => $this->meter_id ,
        'meter_ping_date_time' => $this->meter_ping_date_time ,
    ] );

    if ( $this->start_date_time !== '' ) {
        $query->andFilterWhere ( [ '>=' , 'DATE_FORMAT(start_date_time,"%Y-%m-%d %h:%i:%s")' , date ( 'Y-m-d h:i:s' , strtotime ( $this->start_date_time ) ) ] );
    }
    if ( $this->end_date_time !== '' ) {
        $query->andFilterWhere ( [ '<=' , 'DATE_FORMAT(end_date_time,"%Y-%m-%d %h:%i:%s")' , date ( 'Y-m-d h:i:s' , strtotime ( $this->end_date_time ) ) ] );
    }


    $query->andFilterWhere ( [ 'like' , 'meter_type' , $this->meter_type ] );

    return $dataProvider;
}

Date Time Pickers

 <div class="div1" style="float: left; text-align: right; width: 25%;">

            <span style="padding-bottom: 10px; border-bottom: 1px solid black">
       <?php

       echo DateTimePicker::widget([
           'model' => $searchModel,
           'attribute'=>'start_date_time',
           'options' => ['placeholder' => 'Select Start Date Time ...'],
           'convertFormat' => true,
           'pluginOptions' => [
               'format' => 'yyyy-MM-dd hh:i:ss',
               // 'startDate' => '01-Mar-2014 12:00 AM',
               'todayHighlight' => true
           ]
       ]);
       ?>
    </span>

        </div>
        <div class="div2" style="float: left; text-align: right; width: 25%;">

            <span style="padding-bottom: 10px; border-bottom: 1px solid black">
       <?php

       echo DateTimePicker::widget([
           'model' => $searchModel,
           'attribute'=>'end_date_time',
           'options' => ['placeholder' => 'Select End Date Time ...'],
           'convertFormat' => true,
           'pluginOptions' => [
               'format' => 'yyyy-MM-dd hh:i:ss',
               //'startDate' => '01-Mar-2014 12:00 AM',
               'todayHighlight' => true
           ]
       ]);
       ?>
    </span>

        </div>

I have also looked into this question, but it didn't help me out.

Any help would be highly appreciated.

Muhammad Omer Aslam

You should use the searchModel object to create the active fields, and wrap your fields with form tag, as the datepicker is also on the index action's view. Change the html to the following, i am assuming that your form is inside the pjax container <?php Pjax::begin (); ?>

<?php $form = ActiveForm::begin([
        'action' => ['index'],
        'method' => 'get',
        'options' => [
            'data-pjax' => 1
        ],
    ]); ?>
<div class="div1" style="float: left; text-align: right; width: 25%;">

                <span style="padding-bottom: 10px; border-bottom: 1px solid black">
           <?php

           echo DateTimePicker::widget([
               'model' => $searchModel,
               'attribute'=>'start_date_time',
               'options' => ['placeholder' => 'Select Start Time ...'],
               'convertFormat' => true,
               'pluginOptions' => [
                   'format' => 'yyyy-dd-MM HH:i:ss',
                  // 'startDate' => '01-Mar-2014 12:00 AM',
                   'todayHighlight' => true
               ]
           ]);
           ?>
        </span>

        </div>
        <div class="div2" style="float: left; text-align: right; width: 25%;">

                <span style="padding-bottom: 10px; border-bottom: 1px solid black">
           <?php

           echo DateTimePicker::widget([
               'model' => $searchModel,
                'attribute'=>'end_date_time',
               'options' => ['placeholder' => 'Select End Time ...'],
               'convertFormat' => true,
               'pluginOptions' => [
                   'format' => 'yyyy-dd-MM HH:i:ss',
                   //'startDate' => '01-Mar-2014 12:00 AM',
                   'todayHighlight' => true
               ]
           ]);
           ?>
        </span>

        </div>
        <p>
            <?= Html::submitButton('Search', ['class' => 'btn btn-primary']) ?>
        </p>
<?php ActiveForm::end(); ?>

Then change your action to the following

 public function actionIndex()
{

        $searchModel = new MeterpingSearch();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        return $this->render('index', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
        ]);
}

The above will search the records where the start_date_time and end_date_time matches the exact entered date & time in the form fields, if you want to match the date part only you might have to format the column and matching value in your $query->andFilterWhere([ condition inside the search model.

'DATE_FORMAT('%Y-%d-%m',start_date_time)' => date("Y-d-m",strtotime($this->start_date_time)),
'DATE_FORMAT('%Y-%d-%m',end_date_time)' => date("Y-d-m",strtotime($this->end_date_time)),

EDIT

You want the entered start and end time to fetch all the records that have the start and end time between this range if i assumed correctly like this FIDDLE is doing, if that is correct

remove

 'DATE_FORMAT('%Y-%d-%m',start_date_time)' => date("Y-d-m",strtotime($this->start_date_time)),
 'DATE_FORMAT('%Y-%d-%m',end_date_time)' => date("Y-d-m",strtotime($this->end_date_time)),

and change the search function like below

 // grid filtering conditions
    $query->andFilterWhere([
        'id' => $this->id,
        'meter_id' => $this->meter_id,
        'meter_ping_date_time' => $this->meter_ping_date_time,
        'meter_type' => $this->meter_type,
    ]);
    $query->andFilterWhere(['<=','DATE_FORMAT(start_date_time,"%Y-%m-%d")',date('Y-m-d',strtotime($this->start_date_time))]);
    $query->andFilterWhere(['>=','DATE_FORMAT(end_date_time,"%Y-%m-%d")',date('Y-m-d',strtotime($this->end_date_time))]);
    $query->andFilterWhere(['like', 'meter_msn', $this->meter_msn])
    ->andFilterWhere(['like', 'sub_div_code', $this->sub_div_code])
    ->andFilterWhere(['like', 'sub_div_name', $this->sub_div_name]);

     return $dataProvider;

`EDIT2`

update your DateTimePicker format to 'format' => 'yyyy-MM-dd hh:i:ss' , for both end and start date

then update your search function to the following

  public function search( $params ) {
        $query = MeterPing::find ();

        // add conditions that should always apply here

        $dataProvider = new ActiveDataProvider ( [
            'query' => $query ,
                ] );

        $this->load ( $params );

        if ( !$this->validate () ) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }

        // grid filtering conditions
        $query->andFilterWhere ( [
            'id' => $this->id ,
            'meter_id' => $this->meter_id ,
            'meter_ping_date_time' => $this->meter_ping_date_time ,
        ] );

        if ( $this->start_date_time !== '' && !is_null($this->start_date_time) ) {
        $query->andFilterWhere ( [ '>=' , 'DATE_FORMAT(start_date_time,"%Y-%m-%d %H:%i:%s")' , date ( 'Y-m-d H:i:s' , strtotime ( $this->start_date_time ) ) ] );
    }
    if ( $this->end_date_time !== '' && !is_null($this->end_date_time) ) {
        $query->andFilterWhere ( [ '<=' , 'DATE_FORMAT(end_date_time,"%Y-%m-%d %H:%i:%s")' , date ( 'Y-m-d H:i:s' , strtotime ( $this->end_date_time ) ) ] );
    }


        $query->andFilterWhere ( [ 'like' , 'meter_type' , $this->meter_type ] );

        return $dataProvider;
    }

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-10-28

Comments

0 comments

TOP Ranking

Article