[SOLVED] how to query age.. between 18y/o and 22y/o

[DanDaBeginner]

what I did is to get the $fromAge and $toAge, I did this with PHP..compute the year then insert to query..ex.

 

$tAge = date("Y") - 18; //1989

$fAge = date("Y") - 22; //1985

 

$query = "

SELECT *  from dating_profile

WHERE EXTRACT(YEAR FROM birthdate)

BETWEEN $fAge and $tAge "

and it works fine.. im just wondering how to do it in mysql query instead of computing the $tAge and $fAge..

if I will do this in mysql query will it be faster than computing 1st the  $tAge and $fAge then query?

 

thanx

Dan

[bubblegum.anarchy]

I am actually not sure what you are asking but this should calculate the age based on a birthdate

SELECT round(datediff(CURRENT_DATE, birthdate) / 365) AS age FROM dating_profile;

[DanDaBeginner]

thanx bubblegum.anarchy!

 

I actually want to get the row, age from 18 to 22...

[bubblegum.anarchy]

oh ok - that is the part I misunderstood.. so you will still need WHERE round(datediff(CURRENT_DATE, birthdate) / 365) BETWEEN $fAge AND $tAge

[DanDaBeginner]

thanx again bro, youve been a great help to me..would you think it is slower than the one I have? because it seems to me that it is slower than the one I have because it will still need to do some arithmetic before processing the search.. any more suggestion..?

[bubblegum.anarchy]

I suppose extract(year from birthdate) would be faster than round(datediff(CURRENT_DATE, birthdate) / 365) and if the following is correct than extracting the date is substantially quicker:

DROP TABLE IF EXISTS birthdate;

CREATE TABLE birthdate (
id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
birthdate DATE NOT NULL 
)TYPE=MYISAM;

INSERT INTO birthdate SET birthdate = '1974-12-03';
INSERT INTO birthdate (birthdate) SELECT adddate(birthdate, 1) FROM birthdate; # repeated a number of times

SELECT count(*) FROM birthdate; # records created 2097152

SELECT count(round(datediff(CURRENT_DATE, birthdate) / 365)) AS test FROM birthdate; # average around 922 ms

SELECT count(extract(year from birthdate)) AS test FROM birthdate; # average around 328 ms

[DanDaBeginner]

cool, thanx, so I will stick to my current code...