Use DOB from mysql DB to determine child/adult

Question

This is my query to search the data base for candidates who meet a certain criteria. I am using php, mysql, html

$Odata = mysql_query("SELECT * FROM p_candidate WHERE(`gender` LIKE '%".$Gender."%')
     AND (`verbal`  LIKE '%".$Verbal."%') AND(`waiver`  LIKE '%".$Waiver."%')
     AND(`waiver_type`  LIKE '%".$W_Type."%') AND(`sel_staff`  LIKE '%".$A_Staff."%')
     AND(`sel_peers` LIKE '%".$A_Peers."%')AND(`verbal`  LIKE '%".$Awake."%')
     AND(`ambulatory`  LIKE '%".$Ambulatory."%') AND(`function`  LIKE '%".$Function."%')"
) or die(mysql_error());

There is another criteria I want to add - Adult/Child. I have date of birth as a column in the DB. If the candidate is above 18, would fall under Adult, otherwise Child.

The user may want to search for an adult with all the contents in $Odata. How can I do this?

looking through Calculate Age in MySQL (InnoDb) and Search age range in mysql, php I understand it can be done independently, but how can I incorporate it into my above query. Is that possible?

off your question topic, but you might like to consider changing to mysqli /PDO functions because the mysql_* ones are depreciated. Would also be worth reading about prepared statements and sql injections. — Ian Kenney, Jul 02 '13 at 21:24
TIMESTAMPDIFF(YEAR,date_of_birth,CURDATE()) will give current age if `date_of_birth` is a `timestamp` field. — , Jul 02 '13 at 21:37

Ian Kenney · Accepted Answer · 2013-07-03T06:34:28.970

0

This might help

sample data

create table dob
( 
    dob datetime
  ) 
;
insert into dob select '2001-10-08' ;
insert into dob select '1976-11-28' ;

Figure out child or adult:

select 
 dob, 
 case when dob  > Current_date() - INTERVAL 18 YEAR then 'child' else 'adult' end
from dob

see http://sqlfiddle.com/#!2/2df6d/12

edited Jul 03 '13 at 06:34

answered Jul 02 '13 at 21:40

Ian Kenney

6,376
1
25
44

1

I don't know how mySQL handles stuff like this, but you'd probably be better off using `dob > Current_date() - INTERVAL 18 YEAR`, as the optimizer then has a (more easily recognizable) chance at performing the date math _once_, as opposed to for every row. – Clockwork-Muse Jul 02 '13 at 22:32
Thanks @Ian Kenny, I think that is what I will do! – user2510479 Jul 03 '13 at 13:48

Alejandro Colorado · Answer 2 · 2013-07-02T23:18:38.033

0

You can find a correct answer in the official MySQL documentation:

SELECT CASE WHEN TIMESTAMPDIFF(YEAR,dob,CURDATE()) >=18 THEN 'adult' ELSE 'child' END
FROM p_candidate;

edited Jul 02 '13 at 23:18

answered Jul 02 '13 at 23:12

Alejandro Colorado

6,034
2
28
39

Use DOB from mysql DB to determine child/adult

2 Answers2