Category: Models & ORM
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 tablerigistry1. 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();
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: 1004
Votes: 2