CODEWITHSUNDEEP
phpmysqlselect
Rick Weller
Rick Weller

Reputation: 1258

Select all users who are within a certain age range

I have users in a MySQL database, where their birthdays are stored in a DATE format (yyyy-mm-dd). I want to select users in the database with PHP who are between a certain age range.

What I have is a minimum age(18) and a maximum age(21). I know I can do something with BETWEEN, but the problem is that I only know the years and not the dates.

Does anyone have a suggestion on how I can do this?

This is what I'm doing currently:

function leeftijden($age) { 
    $morgen['day']   = date('d'); 
    $morgen['month'] = date('m'); 
    $morgen['year']  = date('Y') - $age;

    $datum = $morgen['year'] .'-' .$morgen['month'].'-' .$morgen['day'];

    return $datum;
}

Then I do this:

$maxDatum = leeftijden(18);
$minDatum = leeftijden(32);
$sqlRijder = "SELECT * FROM rijder WHERE geboortedatum between '".$minDatum."' AND '".$maxDatum."'";

But this doesn't work 100%.

How can I only select users who are between 18 and 21 years of age?

Upvotes: 5

Views: 2756

Answers (4)

haynar
haynar

Reputation: 6030

try this

SELECT * FROM rijder WHERE DATEDIFF(NOW(), geboortedatum)/365.25 BETWEEN 18 AND 21

Upvotes: 0

Zane Bien
Zane Bien

Reputation: 23125

You can simply do it right in the query:

SELECT * 
FROM rijder 
WHERE geboortedatum BETWEEN 
    CURDATE() - INTERVAL 21 YEAR AND 
    CURDATE() - INTERVAL 18 YEAR

This way, you do not need a special PHP function to construct a DATE string. Simply pass in the numbers and MySQL will make the comparisons for you. Just make sure the higher number comes first in the BETWEEN.

Upvotes: 14

Sashi Kant
Sashi Kant

Reputation: 13465

Try this :::

Select * from table where (DATEDIFF(dob, DATE(NOW())) / 365.25) between 18 and 21

Upvotes: 4

juergen d
juergen d

Reputation: 204784

SELECT * FROM rijder 
WHERE geboortedatum 
    between date_add(curdate(), interval -18 year) 
    and date_add(curdate(), interval -21 year)

Upvotes: 2

Related Questions