query age + date and day

jansky

to all guru

how can i query an age using the birthday?

table

id | name | birthday
1 Jo 1980-11-10
2 jay 1970-12-8
3 Forge 1975-8-4
4 joy 1984-9-7
5 Mike 1960-10-10
6 George 1994-7-7

here is the scenario

I would like to query only starting from September 12 (This day and month is a drop down "day" and month") onwards who has an age 15-23? (This age is a drop down "from and to")

mrhappiness

SELECT 
  id, name,
  DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(birthday)), '%y') age
FROM name_of_your_table
WHERE DATE_FORMAT(birthday, '%m%d') > 912
HAVING age BETWEEN 15 AND 23

try this

jansky

this sample works okay in a single table, but if you dont mind

how this work in multiple tables say 4 tables

table1 - profile contain the birthday
table2 - company (company where the users work)
table3- grouping per department of company
table4 - position

mrhappiness

calculating the age and restricting the result (only people being born after sep 12th) has nothing to do with joining multiple tables

you should have some columns in each table referencing columns in other tables
show them please

jansky

SELECT
members.id,
members.sex,
members.birthday,
members.sportsid,
members.clubid,
members.firstname,
members.middlename,
members.lastname,
companymembers.new_member,
companymembers.seasonid as SEASONID
FROM
companymembers
INNER JOIN members ON (companymembers.memberid=members.id)
AND (members.clubid=companymembers.clubid)
AND (members.sportsid=companymembers.sportsid)
WHERE
(companymembers.sportsid = $sportsid) AND
(companymembers.clubid = $clubid) AND
(members.actionneeded NOT LIKE 1) AND
(members.birthday BETWEEN DATE_SUB( NOW( ) , INTERVAL '".$endage."' YEAR )
AND DATE_SUB( NOW( ) , INTERVAL '".$startage."' YEAR ) ) AND
(members.sex = '".$sex."') AND
((members.firstname LIKE '%$searchstring%') OR
(members.lastname LIKE '%$searchstring%'))
AND
((companymembers.teamid = 0) OR
(companymembers.teamid = $companyselected)) AND
((companymembers.seasonid = 0) OR
(companymembers.seasonid = $seasonselected))";

here is the queries currently i have to change the birthday base from the newly rquirements that i posted.

(members.birthday BETWEEN DATE_SUB( NOW( ) , INTERVAL '".$endage."' YEAR )
AND DATE_SUB( NOW( ) , INTERVAL '".$startage."' YEAR ) )