Convert year-search to age-search

I trying to create a between year search of people, I got it to work(se code below) but I want the user to be able to specify the age instead of the birthyear in the searchfield.

So I need to calculate "current date - age" from the searchfields and then insert it in the between-syntax somehow.

This is my code at the moment.

Model:

public $date_first;

public $date_last;


public function rules()

	{

		return array(

			array('..., date_first, date_last', 'safe', 'on'=>'search'),

		);

	}


public function search()

	{

		$criteria=new CDbCriteria;

		

		$criteria->with = 'people';

		

		$criteria->compare('...');

        

        if((isset($this->date_first) && trim($this->date_first) != "") && (isset($this->date_last) && trim($this->date_last) != ""))

                        $criteria->addBetweenCondition('people.date_of_birth', ''.$this->date_first.'', ''.$this->date_last.'');

        

	}

And this is the _searchview:

<div class="row">

	<?php echo $form->label($model,'From age'); ?>

	<?php echo $form->textField($model,'date_first'); ?>

</div>

<div class="row">

	<?php echo $form->label($model,'To age'); ?>

	<?php echo $form->textField($model,'date_last'); ?>

</div>

Any Ideas?

this is how you can inject custom filter conditions:

$criteria->addCondition('(now() - people.date_of_birth) BETWEEN :age1 AND :age2');

$criteria->params[':age1'] = $age_first;

$criteria->params[':age2'] = $age_last;

the above is just example. Use your expression to calculate age (proper to your database engine) and proper values for params.

Couldn’t get it to work so I tried something more logic to me.

Still doesn’t work tho, but I can’t figure out why. It doesn’t provide an error, but I don’t get any searchresults.

if((isset($this->age_first) && trim($this->age_first) != "") && (isset($this->age_last) && trim($this->age_last) != ""))

                        $criteria->addCondition('people.date_of_birth BETWEEN :age1 AND :age2');

						$criteria->params[':age1'] = 2012 - $this->age_first;

						$criteria->params[':age2'] = 2012 - $this->age_last;

I guess it’s the math that’s causing the problem, maybe that can be made different?

Or is it better to stick with your way?

you are camparing database date field with integer (eg. people.date_of_birth BETWEEN 11 AND 12)… I do not know what database you are using but most I know won’t work as you might expect. You have to calculate age from date_of_birth or compare dates.

okey, the database field is of the type datetime.

if I look back at your code

$criteria->addCondition('(now() - people.date_of_birth) BETWEEN :age1 AND :age2');

$criteria->params[':age1'] = $age_first;

$criteria->params[':age2'] = $age_last;

is now() a php function?

This is a value taken from the database:

Aug 30 1988 12:00:00:000AM

if I hardcode the values in it will look something like this.

$criteria->addCondition('2012... - people.date_of_birth) BETWEEN 15 AND 22');

Do I need to format the datetime in some way?

Thanks

"now()" is a MySQL function returning current timestamp. I did wrote that this is only an example and you should use expression proper to your db engine. The example was only to show how (and where) put expression that calculates age for every person. In fact for MySQL you should use expression like this:

$criteria->addCondition(‘TIMESTAMPDIFF(YEAR,people.date_of_birth,NOW()) BETWEEN 15 AND 22’);