mysql search

[ted_chou12]

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]
Thanks
Ted

[ted_chou12]

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

[genericnumber1]

you know you can query a range of dates... something like

SELECT * 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...

[ted_chou12]

really? I dont find those in the tutorial, thats why I am having some trouble with this...
Thanks
Ted

[HuggieBear]

[quote author=genericnumber1 link=topic=121759.msg501186#msg501186 date=1168423589]
you know you can query a range of dates... something like

SELECT * 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 Oracle

Regards
Huggie

[genericnumber1]

I knew you could do it in SQL though I've really not tried it in MySQL.. why, you figure you can't?

[HuggieBear]

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

[genericnumber1]

doesn't look like it's in the mysql handbook, back to square one ted

[ted_chou12]

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?
Thanks
Ted

[HuggieBear]

What format is your dob column in your database?

Regatds
Huggie

[ted_chou12]

is simply stored as DATE for type so the format is just like the one above which is YYYY-MM-DD

[HuggieBear]

OK, I'll see if I can come up with a function, give me an hour...

Huggie

[ted_chou12]

thanks :D