other filter not work combine Date range Filter

Yii 1.1 General Discussion
1

The first problem I only input from_date: 01-2015-01-01 and to_date: 05-02-2015, the result is the data from only 01 to 05 is appears. but if all parameter filled all (user,location,from_date, to_date) the seacrh result data with to_date(date 05-02-2015) is not appear, only date:01 until 04 appear.

The second problem if I input from_date and to_date same date (to search for one period) with parameter user_id and location filled all or not filled, no results found.

below model code :


{

                // @todo Please modify the following code to remove attributes that should not be searched.


                $criteria=new CDbCriteria;

                if(!empty($this->from_date) && empty($this->to_date)){

                        $criteria->condition="create_date >= '$this->from_date'";

                }elseif(!empty($this->to_date) && empty($this->from_date)){

                        $criteria->condition="create_date <= '$this->to_date'";

                }elseif(!empty($this->to_date) && !empty($this->from_date)){

                        $criteria->condition="create_date >= '$this->from_date' and create_date<= '$this->to_date'";

                }

                //

                $criteria->compare('user_id',$this->user_id);

                $criteria->compare('location',$this->lokasi,

                /* $criteria->mergeWith('create_date',$this->create_date

                ); */

                return new CActiveDataProvider($this, array(

                        'criteria'=>$criteria,

                        'sort'=>array(

                        'defaultOrder'=>array('create_date DESC',)),

                                'pagination'=>array(

                                                'PageSize'=>20

                                ),

                ));

        }

please help, how is the correct code for search with date range filter ?

2

If you added condition based on create_date why is there second




$criteria->compare('create_date',$this->create_date,true);
3

yes it was a mistake and I have to delete, sir. i’ve try again and the result same

4

What is the format of db column for the create_date?

If this is MySQL datetime or timestamp the format is ‘YYYY-MM-DD HH:MM:SS’ and date is ‘YYYY-MM-DD’. Based on your first post you have got MM-DD-YYYY (or DD-MM-YYYY) there.

If your field is plain text you need to do some additional processing to make it comparable this way but if you store date as one of these column types make sure you compare it with properly formatted date.

5

format db is YYYY-MM-DD HH:MM:SS, my cjuidatepicker has format ‘options’=>array(‘dateFormat’=>‘yy-mm-dd’,). in my first post is just an example, sorry if confusing.

6

And what is the type of the db column?

7

timestamp, sir.

problem was resolved with this code


if(!empty($this->from_date) && empty($this->to_date)){

            $criteria->condition="create_date >= '$this->from_date'";

			}elseif(!empty($this->to_date) && empty($this->from_date)){

			$to_date= new DateTime($this->to_date);

			$to_date->modify('+1 day');

			$to_date=$to_date->format('Y-m-d');

			$criteria->condition="create_date <= '$to_date'";

			}elseif(!empty($this->to_date) && !empty($this->from_date)){

			$to_date= new DateTime($this->to_date);

			$to_date->modify('+1 day');

			$to_date=$to_date->format('Y-m-d');

            $criteria->condition="create_date >= '$this->from_date' and create_date<= '$to_date'";}

thank you, sir.