Yii 1.1: Search method of a model for filters when using dates - unixtimestamp in database

12 followers

I ran into a problem and on irc we worked it out. Since there is a lack on information on filters in genral i thought i'd start sharing all my findings, might be usefull for other people.

One of the problems was when your users search for a date, let's say for the date of an event. For general ease i work with unix timestamps in my database. So for the start time of an event it's somewhere in a day but usually not the very beginning of the day nor the very end.

so first we have to work out the date to a time and get the start and the end of that day in timestamp format: (in my country my users work in the format day/month/year but you gotta change this to your needs)

list($day,$month,$year) = explode("/",$this->start);
$daystart= mktime(0,0,0,(int)$month,(int)$day,(int)$year);
$dayend= mktime(23,59,59,(int)$month,(int)$day,(int)$year);

Then we need to add the condition for the CDbCiteria:

$criteria->condition = ':s<=start AND start<=:e';
$criteria->params=array(':s'=>$daystart,':e'=>$dayend);

now because if you put nothing in the date search field of the filter it would result in no records found. This is because an empty date would try to find on a mktime(0,0,0,0,0,0) which results in january 1st 1970 or something, so unless you have an event that exact date (would seem most unlikely), you will have no results. Therefor we would just check if empty before doing the condition, my complete search method would be something like this( bar the other attributes that can be searched)

public function search()
    {
 
 
        $criteria=new CDbCriteria;
 
        if(!empty($this->start))
        {
            list($day,$month,$year) = explode("/",$this->start);
            $daystart= mktime(0,0,0,(int)$month,(int)$day,(int)$year);
            $dayend= mktime(23,59,59,(int)$month,(int)$day,(int)$year);
            $criteria->condition = ':s<=start AND start<=:e';
            $criteria->params=array(':s'=>$daystart,':e'=>$dayend);
        }
 
        return new CActiveDataProvider(get_class($this), array(
            'criteria'=>$criteria,
        ));
    }

Total 1 comment

#6025 report it
backloop at 2011/12/06 10:15am
Parameterized

I wrapped the code inside a parameterized method so as to be able to handle any number of date attributes in the model (For example a createtime and a lastvisittime). Here is the method:

public function criteriaAddDateCondition(&$criteria,$attributeName)
    {
        $attribute = $this->$attributeName;
        if ($attribute != '' && $attribute != null) {
            list($day,$month,$year) = explode(".",$attribute);
            $daystart= mktime(0,0,0,(int)$month,(int)$day,(int)$year);
            $dayend= mktime(23,59,59,(int)$month,(int)$day,(int)$year);
            $sAlias = ':s'.$attributeName;
            $eAlias = ':e'.$attributeName;
            $criteria->addCondition($sAlias.'<='.$attributeName.' AND '.$attributeName.'<='.$eAlias);
            $criteria->params = array_merge($criteria->params,array($sAlias=>$daystart,$eAlias=>$dayend));
        }
    }

And here are the calls for the createtime and lastvisittime date attributes from inside the model search() method:

$criteria = new CDbCriteria;
$this->criteriaAddDateCondition($criteria,'createtime');
$this->criteriaAddDateCondition($criteria,'lastvisittime');

Leave a comment

Please to leave your comment.

Write new article