CODEWITHSUNDEEP
phpyii2
Yang
Yang

Reputation: 8701

search between two dates in yii2

A date can be represented in different formats. A table itself looks like so:

   book varchar(250) NOT NULL,  
   date INT NOT NULL

Now my problem is that I can not implement search in range between two dates. For example, there are 5 books with different dates, but the start date starts at 31/12/14 and final date is 31/02/15. So when a user selects a range between these dates, it must provide all books in that date range.

Is there any way to do it in Yii2? I could not find anything so far

UPDATE

I'm implementing a custom filter which doesn't belong to GridView and it looks like as a standalone box outside the table.

It looks like so:

<div class="custom-filter">

   Date range:
     <input name="start" />
     <input name="end" />

   Book name:
     <input name="book" />

</div>

Upvotes: 9

Views: 67953

Answers (4)

pa3py6aka
pa3py6aka

Reputation: 609

If you get start and end in date format, but date in your database table is in INT type, you must do something like this:

//Get values and format them in unix timestamp
$start = Yii::$app->formatter->asTimestamp(Yii::$app->request->post('start'));
$end = Yii::$app->formatter->asTimestamp(Yii::$app->request->post('end'));

//Book name from your example form
$bookName = Yii::$app->request->post('book');

//Then you can find in base:
$books = Book::find()
    ->where(['between', 'date', $start, $end])
    ->andWhere(['like', 'book', $bookName])
    ->all();

Not forget validate values given from post.

Upvotes: 13

Khotim
Khotim

Reputation: 91

Assuming the date stored as integer represents unix timestamp, you can make a model class and apply yii\validators\DateValidator on start and end attributes.

/**
 * Class which holds all kind of searchs on Book model.
 */
class BookSearch extends Book
{
    // Custom properties to hold data from input fields
    public $start;
    public $end;

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            ['start', 'date', 'timestampAttribute' => 'start', 'format' => 'php:d/m/y'],
            ['end', 'date', 'timestampAttribute' => 'end', 'format' => 'php:d/m/y']
        ];
    }

    public function searchByDateRange($params)
    {
        $this->load($params);

        // When validation pass, $start and $end attributes will have their values converted to unix timestamp.
        if (!$this->validate()) {
            return false;
        }

        $query = Book::find()->andFilterWhere(['between', 'date', $this->start, $this->end]);

        return true;
    }
}

See more about timestampAttribute on this documentation.

Upvotes: 6

Leye Odumuyiwa
Leye Odumuyiwa

Reputation: 674

I believe this is the answer you need:

$model = ModelName::find()
->where(['between', 'date', "2014-12-31", "2015-02-31" ])->all();

Upvotes: 48

kamlesh.bar
kamlesh.bar

Reputation: 1804

use Yii2 Active Record and access books between two dates like this.

public static function getBookBetweenDates($lower, $upper)
{
    return Book::find()
        ->where(['and', "date>=$lower", "date<=$upper"])
        ->all();
}

I assume you are using active record class and you have created Book.php (appropriate name based on table name) As model file .

Upvotes: 1

Related Questions