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 Link to comment https://forums.phpfreaks.com/topic/33572-mysql-search/ 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 Link to comment https://forums.phpfreaks.com/topic/33572-mysql-search/#findComment-157204 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... Link to comment https://forums.phpfreaks.com/topic/33572-mysql-search/#findComment-157207 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 Link to comment https://forums.phpfreaks.com/topic/33572-mysql-search/#findComment-157208 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 Link to comment https://forums.phpfreaks.com/topic/33572-mysql-search/#findComment-157209 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? Link to comment https://forums.phpfreaks.com/topic/33572-mysql-search/#findComment-157210 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 Link to comment https://forums.phpfreaks.com/topic/33572-mysql-search/#findComment-157211 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 Link to comment https://forums.phpfreaks.com/topic/33572-mysql-search/#findComment-157212 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 Link to comment https://forums.phpfreaks.com/topic/33572-mysql-search/#findComment-157213 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 Link to comment https://forums.phpfreaks.com/topic/33572-mysql-search/#findComment-157215 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 Link to comment https://forums.phpfreaks.com/topic/33572-mysql-search/#findComment-157216 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 Link to comment https://forums.phpfreaks.com/topic/33572-mysql-search/#findComment-157221 Share on other sites More sharing options...
ted_chou12 Posted January 10, 2007 Author Share Posted January 10, 2007 thanks :D Link to comment https://forums.phpfreaks.com/topic/33572-mysql-search/#findComment-157223 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.