1

In the database, I Have DOB table wich is DATE . so I have problem in search function, because I want to search people by Date to be correct age at any time. So fields in search is AGE for example 18-25 , . I want to make searchage1 = 18 in this example and searchage2 = 25. 

 `dob BETWEEN ".$search_data['searchage1']." AND ".$search_data['searchage2'];`

I want to convert this 18 and 25 into DATE then Compare it to Database DATES . I have tried some possiblity with no sucess, any idea?

YvesLeBorg
  • 9,070
  • 8
  • 35
  • 48
James Kond
  • 11
  • 4

1 Answers1

0

Firstly, you need to calculate the age based form the dob field, then use this in your WHERE statements.

Please see these threads on how to calculate age in SQL: here or here

EG:

SELECT *, DATEDIFF(dob, '2016-06-28')/365.25 AS `age` WHERE `age` >= '18' AND `age` =< '25';

or

SELECT *, YEAR(CURRENT_TIMESTAMP) - YEAR(dob) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(dob, 5)) AS `age` WHERE `age` >= '18' AND `age` =< '25';
Community
  • 1
  • 1
Jamie Bicknell
  • 2,306
  • 17
  • 35
  • (dob, '2016-06-28') What 2016-06-28 means? can I use (dob, CURRENT_TIMESTAMP) instead of it ? – James Kond Jun 28 '16 at 18:29
  • That calculates the difference between the two dates, the latter being today's date. I just took that SQL from the other answers I've posted. – Jamie Bicknell Jun 28 '16 at 18:30
  • So if we looking for difference then as it seems 2016-06-28 is TODAYS date so can we just use CURRENT_TIMESTAMP – James Kond Jun 28 '16 at 18:33
  • `CURRENT_TIMESTAMP` AKA `NOW` returns the time too, so that may throw your calculations off a bit. – Jamie Bicknell Jun 28 '16 at 18:38
  • Hey Jamie, can you please help me more clear I appreciate that. My sql connect: ' $this->db->query('(SELECT CAT.heading,MODEL.autoid,MODEL.aname,MODEL.outcall1,MODEL.gender,MODEL.online,MODEL.age,MODEL.thumbimg,username=0 FROM regio_model AS MODEL LEFT JOIN regio_categories AS CAT ON CAT.autoid=MODEL.outcall2 WHERE'.$wh.')' wh means whatever I Do for that date calculatin, I cannot change this strcuture since so much thing builded like that in file. so at this time : $wh = "DATEDIFF(dob, '2016-06-28')/365.25 AS `age` WHERE `age` >= '18' AND `age` =< '25';"; This actually taking error – James Kond Jun 28 '16 at 18:46
  • I cannot see exactly what error is cuz of Codeingiter, but I am sure the error is cuz of $wh is wrong . – James Kond Jun 28 '16 at 18:47
  • The `age` calculate needs to be in the first part of the SQL, if you see my examples. – Jamie Bicknell Jun 28 '16 at 18:48
  • I am trying but does not works, can u add ur following code into my Sql that I sent you AND PASTE IT HERE? – James Kond Jun 28 '16 at 18:58