ted_chou12 Posted January 10, 2007 Share Posted January 10, 2007 I stored users' dob all in one single column, but when I want to perform a search, the more ideal situation would be searching their age range instead of date of birth. I do know how to do a search with "%" sign which you could do if you stored the data as age, so range such as 10~19 would be something like "1%" but when storing data as birthdates, the situation is harder, can anyone suggest me how could i do it.btw, this is the function to calculate age through dob:[code]function birthday($birthday) {list($year,$month,$day) = explode("-",$birthday); $year_diff = date("Y") - $year; $month_diff = date("m") - $month; $day_diff = date("d") - $day; if ($month_diff < 0) $year_diff--; elseif (($month_diff==0) && ($day_diff < 0)) $year_diff--; return $year_diff;}[/code]ThanksTed Quote Link to comment Share on other sites More sharing options...
ted_chou12 Posted January 10, 2007 Author Share Posted January 10, 2007 actually, I remembered, you can use the max() and min() for mysql search, so what I only need to do is to convert the age into dob, and plug in the two to give a range, would this work? if yes, can anyone tell me how you can convert age to dob?Thanks Quote Link to comment Share on other sites More sharing options...
genericnumber1 Posted January 10, 2007 Share Posted January 10, 2007 you know you can query a range of dates... something likeSELECT * FROM Users WHERE dob BETWEEN '1/1/2006' AND '1/1/2007'EDIT: oh, your question on age to DOB? okay.. gimme a sec I'll see what I can figure out... Quote Link to comment Share on other sites More sharing options...
ted_chou12 Posted January 10, 2007 Author Share Posted January 10, 2007 really? I dont find those in the tutorial, thats why I am having some trouble with this...ThanksTed Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted January 10, 2007 Share Posted January 10, 2007 [quote author=genericnumber1 link=topic=121759.msg501186#msg501186 date=1168423589]you know you can query a range of dates... something likeSELECT * FROM Users WHERE dob BETWEEN '1/1/2006' AND '1/1/2007'[/quote]Can you use BETWEEN in MySQL? I didn't realise you could? I know it's available in OracleRegardsHuggie Quote Link to comment Share on other sites More sharing options...
genericnumber1 Posted January 10, 2007 Share Posted January 10, 2007 I knew you could do it in SQL though I've really not tried it in MySQL.. why, you figure you can't? Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted January 10, 2007 Share Posted January 10, 2007 I'm pretty certain you can't...On a DATETIME type filed you could use [color=blue]WHERE column_name > '$min' AND column_name < '$max'[/color]Huggie Quote Link to comment Share on other sites More sharing options...
genericnumber1 Posted January 10, 2007 Share Posted January 10, 2007 doesn't look like it's in the mysql handbook, back to square one ted Quote Link to comment Share on other sites More sharing options...
ted_chou12 Posted January 10, 2007 Author Share Posted January 10, 2007 okay, i think converting age to dob would work, i see a lot of dob to age in online tutorials, but not ANY for age to dob, I dont see why it is not useful...Can you suggest me how you could calculate in the oppisite direction?ThanksTed Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted January 10, 2007 Share Posted January 10, 2007 What format is your dob column in your database?RegatdsHuggie Quote Link to comment Share on other sites More sharing options...
ted_chou12 Posted January 10, 2007 Author Share Posted January 10, 2007 is simply stored as DATE for type so the format is just like the one above which is YYYY-MM-DD Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted January 10, 2007 Share Posted January 10, 2007 OK, I'll see if I can come up with a function, give me an hour...Huggie Quote Link to comment Share on other sites More sharing options...
ted_chou12 Posted January 10, 2007 Author Share Posted January 10, 2007 thanks :D Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.