Yii2 Gii write own sql to filter data

Question

I used gii in yii2 to generate a crud system. I need to write this sql to filter correct data in table.

$date_now = date('Y-m-d');

$query = "SELECT * FROM reservation_db WHERE STR_TO_DATE(date,'%d-%m-%Y') > '$date_now'";

I know this is wrong, I save date as type text in database. So, I need using STR_TO_DATE to convert from string to date and make sql only select data greater then today.

Any idea where I do this sql?

Here is my ReservationSearch Model

 <?php

 namespace app\models;

 use Yii;
 use yii\base\Model;
use yii\data\ActiveDataProvider;
use app\models\ReservationDb;

/** * ReservationSearch represents the model behind the search form about app\models\ReservationDb. */ class ReservationSearch extends ReservationDb { /** * @inheritdoc */ public function rules() { return [ [['reservation_id', 'adult', 'child', 'infat', 'phone_num'], 'integer'], [['name', 'date', 'time', 'session', 'seat_area', 'remark', 'payment_status', 'reservation_date'], '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)
{
    $date_now = date('Y-m-d');

    $query = ReservationDb::find();
    $query->andFilterCompare( 'STR_TO_DATE(date)', '$date_now', '>');





    $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([
        'reservation_id' => $this->reservation_id,
        'adult' => $this->adult,
        'child' => $this->child,
        'infat' => $this->infat,
        'phone_num' => $this->phone_num,
    ]);

    $query->andFilterWhere(['like', 'name', $this->name])
        ->andFilterWhere(['like', 'date', $this->date])
        ->andFilterWhere(['like', 'time', $this->time])
        ->andFilterWhere(['like', 'session', $this->session])
        ->andFilterWhere(['like', 'seat_area', $this->seat_area])
        ->andFilterWhere(['like', 'remark', $this->remark])
        ->andFilterWhere(['like', 'payment_status', $this->payment_status])
        ->andFilterWhere(['like', 'reservation_date', $this-
      >reservation_date]);

    return $dataProvider;
}

}

yes, I tried a few way in ReservationSearch. like $query->andFilterCompare('STR_TO_DATE(date)', '>$date_now'); — Chee Seng Teo
– Chee Seng Teo, Commented Jun 29, 2017 at 4:28
or ` $query = (new \yii\db\Query()) ->select('*') ->from('reservation_db') ->where(['reservation_id' => '457']) ->all(); */` — Chee Seng Teo
– Chee Seng Teo, Commented Jun 29, 2017 at 4:28
even I only try to select ID its return me this error Call to a member function andFilterWhere() on a non-object — Chee Seng Teo
– Chee Seng Teo, Commented Jun 29, 2017 at 4:31
Try $query->andFilterCompare([ 'STR_TO_DATE(date)', $date_now, '>' ]) — Insane Skull
– Insane Skull, Commented Jun 29, 2017 at 4:46

Insane Skull · Accepted Answer · 2017-06-29 08:15:07Z

0

Try this :

public function search($params)
{
   $date_now = date('Y-m-d');

   $query = ReservationDb::find();
   $query->andFilterCompare('STR_TO_DATE(date)', $date_now, '>');
   .
   .
   .
}

answered Jun 29, 2017 at 8:15

Insane Skull

9,3619 gold badges49 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chee Seng Teo Over a year ago

Hi @Insane Skull here is the error

Incorrect parameter count in the call to native function 'STR_TO_DATE' The SQL being executed was: SELECT COUNT(*) FROM reservation_db WHERE STR_TO_DATE(date) > '2017-06-29'

Chee Seng Teo Over a year ago

I think STR_TO_DATE is not working for select count(*) ?

Collectives™ on Stack Overflow

Yii2 Gii write own sql to filter data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related