pulling records from a start and end date

[penguinboy]

... this is freaking hard.

okay what i want to do is pull records based on month/day/year

now the tricky part is i want to pull them with a starting date, and an ending date.

so an example of the dates could be

from 08/02/1971 to 08/30/1971

or

from 01/01/1970 to 01/01/1971

or

from 12/24/1980 to 04/04/1983

so here's what i have so far

mysql_query("Select * from Records where
YYYY BETWEEN '$start_year' AND '$end_year'
and
MM IN ('$start_month','$end_month')
");

I think i'm going to have to do multiple querys... and i'll probably need to have php do some math... but its freaking hard!!

thanks for any help!

okay... now while I was typing this I came up with an idea.

if I could somehow lump the fields in the database together..

i could do

yyyymmdd

so

19710801 --- could be the start date
19720204 --- could be the end date

but ... I don't know how i'm going to do that ... unless i change the database... because right now the date is in three fields MM, DD, and YYYY.

well... thanks for any help

-bret

[nnichols]

you should use your second idea of using a DATE field (yyyy-mm-dd). It is easy to convert the table -

1. Add a new field of type DATE

2. run the following update query -

   UPDATE Records SET new_date_field=CONCAT(YYYY,'-',MM,'-',DD)

3. Delete old MM,DD,YYYY fields
   [/list=1]
   Hope this helps 😉