how to create search field in gridview without using search model

Question

I have created sqlDataProvider in my controller and its working. My problem is i dont know how to provide search field since im not using the search model.

this is code in my controller

public function actionAnalisa()
{
    $sql =  "SELECT 
            tbl_permohonan.permohonan_id As permohonan_id, 
            user.id As id, 
            tbl_moderator.bm_id As bm_id, 
            tbl_bhgnmod.unit_kampuscawangan As unit_kampuscawangan, 
            tbl_bahagian.bahagian_nama As bahagian_nama, 
            tbl_unit.unit_nama As unit_nama

            FROM tbl_permohonan

            INNER JOIN user 
            ON tbl_permohonan.user_id=user.id

            INNER JOIN tbl_moderator 
            ON user.id=tbl_moderator.user_id

            INNER JOIN tbl_bhgnmod 
            ON tbl_moderator.bm_id=tbl_bhgnmod.bm_id

            INNER JOIN tbl_bahagian
            ON tbl_bhgnmod.bahagian_id=tbl_bahagian.bahagian_id

            INNER JOIN tbl_unit 
            ON tbl_bhgnmod.unit_id=tbl_unit.unit_id";

    $dataProvider = new SqlDataProvider([
                'sql' => $sql,
                ]);

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

this is in my view

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

               'permohonan_id',
               'id',
               'bm_id',
               'unit_kampuscawangan',
               'bahagian_nama',
               'unit_nama',
               //~ ['class' => 'yii\grid\ActionColumn'],
           ],
       ]); ?>

Answer 1

I would advise against using raw SQL, especially when looking to provide search fields, since this provides a potential vulnerability within the application and opens it up to possibility of SQL injection attacks. Instead I would encourage the use of Yiis query builder or DAO, as is outlined here.

With that said, I have rewritten your code using Yiis inbuilt query builder.

public function actionAnalisa()
{
    // store any $_GET parameters passed for filtering via GridView
    $params = Yii::$app->request->queryParams;

    // use query builder instead of raw SQL to avoid SQL injection attacks
    $query = (new Query())
        ->select([
            'permohonan_id' => 'tbl_permohonan.permohonan_id',
            'id' => 'user.id',
            'bm_id' => 'tbl_moderator.bm_id',
            'unit_kampuscawangan' => 'tbl_bhgnmod.unit_kampuscawangan',
            'bahagian_nama' => 'tbl_bahagian.bahagian_nama',
            'unit_nama' => 'tbl_unit.unit_nama'
        ])
        ->from('tbl_permohonan')
        ->join('INNER JOIN', 'user', 'tbl_permohonan.user_id=user.id')
        ->join('INNER JOIN', 'tbl_moderator', 'user.id=tbl_moderator.user_id')
        ->join('INNER JOIN', 'tbl_bhgnmod', 'tbl_moderator.bm_id=tbl_bhgnmod.bm_id')
        ->join('INNER JOIN', 'tbl_bahagian', 'tbl_bhgnmod.bahagian_id=tbl_bahagian.bahagian_id')
        ->join('INNER JOIN', 'tbl_unit', 'tbl_bhgnmod.unit_id=tbl_unit.unit_id');

    // Adds additional WHERE conditions to the existing query but ignores empty operands
    $query->andFilterWhere(['like', 'tbl_permohonan.permohonan_id', $params['pid']])
          ->andFilterWhere(['like', 'user.id', $params['id']])
          ->andFilterWhere(['like', 'tbl_moderator.bm_id', $params['bm_id']])
          ->andFilterWhere(['like', 'tbl_bhgnmod.unit_kampuscawangan', $params['unitk']])
          ->andFilterWhere(['like', 'tbl_bahagian.bahagian_nama', $params['banama']])
          ->andFilterWhere(['like', 'tbl_unit.unit_nama', $params['unnama']]);

    // an ActiveDataProvider will accept a Query object instead of raw SQL
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);

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

Bare in mind, you will have to add the following to the top of the controller file.

use yii\data\ActiveDataProvider;
use yii\db\Query;

In order to render search fields within the GridView of your view file, you have to specify a filterModel, as is explained here.

Columns within the GridView allow you to specify a filter attribute, which will render a filter cell at the top of the column using HTML specified by the attribute. These filters are automatically submitted via GET to the same page, which is why they have been handled in the controller using the names specified within this GridView.

<?
use yii\helpers\Html;
use yii\grid\GridView;
?>

<?= GridView::widget([
    'dataProvider' => $dataProvider,
    // filterModel must be set to render filter cells within GridView
    'filterModel' => true,
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],
        [
            // specify attribute to display
            'attribute' => 'permohonan_id',
            // filter attribute accepts HTML to render
            // in this case an input field of type string, with a name of 'pid'
            'filter' => Html::input('string', 'pid')
        ],
        [
            'attribute' => 'id',
            'filter' => Html::input('string', 'id')
        ],
        [
            'attribute' => 'bm_id',
            'filter' => Html::input('string', 'bmid')
        ],
        [
            'attribute' => 'unit_kampuscawangan',
            'filter' => Html::input('string', 'unitk')
        ],
        [
            'attribute' => 'bahagian_nama',
            'filter' => Html::input('string', 'banama')
        ],
        [
            'attribute' => 'unit_nama',
            'filter' => Html::input('string', 'unnama')
        ],
    ],
]); ?>