Category: Models & ORM

Q: How to find users between two ages?

I want to find all users between two ages, could someone help me?

$age_from = $this->request->query['age_from'];
$age_to   = $this->request->query['age_to'];

//transform to date format?
$current_year = date('Y', strtotime(date('Y-m-d')));
$current_month = date('m', strtotime(date('Y-m-d')));
$current_day = date('d', strtotime(date('Y-m-d')));
$date_from = ($current_year - $age_from) . '-' . $current_month . '-' . $current_day;
$date_to   = // ?

$users = TableRegistry::get('Users')->find('all', array('fields' => array('Users.id'), 'conditions' => array(
  'Users.bday >= ' => $date_from,
  'Users.bday <= ' => $date_to
), 'order' => array('Users.id DESC')))->toArray();

More specified, I do not have "age" column in Users table. 


Thank you all!

SOLVED age tablerigistry
2 Answers
user
Kristi

137

1. If Users.bday format 'Y-m-d':

Use mySQL CURDATE() function:

$age_from = $this->request->query['age_from'];
$age_to   = $this->request->query['age_to'];

$users = TableRegistry::get('Users')->find('all', array('fields' => array('Users.id'), 'conditions' => array(
  'Users.bday >= (CURDATE() - INTERVAL $age_from YEAR)',
  'Users.bday <= (CURDATE() - INTERVAL $age_to YEAR)'
), 'order' => array('Users.id DESC')))->toArray();

2. or 'Y-m-d H:i:s':

$age_from = $this->request->query['age_from'];
$age_to   = $this->request->query['age_to'];

$users = TableRegistry::get('Users')->find('all', array('fields' => array('Users.id'), 'conditions' => array(
  'Users.bday >= DATE_SUB(NOW(), INTERVAL $age_from YEAR)',
  'Users.bday <= DATE_SUB(NOW(), INTERVAL $age_to YEAR) '
), 'order' => array('Users.id DESC')))->toArray();
Accepted by Kristi

Try this:

$age_from = (empty($this->request->query['age_from']) ? null : $this->request->query['age_from']);
$age_to   = (empty($this->request->query['age_to']) ? null : $this->request->query['age_to']);

if (!empty($age_from) && !empty($age_to) {
  $users = TableRegistry::get('Users')->find('all', array('fields' => array('Users.id'), 'conditions' => array(
    'FLOOR(DATEDIFF (NOW(), Users.bday)/365) BETWEEN ' . $age_from . ' AND ' . $age_to
  ), 'order' => array('Users.id DESC')))->toArray();
}

Created: 13 Sep '16

Last Reply: 19 Sep '16

Replies: 2

Views: 684

Votes: 2

Welcome to Aero Coding!

Aero Coding is a CakePHP-focused Q&A community for professional and enthusiast cake bakers. It's built and run by you as part of the community.


Join Now Tour

Download Cakephp

Start baking your own CakePHP application!


Cakephp All Versions